Re: query uses function based index in DEV1 db but does NOT use it in DEV2 db
Date: Wed, 25 Apr 2012 11:44:55 -0500
Message-ID: <CAN-mCbMSkd4O3_vgHuuGG7NL5n2pkxSmbvFFmOSCsbqhr-3xPw_at_mail.gmail.com>
You are right on the comment... I "thought" I had seen putting -- in front of a hint to make a difference, I was wrong :-(
The plan shows identical order of which table is accessed, the only difference is that in DEV1 it's using the index, while in DEV2 it's using full table scan.
The hidden column for the FBI has up to date stats, and v$sys_optimizer_env is identical between DEV1 and DEV2.
The query is against a view, which is based on the table that has the FBI.
On Wed, Apr 25, 2012 at 11:30 AM, Taylor, Chris David
<ChrisDavid.Taylor_at_ingrambarge.com> wrote:
> First, a hint is already "commented out" (I believe) by the user of /* and */. Those are alternative comment marks - right? So, Oracle will see your hint regardless if you put "--" in front of it or not. :)
>
> Secondly, examine the plan - which table is accessed first, second, third in both Oracle instances.
>
> It's possible Oracle is choosing a different table first, second etc which is causing Oracle to not use the Index you're expecting because then your FBI no longer is the most selective for what it is doing.
>
> Just some ideas to consider.
>
> Chris Taylor
>
> "Quality is never an accident; it is always the result of intelligent effort."
> -- John Ruskin (English Writer 1819-1900)
>
> Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Li Li
> Sent: Wednesday, April 25, 2012 9:34 AM
> To: oracle-l
> Subject: 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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Apr 25 2012 - 11:44:55 CDT