Re: Why isn't Oracle Using My Index
- From: "Niall Litchfield" <niall.litchfield@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Fri, 22 Dec 2006 06:40:17 +0000
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> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx<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
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
Other related posts:
