Re: select * from <table_name> where 1=2

Nice demonstration.

Interesting quirk though on the rownum approach. My favourite has always been:

    where rownum < 1

which, reviewing the rownum = -1 results, looks as if it too should actually do a full table scan - but it doesn't.

I suspect, by the way, that the 'where 1 = 2' option works because of a a relatively recent innovation to the optimizer (7.1+ perhaps) put in to allow for partition elimination in partition views.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Jurij Modic wrote in message <378a633f.1849859_at_news.siol.net>...
>
>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
> 478 bytes sent via SQL*Net to client
> 402 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
>
>SQL> SELECT * FROM scott.emp WHERE rownum = -1;
>
>no rows selected
>
>Statistics
>----------------------------------------------------------
> 0 recursive calls
> 2 db block gets
> 15 consistent gets
> 0 physical reads
> 0 redo size
> 478 bytes sent via SQL*Net to client
> 410 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 0 rows processed
>
Received on Sat Jul 10 1999 - 11:25:06 CDT