Re: select * from <table

From: Jurij Modic <jmodic_at_src.si>
Date: Tue, 06 Jul 1999 20:40:07 GMT
Message-ID: <378a633f.1849859@news.siol.net>

On Tue, 06 Jul 1999 08:59:04 +0530, Anurag Minocha <anurag_at_synergy-infotech.com> wrote:

>Hi,
>I am working on a java application and i have to caplture the table
>structure if the tables in my database by using select * from
><tablename> where 1=2. But this is a fulltable scan.Is there any other
>method by which i can capture the table structures without doing a full
>table scan.

No need to search for a different method. A predicate "where 1=2" will cause the optimizer not to touch the table data at all. Here is a proof:

SQL> SELECT * FROM scott.emp WHERE 1=2;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=560) 1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=560)

Statistics

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

The explain plan indeed suggests there is a full scan performed with this query, however if you look at the statistics (db block gets, consistent gets, physical reads) you can see that the table data wasn't even touched.

>I tried select * from <table_name> where rownum= -1.when i
>tried this with set timing on .This takes more time than select ...1=2..

This is understandable because "where rownum = -1" indeed forces a full table scan. When the first row is selected, it is assigned rownum=1. After the comparison with the WHERE predicate, this row is not included in the result set. Now the second row is also assigned rownum=1 as there is still no rows in the result set and it is also discarded from the result. This goes on and on up to the last row of a table, which is also assigned rownum=1 and discarded. Example:

SQL> SELECT * FROM scott.emp WHERE rownum = -1;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=14 Bytes=560) 1 0 COUNT

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'EMP' (Cost=1 Card=14 Bytes=560
          )

Statistics

          0  recursive calls
          2  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        478  bytes sent via SQL*Net to client
        410  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

As you can see, there really was a full table scan (look at db block gets and consistent gets).

>Please suggest some way.
>
>anurag
>
>reply at
>anurag_at_synergy-infotech.com

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Jul 06 1999 - 15:40:07 CDT

Re: select * from <table_name> where 1=2