sql question

From: <thump604_at_comcast.net>
Date: Thu, 01 Apr 2004 21:13:01 +0000
Message-Id: <040120042113.7009.406C85DD000A5A6D00001B612200761438FFCBCFC98F928A97@comcast.net>

Okay folks...Hopefully someone can help and provide some pointers...

DB is 8174
I have gathered a great deal of outlines in order to identify index usage. I would like to document the indices in the database and if they were used or unused and if they were used how many sql statments or outlines referenced a given index.

I'm not sure how to start with such in effort and not make it a manual nightmare.

Hints look like:
select hint from dba_outline_hints where hint like '%INDEX%'
..

INDEX(S_OPTY S_OPTY_U1)
INDEX(S_OPTY S_OPTY_U1)
INDEX(S_OPTY S_OPTY_V2)
INDEX(S_OPTY S_OPTY_U1)
INDEX(S_OPTY S_OPTY_U1)
INDEX(S_OPTY S_OPTY_V2)
INDEX(S_CONTACT S_CONTACT_U1)
INDEX(S_CONTACT S_CONTACT_U1)
INDEX(S_CONTACT S_CONTACT_U1)
INDEX(S_CONTACT S_CONTACT_V3)
INDEX(S_CONTACT S_CONTACT_P1)
INDEX(S_SRV_REQ S_SRV_REQ_U1)
INDEX(S_SRV_REQ S_SRV_REQ_U1)
INDEX(S_OPTY_CON S_OPTY_CON_U1)
INDEX(S_SRV_REQ S_SRV_REQ_U1)
INDEX(S_SRV_REQ S_SRV_REQ_U1)
INDEX(S_ORDER S_ORDER_U1)
INDEX(S_ORDER S_ORDER_U1)

How can I trim off the data to the left and right of the index name, count the number of times an index is listed in dba_outline_hints and list indices not used at all?

Probably pretty straight forward SQL, but it's one of my weaker areas. Cheers
- David

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Apr 01 2004 - 15:26:32 CST