Today a application developer asket me how he should partition his index organized table.
All query include the primary key, no range, no full scan’s.
So I told him it doen’t make sense to partition this table for perfomance reasons.
To profe my statement I did a small Test case.
create table part_range_iot (c1 number, c2 varchar2(20), c3 char(1),
constraint part_range_iot_pk primary key (c1))
partition by HASH (c1)
PARTITIONS 100
/
begin
for i in 1..999
loop
insert into part_range_iot values (i,'abcd','y');
end loop;
commit;
end;
/
set autotrace on
set timing on
select * from part_range_iot where c1 = 99;
The output from the query was
Execution Plan
----------------------------------------------------------
Plan hash value: 3787470184
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 1 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| PART_RANGE_IOT | 1 | 28 | 1 (0)| 00:00:01 | 58 | 58 |
|* 2 | INDEX UNIQUE SCAN | PART_RANGE_IOT_PK | 1 | | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=99)
Statistics
----------------------------------------------------------
104 recursive calls
0 db block gets
14 consistent gets
0 physical reads
So 14 consitent gets were used
The same case without partitioning
create table part_range_iot (c1 number, c2 varchar2(20), c3 char(1),
constraint part_range_iot_pk primary key (c1))
/
begin
for i in 1..1000
loop
insert into part_range_iot values (i,'abcd','y');
end loop;
commit;
end;
/
select * from part_range_iot where c1 = 99;
The results on the IOT table without partitions
Execution Plan
----------------------------------------------------------
Plan hash value: 2398071293
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PART_RANGE_IOT | 1 | 28 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PART_RANGE_IOT_PK | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=99)
Statistics
----------------------------------------------------------
46 recursive calls
0 db block gets
8 consistent gets
0 physical reads
Just 8 consitent reads.
Conclusion:
Partition an IOT on its primary key has just a negative perfomance impact.
The difference is here
TABLE ACCESS BY INDEX ROWID and TABLE ACCESS BY GLOBAL INDEX ROWID.
On the the non partitioned table we have to do just a unique index scan. On the partitioned table we do the same, but we have to find the right partition first.