Re: "rename" and "create table x as select *" questions

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 01 Jun 1999 21:06:27 GMT
Message-ID: <375442b6.2869297@news.siol.net>

On Tue, 1 Jun 1999 14:12:29 -0400, "Jerry Gitomer" <jgitomer_at_hbsrx.com> wrote:

>Hi,
>
> Instead of doing "create table x as select * from table y where 1=2;"
>do
>
> CREATE TABLE x AS SELECT * FROM y WHERE ROWNUM < 2:
> TRUNCATE TABLE x;
>
>Since only the first row of the table is retrieved this will run very
>quickly.
>

There is no need to do this - it will be fast but it will be slower than "SELECT ... WHERE 1=2" :-(.

As a matter of fact, if you feel more comfortable with ROWNUM as a limiting factor, why then not simply use "WHERE ROWNUM < 1" ? It will be (negligibly) faster and there will be no need for truncating the table. But it will perform exactly the same as "WHERE 1=2". The following examples illustrate this. The first select is just to show the size of a table and the time to perform full scan. The second one is the one you suggested, the third and the forth one are the fastest ones. Although it might not be self evident from the explain plans, the statistics show that for the last two selects Oracle actually doesn't even try to select any data from the table!

SCOTT_at_PO73> set timing on
SCOTT_at_PO73> set autotrace traceonly
SCOTT_at_PO73> 
SCOTT_at_PO73> select * from test;

21376 rows selected.

 real: 9780

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (FULL) OF 'TEST' Statistics

---

          0  recursive calls
          2  db block gets
       2544  consistent gets
        832  physical reads
          0  redo size
    2075761  bytes sent via SQL*Net to client
      15979  bytes received via SQL*Net from client
       1428  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      21376  rows processed

SCOTT_at_PO73> select * from test where rownum < 2;

 real: 710

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 COUNT (STOPKEY)
   2 1 TABLE ACCESS (FULL) OF 'TEST' Statistics

---

          0  recursive calls
          2  db block gets
          1  consistent gets
          8  physical reads
          0  redo size
        325  bytes sent via SQL*Net to client
        321  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT_at_PO73> select * from test where rownum < 1;

no rows selected

 real: 710

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 COUNT (STOPKEY)
   2 1 TABLE ACCESS (FULL) OF 'TEST' Statistics

---

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        321  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SCOTT_at_PO73> select * from test where 1=2;

no rows selected

 real: 720

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 FILTER
   2 1 TABLE ACCESS (FULL) OF 'TEST' Statistics

---

          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        314  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

>regards
>
>Jerry Gitomer

Regards,

Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jun 01 1999 - 16:06:27 CDT