Re: sql question

From: Helen J Mitchell <helenjmitchell_at_comcast.net>
Date: Sat, 3 Apr 2004 21:45:08 -0700
Message-ID: <021e01c419ff$9b574840$0307a8c0@rcs.com>

Do you have a script that counts the number of leafs for an index?

• Original Message ----- From: Igor Neyman To: oracle-l_at_freelists.org Sent: Thursday, April 01, 2004 2:57 PM Subject: RE: sql question

  How many times each index used:

  select SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ') - 1),   count(*)
  from dba_outline_hints
  where hint like '%INDEX%'
  GROUP BY SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ') - 1);

  List of indices not used:

  Select index_name from user_indexes where index_name not in (   Select distinct SUBSTR(hint, INSTR(' ') + 1, LENGTH(hint) - INSTR(' ')

• 1) from dba_outline_hints where hint like '%INDEX%');

  Igor Neyman, OCP DBA
  ineyman_at_perceptron.com

-----Original Message-----

  From: oracle-l-bounce_at_freelists.org
  [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of thump604_at_comcast.net   Sent: Thursday, April 01, 2004 4:13 PM   To: oracle-l_at_freelists.org
  Subject: sql question

  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

  ---

  ---

  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

---

---

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 Sat Apr 03 2004 - 22:41:25 CST