| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: can't get a simple query to use an index
OMG.
select order_header_id from order_Log where order_header_id=300947178;
Elapsed: 00:01:09.04
select order_header_id from order_Log where order_header_id='300947178'; Elapsed: 00:00:00.01
This list is wonderful for pointing out the blatantly obvious.
THANK YOU!!!
...i'll crawl back into my hole now.
-----Original Message-----
From: Boyle, Christopher J. [mailto:Christopher.J.Boyle_at_espn.com]
Sent: Wednesday, February 04, 2004 12:53 PM
To: oracle-l_at_freelists.org
Subject: RE: can't get a simple query to use an index
Are order_header_id and xxxx the same datatype? If not, the implicit = conversion will prevent the index from being used.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Chris Stephens
Sent: Wednesday, February 04, 2004 1:44 PM
To: 'oracle-l_at_freelists.org'
Subject: can't get a simple query to use an index
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 =3D 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 =3D XXXX;
4 rows returned
Full table scan in trace
Select select /*+ index(order_log) */ rowid from order_log where order_header_id =3D XXXX;
4 rows returned
Full table scan in trace
Alter session set Optimizer_idex_cost_adj =3D 1;
Session altered
Select rowid from order_log where order_header_id =3D XXXX;
4 rows returned
Full table scan in trace
Select select /*+ index(order_log) */ rowid from order_log where order_header_id =3D 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?
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |