Andrea Held / 2008

Kapitel 12

================================================================================
- Partitionen anlegen

create table gespraechsdaten
(
   kunden_id    number,
   start_zeit  date,
   …
)
partition by range (start_zeit)
interval (numtoyminterval(1,'MONTH'))
(
   partition p0701 values less than (to_date('2007-02-01','yyyy-mm-dd'))
);


================================================================================
- Partitionen loeschen

alter table verkauf drop partition y08q1;

================================================================================
- Lokalen Index erstellen

create index idx_verkauf_01
on verkauf (artikel_nr)
local;


================================================================================
- Zugriffsplan anzeigen

SQL> explain plan for select * from verkauf where verkaufsdatum = to_date('06-jun-2008, 'dd-mon-yyyy');

SQL> select * from table(dbms_xplan.display);


================================================================================
- List-List-Partitioning

create table artikel
(
   product_id     number,
   product_code   number,
   beschreibung   varchar2(20),
   status         varchar2(2)
)
partition by list (product_code)
subpartition by list (status)
(
   partition p101 values (101)
   (
      subpartition p101_ct values ('VK'),
      subpartition p101_ny values ('AU'),
      subpartition p101_def values (default)
   ),
   partition p201 values (201)
   (
      subpartition p201_ct values ('VK'),
      subpartition p201_ny values ('AU'),
      subpartition p201_def values (default)
   )
);


================================================================================
- List-Range-Partitioning

create table sales
(
   sales_id     number,
   product_code number,
   status       varchar2(2)
)
partition by list (status)
subpartition by range (product_code)
(
   partition VK values ('VK')
   (
      subpartition ct_100 values less than (101),
      subpartition ct_200 values less than (201)
   ),
   partition AU values ('AU')
   (
      subpartition NY_100 values less than (101),
      subpartition NY_200 values less than (201)
   )
);


================================================================================
- Reference-Partitioning

create table kunde
(
   kundennr    number primary key,
   kundenname  varchar2(200),
   rating      varchar2(2) not null
)
partition by list (rating)
(
    partition p02 values ('01'),
   partition p01 values ('02')
);

create table verkauf
(
   rechnungsnr    number primary key,
   kundennr       number not null,
   ...
   constraint  fk_verkauf_01
    foreign key (kundennr)
    references kunde
)
partition by reference (fk_verkauf_01);


================================================================================
- Reference-Partitiong

create table auftrag
( auftragsnr number(12) constraint orders_pk primary key,
  auftragsdatum date not null,
  auftragsstatus varchar2(1)
)
  partition by range (auftragsdatum )  
     ( partition p_before_jan_2008 values less than
       (to_date('01-01-2008','dd-mm-yyyy')),
  partition p_2008_jan values less than
       (to_date('01-02-2008','dd-mm-yyyy')),
  partition p_2008_feb values less than
       (to_date('01-03-2008','dd-mm-yyyy')),
  partition p_2008_mar values less than
       (to_date('01-04-2008','dd-mm-yyyy'))
);


create table auftragsposition
( auftragsnr number(12) not null,
  product_id number not null,
  anzahl     number not null,
  preis      number not null,
constraint auftragspos_fk foreign key (auftragsnr )
references auftrag(auftragsnr )
)
partition by reference(auftragspos_fk);


================================================================================
- Interval Partitioning

create table verkauf
( artikelnr       number(6) not null,
  kundennr        number not null,
  verkaufsdatum   date not null,
  ...
)
partition by range (verkaufsdatum)
interval(NUMTOYMINTERVAL(1,'MONTH'))
(  partition p_before_1_jan_2008 values
   less than (to_date('01-01-2008','dd-mm-yyyy')));


================================================================================
- Interval Partitioning mit Store in


create table verkauf
( artikelnr       number(6) not null,
  kundennr        number not null,
  verkaufsdatum   date not null,
  ...
)
partition by range (verkaufsdatum)
interval(NUMTOYMINTERVAL(1,'MONTH'))
store in (tbs1,tbs2,tbs3,tbs4)
(  partition p_before_1_jan_2008 values
   less than (to_date('01-01-2008','dd-mm-yyyy')));


================================================================================
- Partitionen pruefen

select table_name, partition_position, partition_name, tablespace_name
from user_tab_partitions
where table_name = 'VERKAUF'
order by 2;

================================================================================
- Tabelle von Range-Partitioning in Interval-Partitioning aendern

alter table verkauf
set interval (NUMTOYMINTERVAL(1, 'MONTH'));

alter table verkauf
set store in (tbs1,tbs2,tbs3,tbs4);

================================================================================
- System Partitioning

CREATE TABLE SysPartTable (spalte_1 integer, spalte_2 date)
PARTITION BY SYSTEM
(PARTITION p1 TABLESPACE tbs_1,
 PARTITION p2 TABLESPACE tbs_2,
 PARTITION p3 TABLESPACE tbs_3,
 PARTITION p4 TABLESPACE tbs_4
);

Weitere Beispiele:
CREATE TABLE SysPartTable (spalte_1 integer, spalte_2 date)
PARTITION BY SYSTEM PARTITIONS 4;

CREATE TABLE SysPartTable (spalte_1 integer, spalte_2 date)
PARTITION BY SYSTEM PARTITIONS 4
TABLESPACE tbs_1;

CREATE TABLE SysPartTable (spalte_1 integer, spalte_2 date)
PARTITION BY SYSTEM PARTITIONS 4
STORE IN (tbs_1,tbs_2,tbs_3,tbs_4);

================================================================================
- Daten in Tabelle mit System Partitioning einfuegen und aendern

sqlplus> insert into SysPartTable partition (sys_p42) 
  2      values (1, sysdate);

SQL> delete SysPartTablewhere spalte_1 = 1;
SQL> delete SysPartTablepartition (sys_p42) where spalte_1 = 1;
sqlplus> update SysPartTable 
  2      set spalte_2 = sysdate +2 
  3      where spalte_1 = 5;

================================================================================
- Virtuelle Spalten als Partitionierungsschluessel

create table reservierung 
(
   reservierungsnr           number not null,
   reservierungsdatum        date,
   hotel_id                  number(3),
   gast_id                   number,
   star_rating               number(1)
          generated always as (substr(hotel_id,1,1)) virtual
)
partition by list (star_rating)
(
   partition stern5 values (5),
   partition stern4 values (4),
   partition stern3 values (3),
   partition andere values (default)
);


================================================================================
- Partition verschieben

ALTER TABLE rechnung MOVE PARTITION vor_1999 TABLESPACE archive_storage;

================================================================================
- Partitionen komprimieren

SQL>create table sal
           (sno number(6),
           last_name varchar2(30),
           salary number(6))
    partition by range(salary) Interval  (5000)
          (
          partition p1 values less than (5000)  COMPRESS ,
          partition p2 values less than (10000) NOCOMPRESS,
          partition p3 values less than (15000) COMPRESS,
          partition p4 values less than (20000) NOCOMPRESS
          );
sqlplus>select table_name, partition_name, compression
  2  from user_tab_partitions
  3  where table_name='SAL';

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
SAL                            P1                             ENABLED
SAL                            P2                             DISABLED
SAL                            P3                             ENABLED
SAL                            P4                             DISABLED

sqlplus> alter table sal move partition P2 compress;
Tabelle wurde geändert.

sqlplus> alter table sal move partition P3 nocompress;
Tabelle wurde geändert.

sqlplus>
  1  select table_name, partition_name, compression
  2  from user_tab_partitions
  3  where table_name='SAL';

TABLE_NAME                     PARTITION_NAME                 COMPRESS
------------------------------ ------------------------------ --------
SAL                            P1                             ENABLED
SAL                            P2                             ENABLED
SAL                            P3                             DISABLED
SAL                            P4                             DISABLED


================================================================================