You probably need to rebuild index. It looks to me that your index
might not be usable.There is "STATUS" field in the DBA_INDEXES.
If someone moved the table, indexes are unusable.
On 02/04/2004 01:44:21 PM, Chris Stephens wrote:
> 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
> -----------------------------------------------------------------
>
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:06:18 CST
