Ask The Real Tom

igate partitionionirung
=========================

1. Zeitliche partitionierung nach monat
2. partitonierung nach status zum abhängen

Variante 1
============
# list-range partitioning was introduced in 11gR1 as the "Extended Composite Partitioning" new feature.
Open
Zeit
Closed
Zeit

# Tablespace konzept
* managment aufwand
* duplicate / restore
* disk location speed
* compression / reuse space

->
TBSP_OPEN
TBSP_CLOSED_200903
TBSP_CLOSED_200904

Nachteile:
* schlechtere update perfomance wegen row movement

Vorteil
* bessere query perfomance wegen reduziertem i/o
* es können rows mit status closed abgehängt werden

Variante 2
============
Zeit
Open
Closed

Test 1
=========
create user test identified by test;
grant connect to test;
grant resource to test;
connect test/test

create sequence ptest_seq ;
CREATE TABLE ptest
( id             NUMBER
, name           VARCHAR2(60)
, status        VARCHAR2(10)
) PARTITION BY LIST (status)
SUBPARTITION BY RANGE (id)
( PARTITION p_open VALUES ('open')
( SUBPARTITION p_open_small VALUES LESS THAN (8)
, SUBPARTITION p_open_medium VALUES LESS THAN (80)
, SUBPARTITION p_open_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_closed VALUES ('closed')
( SUBPARTITION p_closed_small VALUES LESS THAN (5)
, SUBPARTITION p_closed_medium VALUES LESS THAN (50)
, SUBPARTITION p_closed_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_default VALUES (DEFAULT)
( SUBPARTITION p_default_small VALUES LESS THAN (10)
, SUBPARTITION p_default_medium VALUES LESS THAN (100)
, SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)
)
) ENABLE ROW MOVEMENT;

begin for x in 1..20 loop
insert into ptest (id,status) values (ptest_seq.nextval,'open');
insert into ptest (id,status) values (ptest_seq.nextval,'closed');
end loop;
end;
/
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='open' where mod(id,2)=0;
commit;

select count(1) from ptest partition (p_open);
select count(1) from ptest partition (p_closed);

update ptest set status='closed' where mod(id,2)=0;
commit;

-- add subpartition
alter table ptest SPLIT SUBPARTITION p_closed_high AT (120) into (subpartition p_closed_medium2, subpartition p_closed_high);
select count(1) from ptest subpartition (p_closed_medium2);

-- drop subpartition
alter table ptest drop SUBPARTITION p_closed_small;

-- compress partition
alter table ptest move SUBPARTITION p_closed_medium compress;