Foreign Keys with Missing Indexes

--FK Constraint columns not indexed

set lines 160set pages 40col FKCons       format a30col SourceTable  format a30col SourceColumn format a30col TargetTable  format a30col TargetColumn format a30select scc.constraint_name  as FKCons,       scc.table_name       as SourceTable,       scc.column_name      as SourceColumn,       tcc.table_name       as TargetTable,       tcc.column_name      as TargetColumn        from all_cons_columns scc,        all_constraints sc,       all_cons_columns tcc where sc.constraint_name = scc.constraint_name    --Join Cons to Cons Cols   and sc.r_constraint_name = tcc.constraint_name  --Join source to target   and sc.constraint_type = 'R'                    --RI Constraints only   and scc.owner = '&SCHEMA'                           --Only OMS schema   and tcc.table_name||tcc.column_name 
       not in (select i.table_name||i.column_name                  from all_ind_columns i                where i.index_owner = '&SCHEMA')       --FKs not indexed order by scc.table_name/

I can only guess this was a problem in prior releases of the database.  According to this test in 12c, it doesn't seem possible.


SQL> create table source (sourceid number, constraint sourcepk primary key (sourceid));
Table created.

SQL> create table target (targetid number, constraint targetpk primary key (targetid));
Table created.

SQL> alter table target add constraint sourceidfk foreign key (targetid) references source (sourceid);
Table altered.

SQL> col table_name format a20
SQL> col index_name format a20
SQL> col column_name format a20

SQL> select table_name,index_name,column_name from all_ind_columns where index_owner = 'KEN';

TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
TARGET               TARGETPK             TARGETID
SOURCE               SOURCEPK             SOURCEID

SQL> drop index sourcepk;
drop index sourcepk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table source disable constraint sourcepk;
alter table source disable constraint sourcepk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (KEN.SOURCEPK) - dependencies exist

AWR Interval and Retention

SQL
select
       extract( day from snap_interval) *24*60+
       extract( hour from snap_interval) *60+
       extract( minute from snap_interval ) "Snapshot Interval",
       extract( day from retention) *24*60+
       extract( hour from retention) *60+
       extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

SAMPLE OUTPUT
Snapshot Interval Retention Interval
----------------- ------------------
               60              10080