RE: query uses function based index in DEV1 db but does NOT use it in DEV2 db
- From: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- To: 'Li Li' <litanli@xxxxxxxxx>
- Date: Wed, 25 Apr 2012 12:09:26 -0500
Another possibility...
Have you compared the views just to double-check that they are identical in
DEV1 & DEV2?
You may also want to compare the indexes on the table(s) in question to verify
they are in fact identical. Since accesses are based on selectivity, Oracle
has determined that the selectivity of the FBI in dev1 is optimal, while the
selectivity of the index in DEV2 is not-optimal (or that a FTS is optimal if
you prefer)
If a full table scan is preferred in DEV2, then Oracle has made the
determination that it's going to need a certain amount of the data from the
table regardless of any indexes so that it is *cheaper* to just do a full table
scan from the beginning. Right?
There are times where if you've asked for some percentage of a table's rows,
but because that percentage is spread out across the whole table, that Oracle
will favor a FTS even when asked for a small percentage of the table's rows.
(I was trying to find an example of this but can't put my fingers on it at the
moment...)
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: Li Li [mailto:litanli@xxxxxxxxx]
Sent: Wednesday, April 25, 2012 11:45 AM
To: Taylor, Chris David
Cc: oracle-l
Subject: Re: query uses function based index in DEV1 db but does NOT use it in
DEV2 db
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
