RE: Why isn't Oracle Using My Index

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 21 Dec 2006 22:48:10 -0700
Message-Id: <200612220548.kBM5mERG007262@mail97.megamailservers.com>

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.freelists.org/webpage/oracle-l

Received on Thu Dec 21 2006 - 23:48:10 CST