RE: can't get a simple query to use an index
- From: "Whittle Jerome Contr NCI" <Jerome.Whittle@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Feb 2004 13:02:10 -0600
Hi Chris,
Are the "Select select" below a typo? If not, they might mess up the hints.
If in doubt, I whip out the Rule hint and see what happens.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
jerome.whittle@xxxxxxxxxxxx
618-622-4145
> -----Original Message-----
> From: Chris Stephens [SMTP:ChrisStephens@xxxxxxxxxx]
>
> 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@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:
