Re: where 1=2

From: Johan Eriksson <valpis_at_gmail.com>
Date: Mon, 29 Jun 2015 20:05:26 +0200
Message-ID: <CABz5TyB3_=yzp5uAas8Q8k8K+V63jA-pHLqkVOhB6jk5c5K2ww_at_mail.gmail.com>

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_at_jlcomp.demon.co.uk
> 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
> _at_jloracle
> ------------------------------
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 29 2015 - 20:05:26 CEST