Re: Bind Variables ignoring Index on 10.2.0.4
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