RE: Why isn't Oracle Using My Index

Greetings,
 
Thanks to Riyajh Shamsudeen for pointing out to me that NLS_SORT is in
part the culprit. In our database NLS_SORT = GENERIC_BASELETTER.
According to the Oracle documentation this forces a full table scan. I
set nls_sort=binary and the query used the indexes. I still don't fully
understand what is going on and need to do some further reading. I also
need to turn on cpu_costing and see if that will resolve the problem if
nls_sort is left at it's current setting. Thanks to all who responded, I
truly appreciate the help.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------
 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |   416 |   226K|  1430 |
|*  1 |  TABLE ACCESS FULL   | T185        |   416 |   226K|  1430 |
--------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
--------
   1 - filter(NLSSORT("T185"."C1")=NLSSORT('HD0000000041608'))
 
Note: cpu costing is off

 

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208 

 

________________________________

From: Jared Still [mailto:jkstill@xxxxxxxxx] 
Sent: Thursday, December 21, 2006 11:20 AM
To: Thomas.Mercadante@xxxxxxxxxxxxxxxxx
Cc: William Wagman; oracle-l@xxxxxxxxxxxxx
Subject: Re: Why isn't Oracle Using My Index


On 12/21/06, Mercadante, Thomas F (LABOR)
<Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote: 

        Bill,
        
        When you run the query, how fast is the response?
        


Just to add a bit to Tom's response:

Why not do a 10046 trace on the query?

Run it both with and without the index hint and 
compare the results.

An excellent method for comparing the 2 would
be to use runstats -  http://asktom.oracle.com/tkyte/runstats.html

HTH

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: