Other Oracle database topics - Creating empty temp table from an existing view

Author

Creating empty temp table from an existing view

paulfil

2005-09-28, 9:23 am

I am looking for a generic approach to create an empty temp table
matching the definition of given columns of existing view.

To do that I use SQL like the following:

CREATE GLOBAL TEMPORARY TABLE MY_TEMP_TABLE AS
SELECT COLUMN_1, COLUMN_2 FROM MY_VIEW WHERE 1 = 2

The problem I face is that this approach appears to be too costly
because according to explain plan ORACLE scans indexes of all the
tables composing the view despite the fact that WHERE clause constantly
false and no rows should be returned.

Is there a better and faster approach? One condition: the solution
should be fairly generic, with only known factors being column names
and view name.

Mark D Powell

2005-09-28, 11:23 am

Based on experience Oracle will actually only execute the first step
and then terminate (at least on my 9.2+ system). Try the command.

You could read the view column list from dba_tab_columns and use
dynamic SQL via execute immediate to issue the create temporary table
statement.

HTH -- Mark D Powell --

Sponsored Links