Re: Does it read empty blocks into buffer cache ?

news.blueyonder.co.uk wrote:
> There's a couple of answers to this question:
>
> 1. An index exists on the table.
> if an index exists and the optimiser recognises the index can be used, then
> no full table scan would be made.
>
> 2. The table has been analysed.
> if the table has been analysed the optimiser will recognise there are no
> rows in the table and therefore no full table scan will be necessary.

Now this one is interesting. And wrong. The rule is that 'BLOCKS' in DBA_TABLES tells Oracle what to read, not NUM_ROWS. And since a delete doesn't adjust the HWM, even an analysis will indicate that there are lots of blocks to read. And they will accordingly be read:

SQL> create table huge as select * from dba_objects; Table created.

SQL> insert into huge select * from huge; 29371 rows created.

SQL> /
58742 rows created.

SQL> /
117484 rows created.

SQL> commit;
Commit complete.

SQL> delete from huge;
234968 rows deleted.

SQL> analyze table huge compute statistics; Table analyzed.

[Note the analysis is done AFTER the delete]

SQL> select num_rows, blocks

   2 from dba_tables
   3 where table_name='HUGE';

   NUM_ROWS BLOCKS
---------- ----------

          0 6534

SQL> set autotrace on stat
SQL> select count(*) from huge;

   COUNT(*)

          0

Statistics

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

You might notice that the DBA_TABLES view reports many thousands of blocks, but no rows; and the SQL Plus statistics indicate that, despite not having any rows at all, the scan of the table is still performing 1774 physical reads. Oracle *is* therefore doing a full table scan on this table, and those empty blocks *are* indeed being read.

Regards
HJR
>
> So your solutions is to TRUNCATE the table which will bring the high water
> mark down.
>
>
> "Bass Chorng" <bchorng_at_yahoo.com> wrote in message
> news:bd9a9a76.0301201619.19d6d03f_at_posting.google.com...
>

>>If I have a table with 0 rows but it has a very high highwater mark
>>and if I do a select *, would Oracle scan all empty blocks into
>>the buffer cache ?
>>
>>The known factor is Oracle will perform a full table scan to the
>>highwater mark although the table is empty, and the question
>>is,is Oracle going to flood the buffer cache with all the
>>empty blocks it reads ?
>>
>>I don't see any reason why it wouldn't, as it is the same
>>as if the table is loaded with rows. But then the state of
>>x$bh would be 0 or 1 for those empty blocks that are read
>>in ?
>>
>>I did a test and observed no state change. But I can not
>>tell if it did not read the empty blocks in the first place
>> or if it did, but since the blocks are empty, they are
>>still marked as state 0.
>>
>>Wonder if any body knows the answer.