Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index

RE: Why isn't Oracle Using My Index

From: William Wagman <wjwagman_at_ucdavis.edu>
Date: Thu, 21 Dec 2006 23:07:54 -0800
Message-ID: <FE043305B38A0F448F3924429D650C2AB7AD87@VEXBE2.ex.ad3.ucdavis.edu>


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_at_ucdavis.edu
(530) 754-6208  


From: Wolfgang Breitling [mailto:breitliw_at_centrexcc.com] Sent: Thursday, December 21, 2006 9:48 PM To: William Wagman
Cc: oracle-l_at_freelists.org
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/>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 22 2006 - 01:07:54 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US