Re: "WHERE 1 = 2" does strange things
- From: BigBoote66@xxxxxxxxxxx
- Date: 28 Sep 2005 13:33:31 -0700
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
.
- References:
- "WHERE 1 = 2" does strange things
- From: paulfil
- "WHERE 1 = 2" does strange things
- Prev by Date: Re: A very slow Select Statement
- Next by Date: Minus in View?
- Previous by thread: "WHERE 1 = 2" does strange things
- Next by thread: Re: "WHERE 1 = 2" does strange things
- Index(es):
Relevant Pages
|
