Thanks for sending the V$MYSTAT data offline.

Try this explanation ... The first query is fetching all the rows using an array size of 15. The V$MYSTAT data showed a difference of about 25000 user calls, and the SQL*Net roundtrips to/from client data below matches that. The number of additional consistent gets is not much less than that. This suggests that there is an extra consistent get for each fetch, unless the previous fetch finished at a database block boundary.

Thanks for your response. I had analyzed the tables and CHAIN_CNT = 0. Just to verify, I also checked table fetch continued row which was 0 on all my tests. Any other ideas?

Analyze the table and see if you have any chained rows. If there are chained rows and if the STATE_CODE field is not always in the last row piece, then an extra consistent gets will be needed to get the column values from the trailing row pieces of chained rows that are not excluded by the where clause predicates.

I have a table (TEST1) made up of 11,333 blocks. No indexes on this table. I ran two queries, both reported to do full table scans (as expected), one returning all the rows from the table and one with a bogus condition resulting in no rows returned. I expected, that since both queries did full table scans, that the amount of I/O would be the same. Yet the query which returned data did 3 times as much I/O as the one which did not. Output follows :

 12:08:22 SQL> set autotrace traceonly exp stat
 12:08:22 SQL> select
 12:08:22   2  *
 12:08:22   3  from
 12:08:22   4  test1
 12:08:22   5  where
 12:08:22   6  pay_dealer_date >= '01/01/2000'
 12:08:22   7  -- and state_code = 'AB'            ---- BOGUS CONDITION
 12:08:22   8  ;

 375043 rows selected.

 Elapsed: 00:00:55.46

 Execution Plan

----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
    1    0   TABLE ACCESS (FULL) OF 'TEST1'

 Statistics

----------------------------------------------------------
           0  recursive calls
          15  db block gets
       35581  consistent gets
       10575  physical reads
           0  redo size
    66817080  bytes sent via SQL*Net to client
     2775646  bytes received via SQL*Net from client
       25004  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
      375043  rows processed


 12:09:35 SQL> select
 12:09:35   2  *
 12:09:35   3  from
 12:09:35   4  test1
 12:09:35   5  where
 12:09:35   6  pay_dealer_date >= '01/01/2000'
 12:09:35   7  and state_code = 'AB'    ---- BOGUS CONDITION
 12:09:35   8  ;

 no rows selected

 Elapsed: 00:00:03.43

 Execution Plan

----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
    1    0   TABLE ACCESS (FULL) OF 'TEST1'



 Statistics

----------------------------------------------------------
           0  recursive calls
          15  db block gets
       11337  consistent gets
       10573  physical reads
           0  redo size
        1860  bytes sent via SQL*Net to client
         313  bytes received via SQL*Net from client
           1  SQL*Net roundtrips to/from client
           0  sorts (memory)
           0  sorts (disk)
           0  rows processed

What am I missing here? Any help appreciated.