Donald K. Burleson Oracle Tips

Finding the index that enforces a primary key constraint

One of the most confounding issues with Oracle is the underlying indexes that are used to enforce a unique constraint.  In Oracle9i, the index can be dropped and managed separately from the constraint, but we still need a script to quickly locate the index that used by the unique constraint.

As we may know, Oracle-created indexes have cryptic names such as SYSnnnn, and you have no clue about the use for the index.

The following query utilizes the internal Oracle tables to create a nice report that shows the owner, table name, the constraint name, the index name, and additional constraint information.

create or replace view

   pk_index
as
select

   u.name    owner,
   t.name    table_name,
   c.name    constraint_name,
   i.name    index_name,
   cd.defer  con_info
from

   sys.cdef$ cd,

   sys.con$  c,
   sys.obj$  t,
   sys.obj$  i,
   sys.user$ u
where

   cd.type# = 2
and

   cd.con# = c.con#
and

   cd.obj# = t.obj#
and

   cd.enabled = i.obj#
and

   c.owner# = u.user#

;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: