Since you can query ASH I assume you can also get the runtime execution plan.
What do the two predicate sections say.
If it's not cost the next obvious guess is NLS conversion or some other
coercion issue.
Sent from my iPad
On 29 Jun 2015, at 19:05, Johan Eriksson <valpis@xxxxxxxxx> wrote:
The table has 30 million rows so it is not that small, and the difference in
execution time is 20ms when using index and 140 secs for a FTS, statistics
are gathered for bot tables and indexes on all environments.
When checking the execution plan it always says it will use index (only
checked with SQL developer so far) but ASH says it has performed a FTS on
11.2.0.4
Will do a trace 10053 here shortly
On Mon, Jun 29, 2015 at 3:48 PM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx> wrote:
I suspect t1 is very small and the cost of the tablescan matches the cost of
the indexed access.
Even in 9i the optimizer could factor out the 1 != 1 in your expression and
end up with optimizing:
select * from t1 where c1 = 1;
explain plan for {your statement};
select * from table(dbms_xplan.display);
check the predice section.
There shouldn't be anything implementing the 1 != 1 bit in the predicate
section.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
