Cominciamo a vedere un po’ i dettagli su come si definiscono e si gestiscono tabelle e indici partizionati. Una tabella nasce come partizionata alla sua creazione. In oracle 12.1 sembra sia possibile convertire una tabella non partizionata in partizionata, si tratta però di una cosa che devo ancora studiare quindi per ora non ne scrivo. Riporto solo una piccola annotazione: Christian Antognini, nel suo libro dopo aver descritto nel dettaglio i vari “percorsi” di accesso che può utilizzare l’ottimizzatore Oracle con tabelle partizionate, spiega come nella sua esperienza l’uso del partitioning sia una cosa che deve rientrare nel progetto iniziale di una applicazione e non una cosa che si può aggiungere a posteriori sperando di risolvere dei problemi.
Rimanendo quindi sulla versione 11.2 se abbiamo una tabella esistente, popolata non partizionata e a un certo punto decidiamo di partizionarla, non lo possiamo fare in maniera diretta con un semplice “ALTER”. Che la cosa non possa essere così diretta è anche intuitivo: comunque dovremo spostare fisicamente dei dati per distribuirli su partizioni diverse e “indipendenti”. Cominciamo quindi con un esempio di creazione di una nuova tabella partizionata per “range” su un campo data:
CREATE TABLE range_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')),
PARTITION p_other VALUES LESS THAN (MAXVALUE) );
Con questo comando si crea una tabella che ha cinque partizioni, p0 conterrà i record con valori nel campo time_id inferiori (antecedenti) al 1-1-2008, p1 quelli superiori a quella data e fino al 1-1-2009. p_other conterrà tutti i record con valori superiori a 1-1-2010 o null. Anche se poi ci sono piccoli dettagli che rendono alcune operazioni più “complicate” (vedi ad esempio qui: https://connor-mcdonald.com/2017/08/01/interval-partitioning-just-got-better/). Faccio fatica a trovare un motivo per cui l’esempio precedente non debba utilizzare l’estensione “interval partitioning” che diventerebbe ad esempio:
CREATE TABLE interval_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
In questo caso ho omesso l’ultima partizione con il limite “MAXVALUE”. Con l'”interval partitioning” Oracle prende a riferimento l’ultima partizione e al bisogno creerà nuove partizioni che contengono intervalli di date di un mese. Per questo motivo Oracle non mi lascia eliminare l’ultima partizione:
SQL> ALTER TABLE INTERVAL_SALES DROP PARTITION P0; Table altered. SQL > ALTER TABLE INTERVAL_SALES DROP PARTITION P3; ALTER TABLE INTERVAL_SALES DROP PARTITION P3 * ERROR at line 1: ORA-14758: Last partition in the range section cannot be dropped
(ed è il tema trattato nel post di Connor McDonald che ho citato sopra)
Inseriamo un po’ di dati a caso e vediamo cosa succede:
create sequence sinterval_sales;
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-11-2004','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-11-2008','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-02-2009','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-09-2009','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-09-2010','dd-mm-yyyy'));
insert into interval_sales (prod_id,cust_id,time_id) values (sinterval_sales.nextval,1, to_date('10-05-2010','dd-mm-yyyy'));
SQL > select partition_name, high_value,interval from user_tab_partitions where table_name='INTERVAL_SALES';
PARTITION_NAME HIGH_VALUE INT
------------------------------ -------------------------------------------------------------------------------- ---
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA NO
SYS_P145 TO_DATE(' 2010-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES
SYS_P146 TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA YES
Ora vediamo un esempio di partizionamento per “lista”:
CREATE TABLE list_sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY list (channel_id)
( PARTITION p0 VALUES ('A'),
PARTITION p1 VALUES ('B'),
PARTITION p3 VALUES ('C','D'),
PARTITION p_other VALUES (DEFAULT) );
E senza esitazione passiamo a un esempio di hash partitioning:
CREATE TABLE hash_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY hash (prod_id) ( PARTITION p0, PARTITION p1, PARTITION p3, PARTITION p4);
Oppure:
CREATE TABLE hash2_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY hash (prod_id) partitions 8;
L’hash partitioning prevede due sintassi leggermente diverse. Nel primo caso ho la possibilità si specificare in creazione proprietà diverse per ciascuna partizione, ad esempio tablespace diverse, nel secondo gli dico solo di fare 8 partizioni.
Farei un ultimo esempio di creazione introducendo il “subpartitioning”:
CREATE TABLE p_ticket
( atickid number(10,0),
atickdataora timestamp(6),
atickfk number(10,0),
atickdescrizione varchar2(100 char)
)
PARTITION BY RANGE (atickdataora) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (atickfk)
SUBPARTITION TEMPLATE
( SUBPARTITION P_1 VALUES (1)
, SUBPARTITION P_2 VALUES (2)
, SUBPARTITION P_3 VALUES (3)
, SUBPARTITION P_4 VALUES (4)
, SUBPARTITION P_5 VALUES (5)
, SUBPARTITION p_others VALUES (DEFAULT)
)
( PARTITION before_2018 VALUES LESS THAN (TO_DATE('01-01-2018','dd-MM-yyyy'))
( SUBPARTITION before_2018_s1 values (DEFAULT) )
);
INSERT INTO P_TICKET (ATICKID,ATICKDATAORA,ATICKFK,ATICKDESCRIZIONE) VALUES (1,to_date('18-07-1973','dd-mm-yyyy'),1,'a');
INSERT INTO P_TICKET (ATICKID,ATICKDATAORA,ATICKFK,ATICKDESCRIZIONE) VALUES (1,to_date('21-01-2019','dd-mm-yyyy'),2,'b');
L’esempio sopra è un po’ particolare perché ho voluto metterci dentro un po’ di tutto. Ho utilizzato un “SUBPARTITION TEMPLATE”, è una tecnica alternativa allo specificare le sottopartizioni per ogni partizione esplicitamente. Nel caso di sottopartizionamenti di tipo INTERVAL-* l’uso del template è obbligatorio in quanto indica ad Oracle come partizionare le nuove partizioni create per gli intervalli. Il template è modificabile e incide sullo schema di sottopartizionamento delle nuove partizioni create. Poi ho anche definito per la partizione di partenza uno schema di sottopartizionamento diverso. Se avessi definito più partizioni avrei potuto definire per ciascuna partizione uno schema di sottopartizionamento diverso. Per non farla troppo lunga non riporto qui i test che ho fatto ma posso dire che se non si specifica il template le nuove partizioni avranno una unica sottopartizione che prendono tutti i valori.
Oracle and other 