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