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 owner, table_name, column_name, position from
dba_cons_columns;
Of course, it wouldn't give you the name of the index, so you can make this
an inline view for a larger query to hit DBA_IND_COLUMNS again would...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, November 22, 2002 3:03 PM
>
> 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 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 c,dba_indexes i, dba_tables t Where t.Table_Name =
> i.Table_Name and i.owner=o.owner and i.index_name=o.object_name and
> c.constraint_name=s.name and cd.Enabled = o.object_id and cd.con# =
s.con#
> and o.owner not in ('SYS','SYSTEM')
>
>
> do a minus will get what you want.
>
>
> -----Original Message-----
> Sent: Friday, November 22, 2002 1:40 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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 Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Shao, Chunning
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
