Query Optimizer causing our indexed not to be used
From: Mike Griffin <mgriffin_at_cdgpd.com>
Date: Wed, 3 Feb 1999 14:25:41 -0800
Message-ID: <79aigr$8ds$1_at_brokaw.wa.com>
[Quoted] 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
Date: Wed, 3 Feb 1999 14:25:41 -0800
Message-ID: <79aigr$8ds$1_at_brokaw.wa.com>
[Quoted] 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
[Quoted] 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