| functional index and bind variable problem [message #159244] |
Fri, 17 February 2006 00:04  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
