Query Optimizer causing indexes not to be used

From: Mike Griffin <mgriffin_at_cdgpd.com>
Date: Wed, 3 Feb 1999 14:24:22 -0800
Message-ID: <79aiee$465$1@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 Wed Feb 03 1999 - 16:24:22 CST