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: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 21 Dec 2006 22:58:22 -0700
Message-Id: <200612220558.kBM5wQxl026438@mail76.megamailservers.com>


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 Thu Dec 21 2006 - 23:58:22 CST

Original text of this message

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