RE: can't get a simple query to use an index
- From: "Boyle, Christopher J." <Christopher.J.Boyle@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Feb 2004 13:53:14 -0500
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Chris Stephens
Sent: Wednesday, February 04, 2004 1:44 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
