Re: index not in use

On Feb 28, 9:45 pm, "Steve Robin" <ocma..._at_gmail.com> wrote:
> On Feb 28, 7:44 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > Oracle is predicting that the cost of retrieving the data by a full
> > tablescan is 1040, while it is predicting that the access by index
> > will have a cost of 14108. The simple answer is:
> > 1040 < 14108
>
> > Plan with the cost of 1040 is predicted to be more efficient than the
> > plan with a predicted cost of 14108, so the more expensive plan is
> > discarded. A 10053 trace will show you this decision making process
> > in action, but it takes a while to learn how to read such a trace
> > file.
>
> > A very simple run through, assuming (this is a little different than
> > your situation):
> > * 89,518 rows in the table occupying 24MB
> > * 8KB block size
> > * db file multiblock read count = 8
> > * B*Tree index exists (numbers will likely be different for a bitmap
> > index)
> > * every block read results in a physical read
>
> > 24,000,000 / 89,518 = 268 bytes per row
> > 8,192 / 268 = Roughly 30 rows per 8KB block
>
> > Assuming db file multiblock read count = 8:
> > 30 * 8 = 240 rows read per physical I/O
>
> > For a B*Tree index:
> > At least 3 I/Os to read the index + 1 I/O to read the row:
> > 3 + 1 = 4 IOs per row = 1/4 row per physical I/O
>
> > In this example, performing a full table scan is theoretically 960
> > times more efficient than an index lookup, if 99.99% of the rows need
> > to be returned. This theoretical calculation has enough errors in it
> > to be the basis for a long article. The point is simply that table
> > access by index lookup is not always the most efficient method to
> > retrieve data.
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> Thanks a lot. That helps me a lot.
> But still I don't understand one thing.
> SQL> select count(1) from MCC_USER.SC_ORGANIZATION where
> 2 ORGANIZATIONTYPE='3';
>
> COUNT(1)
> ----------
> 797
>
> Why in explain plan, this query retrieves 89518, when it has 797 rows.

Oracle makes predictions based on the statistics that are in the database to determine the approximate number of rows that will be returned when you perform an EXPLAIN PLAN. Sometimes the statistics are insufficient to accurately approximate the number of rows that will be returned. Assume that there are three distinct values in the ORGANIZATIONTYPE column, there are roughly 268,554 rows in the table, and no histograms are available for the column that show the data distribution among the three distinct values. Are there 2 rows with ORGANIZATIONTYPE='1', 267,755 rows with ORGANIZATIONTYPE='2', and 797 rows with ORGANIZATIONTYPE='3'? Or, are there 89,518 rows with each of the three values?

What you might want to do is to use DBMS_XPLAN rather than EXPLAIN PLAN, or use a 10046 trace to determine the actual plan. In 10g R2 the actual plan will also appear in a 10053 trace file. Setting up for such a trace would look like this (note this works as is on 10g R2 - it may or may not work as is on 9.2.0.8):

ALTER SESSION SET STATISTICS_LEVEL=ALL; select count(1) from MCC_USER.SC_ORGANIZATION where

   ORGANIZATIONTYPE='3'; SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS')); Note that the output of the above will look very poor in SQLPlus - you may need to disable column headings first.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Feb 28 2007 - 22:32:10 CST