Re: Query Optimizer causing our indexed not to be used

If you have to show the same part_id twice, show the same one - not from different tables.
SELECT table1.PART_ID, table1.NAME, table1.PART_ID FROM table1, table2
WHERE table1.NAME = 'FOO' and table1.PART_ID = table2.PART_ID

Mike Griffin wrote in message <79aigr$8ds$1_at_brokaw.wa.com>...
>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
>
>
Received on Fri Feb 05 1999 - 06:27:23 CET