Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Why CBO don't use function-base index when I use like and bind variable

894637Jun 23 2012 — edited Jun 24 2012
Hello

I have litle problem with function-base index and like with bind variable.
When I use like with bind variable, the CBO don't use my function-base index.
For example when I create table and index:

ALTER SESSION SET NLS_SORT='BINARY_CI';
ALTER SESSION SET NLS_COMP='LINGUISTIC';
alter session set nls_language='ENGLISH';

-- DROP TABLE TEST1;
CREATE TABLE TEST1 (K1 VARCHAR2(32));
create index test1_idx on test1(nlssort(K1,'nls_sort=BINARY_CI'));

INSERT INTO TEST1
SELECT OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;

When I run:
ALTER SESSION SET NLS_SORT='BINARY_CI';
ALTER SESSION SET NLS_COMP='LINGUISTIC';
SELECT * FROM TEST1 WHERE K1 = 'abcd';
or
SELECT * FROM TEST1 WHERE K1 LIKE 'abcd%';

CBO use index.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4vrmp7cshbvqy, child number 1
-------------------------------------
SELECT * FROM TEST1 WHERE K1 LIKE 'abcd%'

Plan hash value: 1885706448

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 2 | 98 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST1_IDX | 2 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("TEST1"."SYS_NC00002$">=HEXTORAW('6162636400') AND
"TEST1"."SYS_NC00002$"<HEXTORAW('6162636500') )


but when I run
SELECT * FROM TEST1 WHERE K1 LIKE :1;
CBO don't use index

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9t461s1669gru, child number 0
-------------------------------------
SELECT * FROM TEST1 WHERE K1 LIKE :1

Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 89 (100)| |
|* 1 | TABLE ACCESS FULL| TEST1 | 2 | 48 | 89 (3)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("K1" LIKE :1)


What should I change to force CBO to use index.
I don't wont use index hint in query.


My oracle version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2012
Added on Jun 23 2012
8 comments
764 views