RE: can't get a simple query to use an index

From: Whittle Jerome Contr NCI <Jerome.Whittle_at_scott.af.mil>
Date: Wed, 4 Feb 2004 13:02:10 -0600
Message-ID: <DFEC3C69DE8C524DA705BC09A220740B0257A103@AMCW2VN801.amc.ds.af.mil>

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_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Chris Stephens [SMTP:ChrisStephens_at_affina.com]
>
> 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 - 13:02:10 CST