| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Identifying indexes
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...
>
> I don't get it. "Do a minus"? Mr. Shao could you explain that please.
>
> RWB
>
>
>
>
> "Shao, Chunning" <cshao_at_traffic.com>@fatcity.com on 11/22/2002 02:49:15 PM
>
> Please respond to ORACLE-L_at_fatcity.com
>
>
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: <cemail2_at_sprintmail.com
> INET: cemail2_at_sprintmail.com
>
> 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: ListGuru_at_fatcity.com (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: cshao_at_traffic.com
>
> 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: ListGuru_at_fatcity.com (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: Reginald.W.Bailey_at_jpmorgan.com
>
> 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: ListGuru_at_fatcity.com (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: Tim_at_SageLogix.com 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: ListGuru_at_fatcity.com (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).Received on Fri Nov 22 2002 - 17:09:25 CST
 |
 |