I am trying to find out what indexes are in the database only for performance
reasons and do not enforce a constraint. What would be the query to do that?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network
The only indexes that enforce a constaint are primary key and unique key
indexes. You can easily spot them using
SELECT INDEX_NAME, OWNER
FROM DBA_INDEXES
WHERE UNIQUENESS = 'NONUNIQUE'
However, beware, there may be some unique indexes that may have been created
for performance reasons only,
This may not do all you need but it will list out all the indexes in
dba_indexes that do not have a corresponding entry in the dba_constraints
table. You might have to put a few other criteria on it ... but this might
get you in the right direction.
select
a.owner,
a.index_name,
Arup Nanda wrote:
The only indexes that enforce a constaint are primary key and unique key
indexes. You can easily spot them using
SELECT INDEX_NAME, OWNER
FROM DBA_INDEXES
WHERE UNIQUENESS = 'NONUNIQUE'
However, beware, there may be some unique indexes that may have been created
for
The following will give you all the indexes that belongs to a constraint
select distinct t.owner as table_owner, i.table_name, o.owner as index_owner,
o.object_name as index_name,s.name as constraint_name,c.constraint_type from
sys.cdef$ cd,dba_objects o,sys.con$ s,dba_constraints
Title: RE: Identifying indexes
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
I am trying to find out what indexes are in the database only
for performance
reasons and do not enforce a constraint. What would be the
query to do that?
This query
I don't get it. Do a minus? Mr. Shao could you explain that please.
RWB
Shao, Chunning [EMAIL PROTECTED]@fatcity.com on 11/22/2002 02:49:15 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
The
A MINUS operator in SQL will find the differences between two queries. To
find indexes that are not associated with constraints (PK, UK, or FK), you
could you the following:
select table_owner, table_name, column_name, column_position from
dba_ind_columns
minus
select
1. Since DBA_CONSTRAINTS is already connecting to cons$ and cdef, why are
you using them in your join ?
2. Why are you joining to dba_objects ?? What purpose does it fill ?
3. Why not just join dba_indexes to dba_constraints and rule out the kind of
constraints you are not interested in ... i.e.
