RE: Why isn't Oracle Using My Index

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


Other related posts: