AW: query uses function based index in DEV1 db but does NOT use it in DEV2 db
- From: Petr Novak <Petr.Novak@xxxxxxxxxxxx>
- To: "litanli@xxxxxxxxx" <litanli@xxxxxxxxx>, oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 25 Apr 2012 15:43:42 +0000
Hi Li,
did you also check statistics on the hidden columns for the function based
index ?
in dba_tab_columns you should find hidden columns with name corresponding to
index columns artificial names
Best Regards,
Petr
Von: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx]" im
Auftrag von "Li Li [litanli@xxxxxxxxx]
Gesendet: Mittwoch, 25. April 2012 16:33
Bis: oracle-l
Betreff: query uses function based index in DEV1 db but does NOT use it in DEV2
db
Hi, List,
I have a query that uses function based index in DEV1 db but does NOT
use it in DEV2 db. I've compared parameters
db_file_multiblock_read_count and optimizer_index_cost_adj, they are
identical between DEV1 and DEV2. I've gathered system stats and stats
on the tables that the query is based on in DEV2. Still the query
refuses to use the index in DEV2. I can use the index() hint to force
it to use it but that's not an accepted way to resolve this issue.
Another strange thing is: even a commented out hint would make the
query to use the index. such as:
select --/*+ index(tranlogtra0_ NU_TTL_CANX) */
I am confused and wondering what else I can do? Version 11.1.0.7.0 on RHEL 5.3
Any suggestions and comments would be greatly appreciated!
-Li
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » AW: query uses function based index in DEV1 db but does NOT use it in DEV2 db - Petr Novak
