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