Why Are My Indexes Still Valid Quiz ? (Move On) October 11, 2011
Posted by Richard Foote in Index Organized Tables, Oracle Indexes, Quiz.trackback
OK, this quiz is a nice easy one, the lads at work got this without too much trouble.
Normally, when you MOVE (re-org) a table, all the associated indexes become Unusable. As below:
SQL> create table pink_floyd (id number constraint pf_pk primary key, code number, name varchar2(30)); Table created. SQL> create index pf_code_i on pink_floyd(code); Index created. SQL> insert into pink_floyd select rownum, mod(rownum,100), 'The Dark Side Of The Moon' from dual connect by level <= 10000; 10000 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'PINK_FLOYD', cascade=>true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed. SQL> select index_name, num_rows, status from dba_indexes where table_name = 'PINK_FLOYD'; INDEX_NAME NUM_ROWS STATUS ------------------------------ ---------- -------- PF_PK 10000 VALID PF_CODE_I 10000 VALID SQL> alter table pink_floyd move; Table altered. SQL> select index_name, num_rows, status from dba_indexes where table_name = 'PINK_FLOYD'; INDEX_NAME NUM_ROWS STATUS ------------------------------ ---------- -------- PF_PK 10000 UNUSABLE PF_CODE_I 10000 UNUSABLE
So the indexes are now all unusable ..
However, I previously created another table called BOWIE that has exactly the same columns, indexes and data but when I MOVE this table:
SQL> select index_name, num_rows, status from dba_indexes where table_name = 'BOWIE'; INDEX_NAME NUM_ROWS STATUS ------------------------------ ---------- -------- BOWIE_PK 10000 VALID BOWIE_CODE_I 10000 VALID SQL> alter table bowie move; Table altered. SQL> select index_name, num_rows, status from dba_indexes where table_name = 'BOWIE'; INDEX_NAME NUM_ROWS STATUS ------------------------------ ---------- -------- BOWIE_PK 10000 VALID BOWIE_CODE_I 10000 VALID
All the indexes remain VALID !!
What’s so different about this table and their indexes ??????
I plan to discuss this whole topic (finally) in more detail in the coming weeks …
Richard Foote's Oracle Blog 
I’m guessing IOT in this instance?
LikeLike
Can’t be an IOT with two indexes.
You could do it with two BOWIE tables in different schemas. One, in the schema ODDITY, with the indexes and the other in DUKE without. You log on to DUKE, do the move and the indexes in DBA_INDEXES are still valid (because they are on the other BOWIE table).
I think you’ve got something smarter though.
PS. How about copying Tim Hall and doing some quizzes at PL/SQL Challenge ?
LikeLike
Well, IOT and two indexes seems to work for me.
[CODE lang=”sql”]
TESTDB01 USER> select version from v$instance;
VERSION
—————–
11.2.0.1.0
TESTDB01 USER> create table pink_floyd (id number, code number, name varchar2(30), primary key(id, code, name)) organization index;
Table created.
TESTDB01 USER> insert into pink_floyd select rownum, mod(rownum,100), ‘The Dark Side Of The Moon’ from dual connect by level commit;
Commit complete.
TESTDB01 USER> select tablespace_name from user_tables where table_name = ‘PINK_FLOYD’;
TABLESPACE_NAME
——————————
TESTDB01 USER> select index_name, num_rows, status from dba_indexes where table_name = ‘PINK_FLOYD’;
INDEX_NAME NUM_ROWS STATUS
—————————— ———- ——–
SYS_IOT_TOP_152689 VALID
TESTDB01 USER> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’PINK_FLOYD’, cascade=>true, estimate_percent=>null, method_opt=>’FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
TESTDB01 USER> select index_name, num_rows, status from dba_indexes where table_name = ‘PINK_FLOYD’;
INDEX_NAME NUM_ROWS STATUS
—————————— ———- ——–
SYS_IOT_TOP_152689 10000 VALID
TESTDB01 USER> create index i_name on pink_floyd(name);
Index created.
TESTDB01 USER> select index_name, num_rows, status from dba_indexes where table_name = ‘PINK_FLOYD’;
INDEX_NAME NUM_ROWS STATUS
—————————— ———- ——–
SYS_IOT_TOP_152689 10000 VALID
I_NAME 10000 VALID
TESTDB01 USER> alter table pink_floyd move tablespace testspace;
Table altered.
TESTDB01 USER> select index_name, num_rows, status from dba_indexes where table_name = ‘PINK_FLOYD’;
INDEX_NAME NUM_ROWS STATUS
—————————— ———- ——–
SYS_IOT_TOP_152689 10000 VALID
I_NAME 10000 VALID
TESTDB01 USER> select tablespace_name from user_tables where table_name = ‘PINK_FLOYD’;
TABLESPACE_NAME
——————————
TESTDB01 USER>
[/CODE]
I think this works because the IOT is really an index, the move command has the effect of rebuilding the indexes.
LikeLike
I am guessing IOT too.
This is because IOT´s have the primary key and a “best guess adress” as their “rowid” on their secondary indexes and this remains valid even if the IOT is moved (only the guess is probably bad).
The first index, the primary key, is moved to a consistent state by the move table command which is better described as a move index command in this case.
Greetings
Peter
@Gary: IOT´s can have secondary indexes on them.
LikeLike
Julian Dyke has published a presentation on IOT internals that describes secondary Indexes on IOT and best guess address stored in them.
That explains why move table on IOT won’t impact secondary indexes on IOT.
Link to the ppt: http://www.juliandyke.com/Presentations/IOTInternals.ppt
LikeLike
Gotta be an IOT. The secondary index is not an issue.
From the “manuel”:
“Use of primary-key based logical rowids, as opposed to physical rowids, in secondary indexes on index-organized tables allows high availability. This is because, due to the logical nature of the rowids, secondary indexes do not become unusable even after a table reorganization operation that causes movement of the base table rows.”
In IOTs, rows are stored in pk order and rowids use logical rowid rather than physical. The logical rowid doesn’t change with table location.
LikeLike