Re: Indexes Ignored

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 14 Oct 2002 08:30:03 +0100
Message-ID: <aodrpk$ls9$1$8302bc10@news.demon.co.uk>

Step one (always) - force the plan you expect to see by using hints, and compare the results.

In this case, you might also check the details of the execution plan on the other machine - Note in particular the "cardinality" figures.

Although you say the number of rows returned from the inventory table should be three, you might note that Oracle thinks it will get 3,661 rows from the page table by using an index - starting from that point, it doesn't seem too surprising that something in the statistics is telling the CBO to expect lots of rows from things that appear (syntactically) to be child and grandchild tables.

Best guess at this point - there is supposed to be a histogram on the page.page_name column that has gone missing. This might allow Oracle to recognise that the query should return only three rows and act accordingly.

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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





Frank Sconzo wrote in message
<5d56563e.0210130059.7ddde6ac_at_posting.google.com>...


>I am running Oracle 8i on Solaris 2.6.

>

>When I run the follwing query with a simple join, none of my indexes

>on the inventory table are utilized. The table currently has four

>indexes.

>

>SQL> select inventory_name from inventory i, pageinventory pi, page p

>  2  where p.page_name = 'P1HW211010-0-M00800-1---AL2002'

>  3  and p.page_id = pi.page_id

>  4  and pi.inventory_id = i.inventory_id;

>

>Execution Plan

>----------------------------------------------------------

>   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6274 Card=3333670 By

>          tes=286695620)

>

>   1    0   HASH JOIN (Cost=6274 Card=3333670 Bytes=286695620)

>   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'PAGE' (Cost=2 Card=366

>          1 Bytes=109830)

>

>   3    2       INDEX (RANGE SCAN) OF 'PAGE_CI' (NON-UNIQUE) (Cost=1 C

>          ard=3661)

>

>   4    1     HASH JOIN (Cost=6245 Card=91059 Bytes=5099304)

>   5    4       INDEX (FAST FULL SCAN) OF 'PAGEINVENTORY_UNIQ' (UNIQUE

>          ) (Cost=4 Card=91060 Bytes=2367560)

>

>   6    4       TABLE ACCESS (FULL) OF 'INVENTORY' (Cost=4909 Card=378

>          702 Bytes=11361060)

>

>

Received on Mon Oct 14 2002 - 02:30:03 CDT