Home » RDBMS Server » Performance Tuning » functional index and bind variable problem
functional index and bind variable problem [message #159244] Fri, 17 February 2006 00:04 Go to next message
uv_anil
Messages: 4
Registered: February 2006
Junior Member
functional index and bind variable problem

Oracle version:8i
==================
There is a function based index FT_TEXT_FN_IDX on FILE_TEXT table, which was created to improve the performance but actually the below query is not using this index.

Parameters
===========

query_rewrite_enabled boolean TRUE
query_rewrite_integrity string TRUSTED


SELECT TEXT FROM FI_TEXT WHERE FILE_NAME = :b1 AND TEXT LIKE :b2 AND SUBSTR(TEXT,:b3,12) = :b4


FBI script:
-------------

CREATE INDEX FT_TEXT_FN_IDX ON FI_TEXT
(SUBSTR("TEXT",12,12))
LOGGING
TABLESPACE CON_IDX
PARALLEL ( DEGREE 2 INSTANCES 1 );

In select query when I am replacing bind variable :b3 with value 12 , which is exactly like create functional index script then it is taking index. Is there any way how to go about this problem.

Please help me
Thanks,
Anil
Re: functional index and bind variable problem [message #159269 is a reply to message #159244] Fri, 17 February 2006 01:43 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The query must contain a literal value. Since the CBO comes up with an execution plan at PARSE, which occurs before the BIND, Oracle does not know that you will use 12.

Oracle allows cursor sharing if everything but the bind values is the same. This means that every execution has the same execution plan regardless of the bind values - impossible if you want it to use the index.

Perhaps you can use dynamic SQL (EXECUTE IMMEDIATE).

_____________
Ross Leishman
Previous Topic: Why Merge Cartesian Join
Next Topic: Commit time format in retention table
Goto Forum:
  


Current Time: Thu Mar 28 08:08:54 CDT 2024