can't get a simple query to use an index

From: Chris Stephens <ChrisStephens_at_affina.com>
Date: Wed, 4 Feb 2004 12:44:21 -0600
Message-ID: <4A82CFCF30FDD211A7960008C7B1917014F06A2C@hero.affina.net>

This started out with a simple query involving 2 tables that was taking entirely too long....it has been reduced to the easiest of statements in an attempt to for the query to use a stupid index that I'm quite confident will speed things up. Here's as much relevant info as I can think of:  

Optimizer_idex_cost_adj = 100 (I know)

Table was analyzed with dbms_stats.gather_table_stats with 'for all indexed columns' option.

There is an non-unique index on order_header_id

18368689 rows in order_log.

3606306 distinct order_header_id's in order_log  

Select rowid from order_log where order_header_id = XXXX;  

4 rows returned

Full table scan in trace  

Select select /*+ index(order_log) */ rowid from order_log where order_header_id = XXXX;  

4 rows returned

Full table scan in trace  

Alter session set Optimizer_idex_cost_adj = 1;

Session altered  

Select rowid from order_log where order_header_id = XXXX;  

4 rows returned

Full table scan in trace  

Select select /*+ index(order_log) */ rowid from order_log where order_header_id = XXXX;  

4 rows returned

Full table scan in trace    

I tried adding the index name to the hint with the same result.  

What else can I do to force an index lookup?      

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Wed Feb 04 2004 - 12:44:21 CST