CODEWITHSUNDEEP
oracle-databaseindexingunique-constraintcomposite-primary-key
Senthu Sivasambu
Senthu Sivasambu

Reputation: 181

Oracle COLUMN_ID implicit index creation

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.

Upvotes: 0

Views: 679

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

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.

Upvotes: 1

Related Questions