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
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Knecht [knecht.stefan@xxxxxxxxx]
Sent: 29 June 2015 14:21
To: valpis@xxxxxxxxx
Cc: oracle-l-freelists
Subject: Re: where 1=2
If you're not sure why the optimizer does what it does, a Wolfgang ( event
10053 ) can provide insight.
Run it with the event enabled on both versions and compare its choices
Stefan
On Jun 29, 2015 6:34 PM, "Johan Eriksson"
<valpis@xxxxxxxxx<mailto:valpis@xxxxxxxxx>> wrote:
Hi
To use this when creating an empty table (create table t1 as select * from t2
where 1=2) I have done many times, but now I am looking at a query like this:
select *
from t1
where 1!=1
or c1 = '1';
Back in the days (oracle 7) I know we used this form to try to force a full
table scan and I haven't seen it on many years.
The query uses primary key index when executed on 11.2.0.3 but on 11.2.0.4 it
does a full table scan (c1 is unique primary key).
Even when I used this myself many years ago I didn't know why it worked, it
mostly just did. What happens when the optimizer encounter this? Why could this
sometimes force a full table scan?
/johan