Re: Bind Variables ignoring Index on 10.2.0.4

From: Anurag Varma <avoracle_at_gmail.com>
Date: Tue, 8 Apr 2008 08:06:25 -0700 (PDT)
Message-ID: <78f82aec-f2f6-4884-8677-076c137de845@u69g2000hse.googlegroups.com>

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 Received on Tue Apr 08 2008 - 10:06:25 CDT

Original text of this message