0

How does Oracle use COLUMN_ID as found in USER_TAB_COLUMNS view? I just need to confirm that it does not use this internal column ordering while creating implicit indexes - such as when a primary key is enforced or a unique key constraint is created (that is key/constraints columns provided are used in the same order - left to right and not these internal column ordering). (if possible please point me in the direction of Oracle documentation.). Thanks in advance.

Senthu Sivasambu
  • 181
  • 1
  • 3
  • 12
  • 1
    :whenever you create a composite primary key ,you create a script with column name eg: `PRIMARY KEY (album, disk, posn)`,so the ordering decide by that way album is 1,disk is 2,posn is 3 ,not by oracle itself.Same case with UNIQUE key constraint. – Gaurav Soni Feb 22 '13 at 16:00
  • :take a look [link](http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys) – Gaurav Soni Feb 22 '13 at 16:09
  • Thanks for the reply Gaurav - that's what I would 'expect' Oracle to do as well - wonder if there is a spec on this or an easy way to figure out the index keys generated for a constraint. – Senthu Sivasambu Feb 22 '13 at 16:11

1 Answers1

1

It'll be hard to find something stating that it doesn't do something, but there isn't anything stating that it will use column_id to override the index creation.

You can see all the reference to column_id in the documentation here; the only one that seems matter is the all_tab_columns view.

You can verify the order of the columns as used in the index by querying the all_ind_columns view, where you will be able to see that there is no enforced relationship between its column_position - which comes from the order the columns are listed in the index creation command - and column_id.

If you are specifically interested in checking indexes that back up constraints, you can do something like:

select ac.owner, ac.table_name, ac.constraint_name, ac.index_owner,
    ac.index_name, aic.column_position, aic.column_name
from all_constraints ac
join all_ind_columns aic on aic.index_owner = coalesce(ac.index_owner, ac.owner)
    and aic.index_name = ac.index_name
order by 1, 2, 3, 6;

... adding filters for owner or table as needed.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thanks Alex - the link is useful - I can now verify myself if the column_id matters in the case of "implicit" index creations. – Senthu Sivasambu Feb 22 '13 at 16:19
  • Just did a quick check, it confirms that during the implicit index creations Oracle does use the order of the columns passed to key creations for the indexes that back up such constraints. create table test_table( col1 varchar(10), col2 varchar(10), col3 varchar(10), col4 varchar(10) );
    alter table test_table add constraint PK_TEST_TABLE primary key (col3,col1); select * from user_ind_columns where table_name='TEST_TABLE'; 
    result:
PK_TEST_TABLE TEST_TABLE COL3 1 10 10 ASC
PK_TEST_TABLE TEST_TABLE COL1 2 10 10 ASC
     – Senthu Sivasambu Feb 22 '13 at 16:28
  • thanks Alex - could not format the comment - in the middle of something. – Senthu Sivasambu Feb 22 '13 at 16:34