Home » SQL & PL/SQL » SQL & PL/SQL » What is the meaning of this statement
What is the meaning of this statement [message #293367] Fri, 11 January 2008 20:51 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
create table t3 as select * from t2 where 1=2;

This creates a table with just the structure but what does 1=2 mean? How does the engine decipher it?

- Das
Re: What is the meaning of this statement [message #293369 is a reply to message #293367] Fri, 11 January 2008 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Since 1 never equals 2 then no rows are selected.
The net result is to create an empty table with the exact same structure as the table being SELECTed FROM.

However no indexes or additional constraints are produced.
Re: What is the meaning of this statement [message #293374 is a reply to message #293369] Fri, 11 January 2008 21:53 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks a lot

- Das
Re: What is the meaning of this statement [message #293421 is a reply to message #293369] Sat, 12 January 2008 09:14 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

However no indexes or additional constraints are produced.

Except for not Null constraints
Re: What is the meaning of this statement [message #293441 is a reply to message #293367] Sat, 12 January 2008 19:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
you might also be interested to look up "oracle short circuiting" and "oracle query processing".

"where 1 = 2" is a popular short circuiting technique. Not only does your create table create an empty copy of the source table without indexes, most constraints, privileges, triggers, etc., it also does so without visiting the source table.

No rows of the source table are read. This is because during query "setup", Oracle understands that the where clause can not ever return any rows because of the "where 1 = 2". Therefore it does not go to the source table and look for any. It simply creates a new table using the metadata about the source table (or more correctly, the query layout).

This was very popular in the Oracle 7 days for creating what is called "manually partitioned views". Indeed, short circuit behavior that made manually partitioned views so popular in some circles, is still a valuable concept on any version or Oracle although it has been eclipsed by true partitioning in many cases.

So, do some internet reading on

manually partitioned views
short circuiting
query processing steps

It is some good stuff. Kevin
Re: What is the meaning of this statement [message #293448 is a reply to message #293421] Sat, 12 January 2008 22:38 Go to previous message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks

- Das
Previous Topic: Bulk Collect Tuning
Next Topic: Compare Null Value
Goto Forum:
  


Current Time: Sun Dec 04 19:03:11 CST 2016

Total time taken to generate the page: 0.05901 seconds