jump to navigation

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 …

About these ads

Comments»

1. Tony Sleight - October 11, 2011

I’m guessing IOT in this instance?

2. Gary - October 11, 2011

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 ?

Tony Sleight - October 11, 2011

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.

Peter - October 11, 2011

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.

3. Saurabh Manroy - October 12, 2011

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

4. Noons - October 12, 2011

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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,808 other followers

%d bloggers like this: