RE: Why isn't Oracle Using My Index
- From: "William Wagman" <wjwagman@xxxxxxxxxxx>
- To: "Jared Still" <jkstill@xxxxxxxxx>, <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 15:02:53 -0800
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:
