RE: Why isn't Oracle Using My Index
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: wjwagman@xxxxxxxxxxx
- Date: Thu, 21 Dec 2006 22:48:10 -0700
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
Other related posts:
