Re: Why isn't Oracle Using My Index

You and me both, and according to the note the feature has been around since 816, though only really end-user usable since 2002! One does indeed learn something new, if not every day then frequently.

Niall

On 12/22/06, Wolfgang Breitling <breitliw_at_centrexcc.com> 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.1seems to cover it pretty well.
>
> Regards,
> Brandon
>
> ------------------------------
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org<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
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

--
http://www.freelists.org/webpage/oracle-l