RE: where 1=2

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 29 Jun 2015 13:48:44 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928298462_at_EXMBX01.thus.corp>

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

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Stefan Knecht [knecht.stefan_at_gmail.com] Sent: 29 June 2015 14:21
To: valpis_at_gmail.com
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_at_gmail.com<mailto:valpis_at_gmail.com>> 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

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

Received on Mon Jun 29 2015 - 15:48:44 CEST