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

| 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