Re: Why isn't Oracle Using My Index

  • From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
  • To: niall.litchfield@xxxxxxxxx
  • Date: Fri, 22 Dec 2006 10:07:01 -0600

Hi All
Due to some issues with mail server DNS entries, my posting to the list bounces. I am trying other options ;-) So, I was working with William off-line and here are the highlights:
1. I thought, autotrace had dumped incorrect statistics, asked for 10046/10053 trace files. 2. 10053 trace files were never generated for this SQL. Still, don't understand, why ? I think, this must be a bug with 9i. I can't test it. 
   3. explain plan filter predicates clearly pointed out the issue.
Of course, metalink documents mentioned here and reference books are showing the issues. I recreated the issue before telling William. And Wolfgang, as usual, has done a wonderful job of recreating the issue. 


Thanks
Riyaj Shamsudeen

Niall Litchfield wrote:
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@xxxxxxxxxxxxx <mailto:breitliw@xxxxxxxxxxxxx>> 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> [
    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.centrexcc.com/>





The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 12-2006