what is where 1=0?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: what is where 1=0?

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    139
    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


  2. #2
    Join Date
    Feb 2001
    Posts
    5
    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.

  3. #3
    Join Date
    Oct 2000
    Posts
    139
    Hi

    I was told that this speeds up queries that have many where and like, is this true?

  4. #4
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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 :-)

  5. #5
    Join Date
    Nov 2000
    Posts
    212
    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.

  6. #6
    Join Date
    Jan 2001
    Posts
    318
    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

  7. #7
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    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

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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 :)

  9. #9
    Join Date
    Nov 2000
    Posts
    212
    Just wandering:
    who knows if Oracle optimizes such condition 1=0, i.e. does not perform full scan?


  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width