RE: Why isn't Oracle Using My Index
- From: "Mark J. Bobak" <mark.bobak@xxxxxxxxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Fri, 22 Dec 2006 01:43:38 -0500
The solution is, of course, to create a function based index. Here's my
test case:
set lines 150
set echo on
drop table test;
create table test as select * from dba_objects;
create index test_ind on test(object_name);
explain plan for select * from test where object_name = 'IND$';
select * from table(dbms_xplan.display);
pause
alter session set nls_comp=ansi;
alter session set nls_sort=generic_baseletter;
pause
explain plan for select * from test where object_name = 'IND$';
select * from table(dbms_xplan.display);
pause
create index test_nls_ind on
test(nlssort(object_name,'NLS_SORT=generic_baseletter'));
pause
explain plan for select * from test where object_name = 'IND$';
select * from table(dbms_xplan.display);
Here's the output I got on my XE instance on my laptop:
mjb@mars:~$ sqlplus /
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 22 01:34:17 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> @test_case
SQL> drop table test;
Table dropped.
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index test_ind on test(object_name);
Index created.
SQL> explain plan for select * from test where object_name = 'IND$';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2216933833
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 177 | 2
(0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IND | 1 | | 1
(0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access("OBJECT_NAME"='IND$')
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> pause
SQL> alter session set nls_comp=ansi;
Session altered.
SQL> alter session set nls_sort=generic_baseletter;
Session altered.
SQL> pause
SQL> explain plan for select * from test where object_name = 'IND$';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114 | 20178 | 49 (3)| 00:00:01
|
|* 1 | TABLE ACCESS FULL| TEST | 114 | 20178 | 49 (3)| 00:00:01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter(NLSSORT("OBJECT_NAME",'nls_sort=''GENERIC_BASELETTER''')=H
EXTORAW('2B302606002B30260600') )
Note
-----
- dynamic sampling used for this statement
18 rows selected.
SQL> pause
SQL> create index test_nls_ind on
test(nlssort(object_name,'NLS_SORT=generic_baseletter'));
Index created.
SQL> pause
SQL> explain plan for select * from test where object_name = 'IND$';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 833127996
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114 | 20178 |
21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 114 | 20178 |
21 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_NLS_IND | 46 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 -
access(NLSSORT("OBJECT_NAME",'nls_sort=''GENERIC_BASELETTER''')=HEXTORAW('2B3
02606002B30260600') )
Note
-----
- dynamic sampling used for this statement
19 rows selected.
So, now I just need to understand why I'd want to set NLS_SORT to
generic_baseletter in the first place.
-Mark
On Thu, 2006-12-21 at 22:58 -0700, Wolfgang Breitling wrote:
> Thanks for the reference. Not being familiar with that topic either, I
> only set nls_sort. The note shows that I also needed to set nls_comp.
> Then my previous example shows the same behaviour as William
> describes:
>
> ora101.scott> ALTER SESSION SET NLS_COMP=ANSI;
>
> Session altered.
>
> ora101.scott> ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
>
> Session altered.
>
> ora101.scott> explain plan for select random from m1 where
> id='000000000550';
>
> Explained.
>
> ora101.scott> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ----------------------------------------------------------------------------------------
> Plan hash value: 3061007841
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 100 | 2600 | 287 (5)|
> 00:00:01 |
> |* 1 | TABLE ACCESS FULL| M1 | 100 | 2600 | 287 (5)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 -
> filter(NLSSORT("ID",'nls_sort=''GENERIC_BASELETTER''')=HEXTORAW('
> 1212121212121212121717120012121212121212121217171200') )
>
> 14 rows selected.
>
> PS. As to Mark's suggestion of creating an index on the two columns in
> the query to enable an index-only access path. It doesn't work either
> because of the nls_sort issue:
>
> ora101.scott> create unique index m1_i2 on m1(id,random);
>
> Index created.
>
> ora101.scott> explain plan for select random from m1 where
> id='000000000550';
>
> Explained.
>
> ora101.scott> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ------------------------------------------------------------------------------
> Plan hash value: 3061007841
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
> |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 100 | 2600 | 287 (5)|
> 00:00:01 |
> |* 1 | TABLE ACCESS FULL| M1 | 100 | 2600 | 287 (5)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 -
> filter(NLSSORT("ID",'nls_sort=''GENERIC_BASELETTER''')=HEXTORAW('
> 1212121212121212121717120012121212121212121217171200') )
>
> I certainly learned something new today.
>
> At 10:20 PM 12/21/2006, Allen, Brandon wrote:
> > Not familiar with the issue myself until now, but Metalink Note
> > 109118.1 seems to cover it pretty well.
> >
> > Regards,
> > Brandon
> >
> >
> > ____________________________________________________________________
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jared Still
> >
> >
> >
> > Can you perhaps point out just which documentation you
> > found this in?
> >
> > Privileged/Confidential Information may be contained in this message
> > or attachments hereto. Please advise immediately if you or your
> > employer do not consent to Internet email for messages of this kind.
> > Opinions, conclusions and other information in this message that do
> > not relate to the official business of this company shall be
> > understood as neither given nor endorsed by it.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
