RE: Why isn't Oracle Using My Index

Wolfgang, 
 
nls_sort=GENERIC_BASELETTER is set in the database and, as you noted,
nls_comp=ANSI. I believe that was done by the vendor but I don't know
why. I have asked. But your duplication is exactly what I am seeing.
Thanks.
 

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

 

________________________________

From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
Sent: Thursday, December 21, 2006 9:48 PM
To: William Wagman
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Why isn't Oracle Using My Index


What I don't understand is why the simple statement

SELECT C240000008 FROM aradmin.t185 WHERE C1 = 'HD0000000041608'

requires the nlssort filter in the first place. Can you share the ddl
(gotten from dbms_metadata) for the table and its indexes with us.

If is set NLS_SORT = GENERIC_BASELETTER and run a similar statement on
the following table:

CREATE TABLE "SCOTT"."M1"
(       "ID" VARCHAR2(13),
"UNIFORM" VARCHAR2(13),
"RANDOM" VARCHAR2(13),
"FILLER" VARCHAR2(4000)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" 



  CREATE UNIQUE INDEX "SCOTT"."M1_IX" ON "SCOTT"."M1" ("ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

22:26:44 ora101.scott> explain plan for select random from m1 where
id='000000000550';

Explained.

22:27:11 ora101.scott> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
------------------
Plan hash value: 2146983540

------------------------------------------------------------------------
-------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------
-------------
|   0 | SELECT STATEMENT            |       |     1 |    26 |     2
(0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| M1    |     1 |    26 |     2
(0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | M1_IX |     1 |       |     1
(0)| 00:00:01 |
------------------------------------------------------------------------
-------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"='000000000550')

I get the index use and an access predicate rather than a filter
predicate. There is obviously more to the situation than your simple
introduction pretends. Note also that in my testcase the optimizer
correctly predicts a cardinality of 1 for the resultset.

At 04:02 PM 12/21/2006, William Wagman wrote:


        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
         

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com <http://www.centrexcc.com/>  

Other related posts: