Re: Query Optimizer causing indexes not to be used

In article <79aiee$465$1_at_brokaw.wa.com>,   "Mike Griffin" <mgriffin_at_cdgpd.com> wrote:
> We are using a pretty standard Oracle "Select" statement
> and the Oracle optimizer is choosing not to use our
> index, we can use an optimization hint and force it
> to work, but the problem is I'm unsure why this is
> happening? Help.
>
> ================================================
> This Original Query (takes minutes to complete)
> ================================================
> SELECT
> table1.PART_ID, table1.NAME, table2.PART_ID
> FROM table1, table2
> WHERE table1.NAME = 'FOO' and table1.PART_ID = table2.PART_ID
>
> ================================================
> This Modified Query (returns almost immediately)
> NOTICE: /*+ ORDERED USE_NL(table2) */
> =================================================
> SELECT /*+ ORDERED USE_NL(table2) */
> table1.PART_ID, table1.NAME, table2.PART_ID
> FROM table1, table2
> WHERE table1.NAME = 'FOO' and table1.PART_ID = table2.PART_ID
>
> TABLE1
> Name Null? Type
> ------------------------------- -------- ----
> PART_ID NOT NULL NUMBER(38) (indexed)
> NAME VARCHAR2(16) (indexed)
>
> TABLE2
> Name Null? Type
> ------------------------------- -------- ----
> PART_ID NUMBER(38) (indexed)
>
> - Mike Griffin
> mgriffin_at_cdgpd.com
>

Without explain plan output any answer you get is only a guess. Do these tables have statistis and did you update your statistics with a large sample size or compute then re-explain your query to compare the before and after paths? The ORDERED hint example probably produced the explain plan I would have expected from the first query if the tables are decent size to large which leads me to suspect the stats. One way to make a determination that the stats for a table need updating is to compare the num_rows column from all_tables to the result of a select count(*) on the table. If the two numbers differ by more than 5 to 10% then I would update them.

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 03 1999 - 20:04:30 CST