Re: "WHERE 1 = 2" does strange things

paulfil wrote:
> "WHERE 1 = 2" is a known trick to clone table structure. It should
> create an empty table. It should work very fast.
> IT DOES NOT. At least when cloning a structure of a view.
>
> Try it and look at explain plan. For some reason, even if it should be
> absolutely clear to ORACLE optimizer that no rows ought to be return,
> it still scans indexes of the tables forming the view. On complex
> views with large tables this turns out to be a costly process.
>
> Does anybody know more effective method to clone structure of a view
> into a new table?

I think you forgot to include the explain plan & view definition in your request. I'm guessing that the view in question may do some aggregation (group by logic), so it may be processing all the tables in order to resolve the view.

However, you could try some other options:

CREATE TABLE NewTable as SELECT * FROM MyView WHERE Rownum < 1;

Otherwise, if that's also slow, you could write a PL/SQL procedure that takes a table name & new table name and generates some the Create Table sql by querying the user_tab_columns table and execute it with the dbms_sql package. In order to do so, you'll have to make sure you've explicitly granted CREATE TABLE privileges directly to the user running the dbms_sql command (granting them a role that contains CREATE TABLE privileges will not work). That will always be fast, it's just a bit of a hassle to write.

-Steve Received on Wed Sep 28 2005 - 15:33:31 CDT