Re: Index and Constraint

Hi!

Robert Chung <robertch_at_mindspring.com> wrote in message news:1026146809%39256c4f.661295640_at_news.mindspring.com...
> As everybody knows, Oracle creates unique index automatically when
> primary key or foreign key constraints are created on table. They can

For primary key Oracle creates index and unique constraint. For foreign key it does not create any index (and not only Oracle, DB2 does not create index for foreign key to).

One of queries below we use to check schema structure

/*index creation for not indexed foreign keys (for 1 column keys)*/ select 'create index ' || CONSTRAINT_NAME || '_FK on ' || TABLE_NAME || '( ' ||
  COLUMN_NAME || ' );' from USER_CONS_COLUMNS where CONSTRAINT_NAME in
  (select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R') and (TABLE_NAME, COLUMN_NAME) not in
  (select TABLE_NAME, COLUMN_NAME from USER_IND_COLUMNS) order by TABLE_NAME, COLUMN_NAME;

to use DBA_CONS_COLUMNS, DBA_CONSTRAINTS, DBA_IND_COLUMNS in query add to it the condition for owner and index_owner atributes:

select 'create index ' || CONSTRAINT_NAME || '_FK on ' || TABLE_NAME || '( ' ||
  COLUMN_NAME || ' );' from DBA_CONS_COLUMNS where owner = 'USER_NAME' and
CONSTRAINT_NAME in
  (select CONSTRAINT_NAME from DBA_CONSTRAINTS where owner = 'USER_NAME' and
CONSTRAINT_TYPE = 'R')
and (TABLE_NAME, COLUMN_NAME) not in
  (select TABLE_NAME, COLUMN_NAME from DBA_IND_COLUMNS    where index_owner ='USER_NAME')
order by TABLE_NAME, COLUMN_NAME;

 and so one...

> be checked by looking up DBA_INDEXES, ALL_INDEXES, etc., but I cannot
> find any info that shows which index is associated with which
> constraint, especially when index is created first and constraint is
> added later with different names. Is there any way to find that out
> without looking up DBA_IND_COLUMNS and DBA_CONS_COLUMNS and comparing
> columns manually? Thank you in advance.

why manually ? dba have to be lazy :)

--
Regards, Lilya Kozlenko