RE: where 1=2

From: Jonathan Lewis <>
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.

Jonathan Lewis

From: [] on behalf of Stefan Knecht [] Sent: 29 June 2015 14:21
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


On Jun 29, 2015 6:34 PM, "Johan Eriksson" <<>> 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 but on 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?


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

Original text of this message