Re: index rebuild

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 19 Mar 2004 09:22:12 -0000
Message-ID: <002801c40d93$af0069d0$7102a8c0@Primary>

Note in-line:

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

• Original Message -----
  > From: "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk>
  > Do you have index lookup followed by table access by rowid in mind here,
  if you can reduce the number of leaf blocks then you will likely aid any
  > scan operations on the index (including range scans not just ffs). Of
  course you will also with most apps just increase the likelyhood of block splits
  > occurring...
  

The defect in this argument (when used as a general point in favour or rebuilds) is
that most range scans are for a small number of rows. Given that even if an index
is running at a uniform 50% efficiency (i.e. all the blocks hold the same number of
rows, which is half the maximum) a large percentage of small range will still tend to
hit just one leaf block. Conversely, if you have such large range scans most of them
HAVE to hit two leaf blocks to get all the rowids, then most of the time required for
your query is likely to be spent reading lots of table blocks, and the benefit you get
from rebuilding the index is marginal - and you ought to be working on the more important
task of investigating why you are doing such large range scans.

---

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 Fri Mar 19 2004 - 03:18:46 CST