Oracle Database Discussions

Hi all,

I have table hash-partitioned on an indexed column "id" which is non-unique and part of my primary key. Inside each partition, rows with the same id are located close to each other which is done by dbms_redefinition reorg using orderby_cols. The intention is to reduce the amount of physical reads since there are no queries that don't filter on the id column.

What I'm seeing is a lot of physical reads though. The first partition has roughly 80K rows, an average row length of 347, block size of 8K and compression ... resulting in 821 blocks. And when (after flushing buffered cache and shared pool) submit a query that filters on "id" only and results is 106 selected rows, I see roughly 1400 physical reads.

--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 106 | 36782 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION HASH SINGLE | | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| XXX | 106 | 36782 | 3 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | XXXXX | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID"=49743)


Statistiken
----------------------------------------------------------
22243 recursive calls
0 db block gets
66651 consistent gets
1404 physical reads
0 redo size
10933 bytes sent via SQL*Net to client
299 bytes received via SQL*Net from client
9 SQL*Net roundtrips to/from client
150 sorts (memory)
0 sorts (disk)
106 rows processed


I was hoping to see around 10-15 physical reads. And I don't understand how it can be 1400 physical reads with only 821 blocks in this partition. Can somebody explain this behavior to me?

thanks in advance,
Dirk