Re: Bind Variables ignoring Index on 10.2.0.4

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 08 Apr 2008 09:35:15 -0700
Message-ID: <1207672514.235794@bubbleator.drizzle.com>

Anurag Varma wrote:
>
> Vladimir M. Zakharychev wrote:

>> On Apr 7, 10:00 am, pat.ca..._at_service-now.com wrote:

> -snip-

>> I tried this on my test 10.2.0.4/Win32 instance:
>>
>> CREATE TABLE CI_TEST(X CHAR(32));
>>
>> CREATE UNIQUE INDEX UQ$CI_TEST ON
>> CI_TEST(NLSSORT(X,'NLS_SORT=''BINARY_CI''');
>>
>> Inserted 832 rows into this table with values: 'A' to 32x'A', 'B' to
>> 32x'B', ..., 'Z' to 32x'Z'.
>>
>> SELECT X FROM CI_TEST WHERE X=:B1;
>>
>> :B1 was initialized to 'x';
>>
>> SQL_ID  a779vscy3axhc, child number 0
>> -------------------------------------
>> SELECT X FROM CI_TEST WHERE X=:B1
>>
>> Plan hash value: 1574216700
>>
>> ------------------------------------------------------------------------------------------
>> | Id  | Operation                   | Name       | Rows  | Bytes |
>> Cost (%CPU)| Time     |
>> ------------------------------------------------------------------------------------------
>> |   0 | SELECT STATEMENT            |            |       |       |
>> 2 (100)|          |
>> |   1 |  TABLE ACCESS BY INDEX ROWID| CI_TEST    |     1 |    34 |
>> 2   (0)| 00:00:01 |
>> |*  2 |   INDEX UNIQUE SCAN         | UQ$CI_TEST |     1 |       |
>> 1   (0)| 00:00:01 |
>> ------------------------------------------------------------------------------------------
>>
>> Predicate Information (identified by operation id):
>> ---------------------------------------------------
>>
>>    2 -
>> access("CI_TEST"."SYS_NC00002$"=NLSSORT(:B1,'nls_sort=''BINARY_CI'''))
>>
>> Note
>> -----
>>    - dynamic sampling used for this statement
>>

> --snip--

>> Regards,
>>    Vladimir M. Zakharychev

>
>
> I did seem to have reproduced the OP's issue and it seems to be
> present in versions
> higher than 10.2.0.4 and 11.1.0.6
>
> Here is my test case:
>
> ALTER SESSION SET NLS_COMP=LINGUISTIC ;
> ALTER SESSION SET NLS_SORT=BINARY_CI;
>
> create table test (a char(10), b number, c varchar2(10));
> create index test_a_idx on test (NLSSORT(a,'NLS_SORT=BINARY_CI'));
> create index test_c_idx on test (NLSSORT(c,'NLS_SORT=BINARY_CI'));
> insert into test select
> dbms_random.string('A',trunc(dbms_random.value(1,10))), rownum,
> dbms_random.string('A',trunc(dbms_random.value(1,10))) from
> all_tables;
> commit;
>
> set autotrace traceonly exp
> select * from test where a = 'D';
> var v varchar2
> exec :v := 'D';
> var vc char(10)
> exec :vc := 'D';
>
> select * from test where a = :v;
> select * from test where a = :vc;
>
>
> Issue seems to happen only when the column is defined as CHAR
> and the bind variable being used has a datatype of varchar2 (i.e.
> datatype mismatch).
>
> In 10.2.0.3.
> select * from test where a = :v;
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3908671039
>
> ------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
> ------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 13 | 416 |
> 2 (0)| 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 13 | 416 |
> 2 (0)| 00:00:01 |
> |* 2 | INDEX RANGE SCAN | TEST_A_IDX | 5 | |
> 1 (0)| 00:00:01 |
> ------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 -
> access(NLSSORT(INTERNAL_FUNCTION("A"),'nls_sort=''BINARY_CI''')=NLSSORT(:V,
> 'nls_sort=''BINARY_CI'''))
>
>
> In 10.2.0.4.
> select * from test where a = :v;
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 1357081020
>
> --------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time |
> --------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 17 | 544 | 5 (0)|
> 00:00:01 |
> |* 1 | TABLE ACCESS FULL| TEST | 17 | 544 | 5 (0)|
> 00:00:01 |
> --------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 -
> filter(NLSSORT("A",'nls_sort=''BINARY_CI''')=NLSSORT(:V,'nls_sort
> =''BINARY_CI'''))
>
>
> *** If the bind variable is CAST as char(10) .. then it uses the index
> properly.
> select * from test where a = cast(:v as char(10));
>
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3952958149
>
> ------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes |
> Cost (%CPU)| Time |
> ------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 17 | 544 |
> 4 (0)| 00:00:01 |
> | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 17 | 544 |
> 4 (0)| 00:00:01 |
> |* 2 | INDEX RANGE SCAN | TEST_A_IDX | 7 | |
> 1 (0)| 00:00:01 |
> ------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 -
> access(NLSSORT(INTERNAL_FUNCTION("A"),'nls_sort=''BINARY_CI''')=NLSSORT(CAS
> T(:V AS char(10)),'nls_sort=''BINARY_CI'''))
>
>
>
>
> Anurag

Well done.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Apr 08 2008 - 11:35:15 CDT