Hi All,
I am having a wired issue with Function Based Indexes on Orcale 10.2.0.3 running on Solaris 5.9.
I have created two FBI on two tables using following syntax.
CREATE INDEX EQHUB.IDX_BBO_WRT_DES_FBI ON EQHUB.BBO_WARRANT_PRICING (CASE WHEN latest_version_flag = 'Y' THEN 1 ELSE NULL END);
CREATE INDEX EQHUB.IDX_BBO_DES_FBI ON EQHUB.BBO_DESCRIPTIVE_DATA (CASE WHEN latest_version_flag = 'Y' THEN 1 ELSE NULL END);
For the second command (IDX_BBO_DES_FBI), when i query DBA_IND_EXPRESSIONS view, i found that Oracle has done some kind of QUERY TRANSFORMATION (?) and converted
FBI expression to CASE "LATEST_VERSION_FLAG" WHEN 'Y' THEN 1 ELSE NULL END.At the same time,EXPRESSION on first index is not changed.
Now,my question is what has made transformation to occure only for second index.
I also found that inspite of highly SELECTIVE nature of both the indexes, only SECOND index is being used by CBO (for which trasnformation occured)
and IDX_BBO_WRT_DES_FBI is not being used(FTS is happening instead).
Query is using same expression for both the tables as
(CASE WHEN latest_version_flag = 'Y' THEN 1 ELSE NULL END)=1
INDEX_NAME TABLE_NAME COLUMN_EXPRESSION
----------------------------------- ----------------------------------- ----------------------------------------------------------------------
IDX_BBO_WRT_DES_FBI BBO_WARRANT_PRICING CASE WHEN "LATEST_VERSION_FLAG"='Y' THEN 1 ELSE NULL END
IDX_BBO_DES_FBI BBO_DESCRIPTIVE_DATA CASE "LATEST_VERSION_FLAG" WHEN 'Y' THEN 1 ELSE NULL END
I read that expression should be evaluated including CASE of characters and spaces in query.Is that true?
Appreciating responses in advance.