Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index

RE: Why isn't Oracle Using My Index

From: Mark J. Bobak <mark.bobak_at_il.proquest.com>
Date: Fri, 22 Dec 2006 01:43:38 -0500
Message-Id: <1166769818.5795.8.camel@mars> 





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_at_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






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






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






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_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org] 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


Received on Fri Dec 22 2006 - 00:43:38 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US