-
Hi
we have a select in a table which is
select * from xxx where 1=0 what does this mean?
also there is one
select * from yyy where 1=1
-
I don’t know where you got this select but there can be some answers.
1. Select * from table where 1=0
return just header of the fields (attribute)
but with 0 rows
2. Select * from table where 1=1
return whole table entries
this is same as select * from table
I guess someone set this query for easy change from select all to select none
This is just my opinion.
-
Hi
I was told that this speeds up queries that have many where and like, is this true?
-
the "where" clause returnes a boolean value...so if you out 1=1 this is a true condition and you will take all the recors...for 1=0 this is a false condition and will not return records....so :-)
-
usually this is used for such thing:
you have do build dynamic where clause in application:
str_where:='1=1';
if A is not null then
str_where:=str_where||' AND T.A= '||A;
end if;
if B is not null then
str_where:=str_where||' AND T.B= '||B;
end if;
if C is not null then
str_where:=str_where||' AND T.C= '||C;
end if;
The nice thing is that you do not have to test str_where at every 'if' to add 'AND' inf front of it or not.
-
Yes, we have the similar select's too.
ex. Select min(mat_id) from matrix where 13=0 and
select min(mat_id) from matrix where 0=0.
From what I know they are used for cross field validations.
I don't knwo how tehy work. But assume that i want to make sure that the default rate column and assignment rate column has different values for same min(id).
then you will do 13=0 when they are equal it will be 0=0.
I don't know how it knows that I am comparing rates and why number 13=0 etc.
may be some one with better knowledge will be able to explain this.
Also, they have concerns that this makes the poor performance for big tables. But I have no idea.
thank
Sonali
Sonali
-
Another reason why use this is, when you want to copy the table structure with no records then you purposefully make the condition to be false in order not to get any rows.
Thanks
Kishore Kumar
-
Kishore's answer is the usual use for this.
As for speeding up queries... sure, but only if you don't mind not getting any data back :)
-
Just wandering:
who knows if Oracle optimizes such condition 1=0, i.e. does not perform full scan?
-
True, Oracle does not perform full scan with such a dummy condition which allways results in FALSE. This is also an example how one could be misled in guessing what Oracle will perform by just reading the explain plan. Here is a very simple demonstration.
Let us see what Oracle is telling us what it will do with a simple query, first without a dummy FALSE condition and secondly with it:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> SELECT * FROM SCOTT.EMP;
Execution Plan
----------------------------------------------------------
--0------SELECT STATEMENT Optimizer=CHOOSE
--1----0---TABLE ACCESS (FULL) OF 'EMP'
SQL> SELECT * FROM SCOTT.EMP WHERE 1=0;
Execution Plan
----------------------------------------------------------
--0-----SELECT STATEMENT Optimizer=CHOOSE
--1----0---FILTER
--2----1-----TABLE ACCESS (FULL) OF 'EMP'
We can see that in both example Oracle is telling us it will perform full table scan, and in the case with dummy FALSE condition it will apply an extra filter condition to each fetched row. This implies the dummy FALSE condition might have bad impact on performance on a large table. But let us now see what Oracle realy does by looking at statistics of both cases. Pay attention on values of logical and physical I/O (db block gets, consistent gets, physical reads):
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT * FROM SCOTT.EMP;
14 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
2716 bytes sent via SQL*Net to client
659 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> SELECT * FROM SCOTT.EMP WHERE 1=2;
no rows selected
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
1277 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
We can see that in first case it realy performed a table scan, but in second case with dummy FALSE condition it didn't perform a single I/O operation! So in fact the table scan never took place in second example, although the explain plan tels us so. The truth is Oracle's optimizer is clever enough not to perform a query where WHERE condition always results in FALSE regardles of data contents. It is the same with dummy TRUE condition, although this is not so easy to proof as with FALSE. So the fact is that such dummy condition do not harm the performance at all.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|