Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Why isn't Oracle Using My Index
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
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
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-lReceived on Thu Dec 21 2006 - 23:58:22 CST