Why Are My Indexes Still Valid Quiz ? (Move On) October 11, 2011
Posted by Richard Foote in Index Organized Tables, Oracle Indexes, Quiz.6 comments
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 …
