Re: Select Statement

From: <stevedhoward_at_gmail.com>
Date: 21 Apr 2006 08:12:26 -0700
Message-ID: <1145632346.033702.104170@e56g2000cwe.googlegroups.com>

Are you sure about this? Examine the following extremely simple test case...

/**********************************************************************

--create table

SQL> create table t0421(c number, d number);

Table created.

--load 100000 rows

SQL> begin
  2 for i in 1..100000 loop
  3 insert into t0421 values(mod(i,1000),i);   4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly

--get those rows where d=1 and it is the first rownum that matches the
criteria...

SQL> select d from t0421 where d=1 and rownum<2;

Execution Plan

Plan hash value: 459506577

| 0 | SELECT STATEMENT | | 1 | 13 | 23 (5)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | TABLE ACCESS FULL| T0421 | 3 | 39 | 23 (5)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(ROWNUM<2)
2 - filter("D"=1)

--get only those rows that match the criteria, without the rownum
pseudo column

SQL> select d from t0421 where d=1;

Execution Plan

Plan hash value: 2569877182

| 0 | SELECT STATEMENT | | 3 | 39 | 56 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T0421 | 3 | 39 | 56 (2)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("D"=1)

Note

dynamic sampling used for this statement

--filter only those rows with the rownum of 1

SQL> select d from t0421 where rownum<2;

Execution Plan

Plan hash value: 459506577

| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T0421 | 85354 | 1083K| 2 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(ROWNUM<2)

Note

dynamic sampling used for this statement

**********************************************************************/

The cardinality (and I/O, which I don't show), although reflecting a full table scan, do very little I/O when I filter on the column, even without an index. When I use only the rownum, the cardinality and I/O increases.

Can you post the full trace?

Regards,

Steve Received on Fri Apr 21 2006 - 10:12:26 CDT