primary key with a multi-column index


DB version: Oracle 12.1

SQL> create table system.test (col1 number,col2 number, col3 number);

Table created.

SQL> create index system.idx1 on system.test (col2,col1);

Index created.

SQL> alter table system.test ADD PRIMARY KEY (col1) using index;

Table altered.

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
2
—————————————————————————————

But if we do the same steps but just change the index order
SQL> create index system.idx1 on system.test (col1,col2);

Index created.

we get total no of indexes for table=1

SQL> select count(*) from dba_indexes where table_name=’TEST’ and owner=’SYSTEM’;

COUNT(*)
———-
1

why oracle doesn’t use the existing index in first case ?

Answer is:  Multiple reasons, To use a multi-column index for a primary key, the key should be the leading index columns. Remember, Oracle support only “Index Skip Scan” for non-leading columns. ROWID mapping will be always based on leading columns. (Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the nonleading key of the index.)

For more information about index skip scan please follow: http://docs.oracle.com/cd/B10501_01/server.920/a96533/optimops.htm#51553

 

 

Advertisements

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