Originally posted by julian
When an Oracle block is first read from the DB, it is cleaned and put back. Using SELECT COUNT(*) FROM YOUR_TABLE; will cause Oracle to check dirty blocks in the rollback segments, then mark the block as clean if it finds nothing there. This block clean out is avoided in 8i by using COUNT(1) or COUNT(ROWID), isn't it?
[/B]
No. Delayed block cleanout happens regardles of COUNT(*), COUNT(1), COUNT(rowid), COUNT(whatever).... As soon as you touch "commited" dirty buffers they will be cleaned out by select. It doesn't matter if you select any column from that block at all. Block must be read in all cases, no matter if you read any actual values from table rows or not. In fact, in all cases of COUNT(*), COUNT(1), COUNT(rowid), COUNT(not_null_column) FTS only reads row directory from the block header, it doesn't have to inspect the actual rows at all.