Oracle Partitioning: 5^ parte, operazioni sulle partizioni

Una volta che si ha una tabella partizionata vi sono diverse operazioni che si possono fare.

Partition exchange

la prima operazione di modifica su tabelle di cui parlo è secondo me molto interessante. E’ utile in ambienti di datawarehouse ma è anche un’operazione che può tornare utile nel caso si voglia passare da una tabella non partizionata a una partizionata. Di fatto questa operazione permette di scambiare il segmento tra una tabella e una partizione. Questa tecnica risulta utile nei database datawarehouse per caricare massivamente blocchi di dati. Io ora faccio un esempio di passaggio da una tabella non partizionata  a una partizionata:

SQL>CREATE TABLE ticket
2 ( atickid number(10,0),
3 atickdataora timestamp(6),
4 atickfk number(10,0),
5 atickdescrizione varchar2(100 char)
6 );
Table created.

SQL>create index idx_ticket_2 on ticket (atickdataora);
Index created.

SQL>create index idx_ticket_3 on ticket (atickfk);
Index created.

SQL>begin for i in 1..10000 loop insert into ticket (atickid,atickdataora,atickfk,atickdescrizione) values (i,sysdate-520+i/100,mod(i,100),i); end loop; end;
2 /
PL/SQL procedure successfully completed.

SQL>commit;
Commit complete.

SQL>CREATE TABLE p_ticket
2 ( atickid number(10,0),
3 atickdataora timestamp(6),
4 atickfk number(10,0),
5 atickdescrizione varchar2(100 char)
6 )
7 PARTITION BY RANGE (atickdataora) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
8 SUBPARTITION BY LIST (atickfk)
9 SUBPARTITION TEMPLATE
10 ( SUBPARTITION P_1 VALUES (1)
11 , SUBPARTITION P_2 VALUES (2)
12 , SUBPARTITION P_3 VALUES (3)
13 , SUBPARTITION P_4 VALUES (4)
14 , SUBPARTITION P_5 VALUES (5)
15 , SUBPARTITION p_others VALUES (DEFAULT)
16 )
17 ( PARTITION before_2018 VALUES LESS THAN (TO_DATE('01-01-2018','dd-MM-yyyy'))
18 ( SUBPARTITION before_2018_s1 values (DEFAULT) )
19 );
Table created.

SQL>create index idx_p_ticket_2 on p_ticket (atickdataora) local;
Index created.

SQL>create index idx_p_ticket_3 on p_ticket (atickfk) local;
Index created.

SQL>ALTER TABLE p_ticket EXCHANGE SUBPARTITION BEFORE_2018_S1 WITH TABLE ticket including indexes WITH VALIDATION ;
Table altered.
SQL>@count p_ticket
COUNT(*)
----------
10000

SQL>@count ticket
COUNT(*)
----------
0

SQL> select subpartition_name,index_name,status from user_ind_SUBPARTITIONS where INDEX_name LIKE 'IDX_P_TICKET%';
SUBPARTITION_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
BEFORE_2018_S1 IDX_P_TICKET_2 USABLE
BEFORE_2018_S1 IDX_P_TICKET_3 USABLE

La gestione degli indici può essere un po’ complicata, ad esempio se ho un indici univoco sulla tabella di partenza non me lo posso portare sulla tabella partizionata con l’exchange.

 

Add, Drop, Merge e Split

Riunisco in un unico capitoletto queste quattro operazioni perché penso di avere poco da dire. Parlo di partizioni o sottopartizioni nel caso la modalità sia per lista (list) o per intervallo (range), per la modalità hash le cose sono leggermente diverse.

Se devo aggiungere nuove partizioni per liste di valori o intervalli che non erano gia previsti posso facilmente fare un “ADD”. Sembra che sulle tabelle con partizionamento INTERVAL non sia possibile farlo:

SQL>alter table p_ticket add PARTITION before_2019 VALUES LESS THAN (TO_DATE('01-01-2019','dd-MM-yyyy'));
alter table p_ticket add PARTITION before_2019 VALUES LESS THAN (TO_DATE('01-01-2019','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

La cosa ha un senso, le partizioni vengono aggiunte in automatico. Forse si può aggirare l’ostacolo ma non vedendone il motivo per ora non me ne occupo. Da notare che non si possono aggiungere partizioni “prima”, per questo tipo di operazioni si fa lo split.

SQL > alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2007','dd-MM-yyyy'));
alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2007','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

 

In questo caso comunque:

SQL > alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy'));
alter table range_sales add PARTITION p_1 VALUES LESS THAN (TO_DATE('01-01-2011','dd-MM-yyyy'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
GEOCALL@scmspagna_svil > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

 

Avendo definito la partizione POTHER con limite “MAXVALUE” non posso aggiungere partizioni, qui occorre usare la funzione di “SPLIT”. Lo split è l’operazione per cui si può suddividere una partizione in due partizioni, ecco un esempio:

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

SQL > alter table RANGE_SALES split partition pOTHER at (TO_dATE('01-01-2011','DD-MM-YYYY')) into (Partition P4, Partition POTHER);
Table altered.

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

La prossima possibile operazione è quella di “drop”, di eliminazione di una partizione:

SQL > alter table range_sales drop partition p0;
Table altered.

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

Infine, è possibile riunire in unica partizione due partizioni:

SQL > ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P3;
ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P3
*
ERROR at line 1:
ORA-14275: cannot reuse lower-bound partition as resulting partition

SQL > ALTER TABLE range_sales MERGE PARTITIONS P3, P4 INTO PARTITION P4;
Table altered.

 

La documentazione afferma che le due partizioni originali vengono eliminate e ne viene creata una nuova.

Per tutte le operazioni non ho parlato di indici, cerco di riassumere in breve come avviene la loro gestione anche se confesso di non aver testato nel dettaglio tutti i casi. Per tutte le operazioni è prevista una clausola “UPDATE INDEXES” che si occupa di gestire aggiornando gli indici. Se la clausola non è specificata in generale quello che può accadere è che gli indici globali diventano invalidi (“UNUSABLE”) mentre per gli indici “locali” sono inusabili le singole partizioni coinvolte. Nel caso di ADD ho notato che ciò non accade, probabilmente perché è una situazione più facile da gestire. Nel caso di DROP non avviene se la partizione è vuota (almeno così è risultato da un piccolo test su una tabella partizionata per intervallo.

Row Movement

Tutte le operazioni di manipolazione delle partizioni non ne sono intaccate, però per default sulle tabelle non è abilitato il “row movement”, questo significa che operazioni DML che cambiano il rowid di un record non sono possibili. Un operazione di update che modifica la chiave di partizionamento di un record in modo da farlo ricadere su una partizione diversa da quella in cui risiede è uno di quei casi (per la verità non so se ne esistono altri). Riporto un esempio completo:

SQL > select PARTITION_NAME,HIGH_VALUE from user_tab_Partitions where table_name='RANGE_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P1 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
POTHER MAXVALUE

SQL > select * from range_sales;
PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- ------------------- - ---------- ------------- -----------
2 2 08-03-2009 00:00:00 B 2 2 2
1 1 18-04-2010 00:00:00 A 1 1 1

SQL > update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1;
update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL > ALTER TABLE RANGE_SALES ENABLE ROW MOVEMENT;
Table altered.

SQL > update range_sales set time_id=to_date('12-01-2011','dd-mm-yyyy') where prod_id=1;
1 row updated.
 

Vi sono poi altre operazioni di modifica delle partizioni, ad esempio TRUNC o MOVE, per i dettagli però rimando al manuale.

 

Annunci

Oracle Partitioning: 4^ parte, creazione tabelle partizionate

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 Partitioning: 3^ parte, modalità di partizionamento

Siccome mi trovo a lavorare su un database con versione 11gR2 parto con le caratteristiche e funzionalità disponibili in questa versione. Cercherò poi di parlare delle estensioni introdotte con le versioni successive.

Le modalità di partionamento disponibili sono tre:

  • range

  • list

  • hash

Vi è poi la possibilità di estendere il partizionamento a un secondo livello, cioè si partizionano a loro volta le partizioni; in questo caso si parla di “composite partitioning” o “subpartitioning”. Sono possibili tutte le combinazioni; tratto in inganno da questa parte del manuale pensavo facessero eccezione le combinazioni con primo livello basato su hash ma il manuale “SQL Language Reference” non prevede questa esclusione; nel dubbio ho provato con successo a creare una tabella partizionata per hash e sottopartizionata ancora per hash (che poi sia utile è un altro discorso).

Interessante il fatto, scoperto con dei test, che posso avere “sotto-partizionamenti” diversi per partizioni diverse, farò vedere in seguito un esempio di cosa intendo. Questo per ribadire che c’è grande flessibilità. Solo per range e hash è possibile specificare una chiave di partizionamento basata su più colonne, il numero massimo di colonne su cui è possibile basare il partizionamento è 16. L’hash partitioning su più colonne è possibile dalla versione 11.2. E’ possibile anche partizionare su colonne virtuali.

Il numero massimo di partizioni e sottopartizioni possibili è 1024K-1 (1024*1024-1), come specificato nel manuale “SQL Language Reference“, assieme ad altri limiti e dettagli.

Range partitioning

Con questo schema di partizionamento i dati vengono suddivisi sulla base di intervalli prefissati sui valori dei campi scelti come chiave. Ho già accennato all’esempio classico del campo data ma può essere anche un campo numerico. Le partizioni si definiscono impostando sempre l’estremo superiore, vi è poi la possibilità di definire una partizione che contiene tutti gli altri valori usando come limite la parola chiave “MAXVALUE”. I record per cui i campi della chiave di partizionamento sono NULL vengono messi nella partizione con limite superiore MAXVALUE. Una estensione di questa cosa, che però non permette valori null è l'”INTERVAL Partitioning” di cui spiegherò meglio i dettagli più avanti.

List partitioning

Questa schema di partizionamento si basa sulla specifica esplicita di valori per i campi chiave del partizionamento per cui i record devono cadere nella specifica partizione. L’esempio riportato dal manuale parla di singoli stati per elementi da elencare. Per i valori non specificati esplicitamente si può definire una partizione per cui anziché la lista di valori si specifica la parola chiave “DEFAULT” (nella versione 12.2 è stato introdotto l'”Automatic List Partitioning”).

Hash partitioning

Questo schema di partizionamento è particolare e come tale risulta utile solo in casi specifici. In questo caso non si predetermina la partizione di destinazione di un record, la partizione viene selezionata da Oracle sulla base di una funzione di hash che viene applicata alla chiave di partizionamento. In fase di creazione della tabella partizionata quindi si specificano i campi chiave e il numero di partizioni, per una distribuzione equa dei dati tra le partizioni Kyte spiega e dimostra nel suo libro come sia necessario che il numero di partizioni sia sempre una potenza di due.

Mi sembra di poter affermare che la maggior utilità data da questo schema di partizionamento sia quella di ridurre la concorrenza nell’accesso alle strutture di gestione del segmento in fase di inserimento e aggiornamento dati. Certo abbiamo ancora partizioni più piccole su cui lavorare e “indipendenti”, che possono essere messe offline separatamente dalle altre, però i dati sono distribuiti in modo imprevedibile fra le partizioni, quindi qualunque sia la partizione offline è possibile, se non probabile, che contenga dei dati che in quel momento servono. Se si fanno interrogazioni per intervalli questo schema di partizionamento non può che avere un peggioramento in termini di prestazioni.

Estensioni

Agli schemi base di partizionamento e sottopartizionamento descritti in precedenza si sono aggiunte nel tempo delle estensioni che aumentano la flessibilità e il campo di utilizzo del partitioning riducendo le operazioni di manutenzione. Queste estensioni sono:

  • Interval partitioning

  • Reference partitioning

Interval partitioning

Secondo me una delle estensioni più utili introdotte nel tempo, si tratta di una estensione del “range partitioning” che permette la creazione automatica di partizioni per intervalli determinati. Facciamo l’esempio più facile e classico, un partizionamento per intervalli di data. Supponiamo di partizionare la tabella degli ordini per anno, possiamo partire con una singola partizione per l’anno corrente, quanto si inseriranno valori con date successive Oracle crea in automatico la partizione relative a quell’anno. Prima che fosse disponibile questa estensione l’unica possibilità era pre-creare in anticipo le partizioni necessarie.

Reference partitioning

Mentre l’estensione “interval partioning” semplifica molto la gestione senza aggiungere molto alle prestazioni, il reference partitioning interviene sul lato delle prestazioni, permettendo di partizionare una tabella legata tramite una chiave esterna ad un’altra già partizionata. Lo scenario è quello classico delle tabelle in relazione, dette anche master-detail o padre-figlio. Facciamo l’esempio delle tabelle ordini, testate e righe. Abbiamo partizionato la tabella delle testate in base alla data (range partition), possiamo partizionare la tabella delle righe in modalità reference partitioning in modo che i record sulla tabella delle righe degli ordini sono partizionanati in modo che i dati di una partizione della tabella padre abbiano i record figli tutti nella stessa partizione nella tabella figlia. Questo si riflette in un miglioramento nelle prestazioni nei casi in cui i dati vengano interrogati con operazioni di join.

Partizionamento degli indici

Come è possibile partizionare una tabella è possibile partizionare gli indici, seppur con qualche differenza. Non se ne parla molto, forse perché ha poco senso, però è possibile anche partizionare un indice su una tabella non partizionata.

Una prima suddivisione sulla modalità di partizionamento degli indici è quella fra indici LOCAL e indici GLOBAL. Nel primo caso gli indici sono partizionati allo stesso modo della tabella a cui si riferiscono, questo fa si che si estenda il livello di migliore gestione conseguente al partitioning e si aumenti la “disponibilità”; se spostiamo o mettiamo offline una singola partizione ne subirà le conseguenze solo la relativa partizione sugli indici. Nel caso indici partizionati GLOBAL l’indice viene partizionato con un criterio diverso dalla sua tabella, le possibilità sono limitate a range e hash, quindi non è prevista la possibilità di partizionare globalmente un indice in modalità “LIST”. Vi è poi la possibilità di definire alla creazione un indice come “GLOBAL” ma senza nessun criterio di partizionamento, in questo caso semplicemente l’indice non è partizionato. La documentazione (qui quella relativa alla versione 18c) afferma che un indice globale è composto da un unico B-tree. Nel caso di indici partizionati globalmente o non partizionati se una partizione viene eliminata o messa offline l’indice diventa inusabile. Per poter definire un indice univoco esso deve essere globale o contenere la chiave di partizionamento. Gli indici possono essere prefissati o no, se hanno come prima parte la chiave di partizionamento sono prefissati altrimenti no, gli indici globali possono essere solo prefissati, cioè le prime colonne su cui devono essere indicizzati devono essere la chiave di partizionamento. Riassumendo quindi, gli indici partizionati vengono classificati in tre tipologie:

  • “Local prefixed”

  • “Local nonprefixed”

  • “Global prefixed”

Oracle Partitioning: 2^ parte, i vantaggi

Come ho accennato nella prima parte i vantaggi attribuiti a Oracle partitioning sono comunemente questi tre:

  • Migliori prestazioni, sia delle query che delle operazioni “DML”

  • Facilitazione della gestione

  • Migliore “disponibilità”

Vediamoli meglio nel dettaglio.

Migliori prestazioni

si tratta senza dubbio di quello viene maggiormente considerato quando si parla di partitioning. Il guaio è che spesso lo si fa senza una analisi un minimo approfondita di come funziona il partitioning e di come può contribuire a migliorare le condizioni.

Per molti sembra che partitioning equivalga in automatico a migliori prestazioni, soprattutto nelle interrogazioni SQL, indipendentemente dal volume dei dati e dal tipo di interrogazioni fatte su questi dati. Nel suo libro, kyte dedica molto spazio a spiegare come spesso Partitioning venga percepita come una soluzione per migliorare in automatico le prestazioni e spiega come invece il miglioramento delle prestazioni sia solo una parte dei vantaggi che può dare e che li da solo in alcuni specifici ambiti. Va ricordato che Partitioning è una “opzione” del database Oracle. Una “opzione” del database Oracle significa gia parlare di Enterprise Edition, non Standard Edition. Solo  questo significa un aumento del costo delle licenze non trascurabile. Poi la seconda regola delle “opzioni” Oracle (dopo il fatto che sono disponibili solo con la Enterprise Edition) è che sono a pagamento. Da quel che ho capito da una rapida occhiata al listino Oracle passare da licenza Standard Edition a Enterprise Edition con Partitioning significa, al netto di sconti, un aumento da 3 a 4 volte del costo delle licenze. Presumo poi che ci siano svariate tipologie di accordi commerciali che possono ridurre il rapporto di costo ma su questo ne so ben poco e se ne sapessi non ne potrei certo parlare qui; già il listino viene marcato come “confidenziale”. Il sospetto che ho, alimentato dal fatto che più volte mi è stato chiesto: “perché non usiamo partitioning?…” è che qualcuno spinga per giustificare il costo delle licenze, senza avere una visione tecnica di dettaglio.

La verità è che il partitioning da molti vantaggi, molti dei quali li ho realmente capiti solo facendo diversi test dopo aver letto la documentazione approfonditamente. In molti casi le prestazioni non cambiano o possono avere anche un peggioramento. Il tutto in cambio di una gestione leggermente diversa. Devo ammettere che prima di lavorarci e fare un po’ di prove ero prevenuto e attribuivo alla gestione del partitioning un sovraccosto superiore a quello reale. Va anche detto che versione dopo versione sono state introdotte nuove funzionalità e migliorie che hanno reso questa opzione veramente potente e flessibile.

Come accennato prima il partitioning permette di suddividere una grossa tabella in tante piccole tabelline (segmenti), la tabella viene comunque vista come una unica tabella. Il criterio con qui suddividere la tabella viene fissato all’inizio e si basa sui valori che le righe della tabella hanno per una o più colonne prefissate. Il criterio può essere per intervallo (range, ad esempio intervalli di date) o per una lista di valori (se il campo prescelto ha un numero di valori distinti “limitato”). Oppure può essere “casuale” sulla base di una funzione di hashing sui valori di un campo.

Ad esempio, supponiamo di avere la classica tabella degli ordini possiamo pensare di partizionarla per intervalli di date, ad esempio con una partizione per ogni anno. In alternativa possiamo pensare di suddividere gli ordini per filiale dalla quale sono partiti, in questo caso probabilmente può andare bene il partizionamento per lista di valori (list partitioning), la lista può comprendere uno o più valori, quindi ogni partizione può contenere gli ordini di una o più filiali. Infine, possiamo suddividere la tabella in più partizioni sulla base dell’hash dell’id univoco usato come chiave primaria. La tabella degli ordini sarà vista dalle query SQL sempre come non fosse cambiato nulla, nel senso che le query sono scritte allo stesso modo; l’ottimizzatore di Oracle però è in grado di utilizzare il cosiddetto “partition pruning”, cioè al momento di prelevare i dati può escludere le partizioni non rilevanti se nei filtri delle query sono inclusi i campi sui cui valori si è partizionata la tabella. Prendendo la nostra tabella degli ordini partizionata per anno, se andiamo a cercare gli ordini relativi solo a un singolo anno l’ottimizzatore sarà in grado di andare direttamente e solo sulla partizione su cui stanno i record per cui i valori del campo data ricadono in quell’anno. Ecco, prima di andare avanti, se interrogo la tabella senza dare un filtro sulla data ma ad esempio uno solo sull’area geografica di origine dell’ordine allora il fatto di aver partizionato la tabella non migliorerà le prestazioni, anzi potrebbe peggiorarle, avendo da esaminare più segmenti. Il concetto di base che per quella che è la mia esperienza è che se la tabella che si vuole partizionare poi non viene interrogata prevalentemente con filtri sul o sui campi per cui è partizionata allora il partitioning non da nessun vantaggio in termini di prestazioni, anzi potrebbe peggiorare la situazione. Questo considerazione sembra quasi banale ma non lo è, in base alla mia esperienza in pochi ne tengono conto quanto sentono parlare per la priva volta del partitioning.

In realtà poi vi sono meccanismi complessi come le elaborazioni parallele che possono comunque beneficiare del partitioning anche in casi avversi, non mi addentro in questi casi perché è un argomento su cui sono ancora impreparato. Mi limito a ricordare che a monte di tutto occorre anche tenere in considerazione il concetto di database utilizzato per OLTP o Datawarehouse. Si tratta di due utilizzi che si pongono a due estremi contrapposti di tipologia di utilizzo di un database. Da un lato abbiamo molti utenti che fanno piccole transazioni e tante interrogazioni “puntuali”, dall’altro abbiamo pochi utenti che fanno interrogazioni massive per avere dati aggregati su cui fare analisi globali. In un OLTP non va molto bene che un singolo utente sfrutti elaborazioni parallele per prendersi tutte le risorse del sistema per soddisfare la propria richiesta, lasciando gli altri utenti ad aspettare.

Se l’applicazione è molto vicina a un OLTP e quindi le interrogazioni sono puntali è ben difficile che il partitioning possa dare grandi vantaggi in termini di prestazioni e quindi tempi di risposta dell’applicazione: le ricerche sono per lo più indicizzate quindi se le cose vengono fatte come si deve al più non abbiamo peggioramenti. Il partizionamento può essere applicato anche agli indici, essi possono essere partizionati con lo stesso criterio della tabella (LOCAL) o con un criterio diverso (GLOBAL). Oppure possono non essere partizionati per nulla.

Secondo me Thomas Kyte nel suo libro spiega e riassume molto bene le principali caratteristiche del partitioning e i casi in cui queste caratteristiche possono essere sfruttate positivamente. Non ha senso che mi metta a copiare qui quello che ha gia scritto lui. Faccio un ultimo accenno al partizionamento basato sull’hash, un caso particolare che può migliorare le prestazioni in un caso particolare, quello di numerosi inserimenti concorrenti. Facendo l’hash sulla chiave primaria e avendo partizionato la tabella in “n” partizioni, ogni inserimento consecutivo potrebbe andare su partizioni diverse, quindi segmenti diversi, questo ridurrebbe problemi di concorrenza nell’accesso alle strutture che gestisticono lo spazio del segmento stesso.

Facilitazione nella gestione

Gestire tabelle tabelle molto grandi può essere in certe condizioni piuttosto complicato. Va comunque stabilito anche cosa si intende per tabelle molto grandi. Non è un caso che il manuale Oracle dedicato al partitioning sia “Oracle database VLDB and partitioning guide“. Nell’introduzione di questo manuale si parla di database da diverse centinaia di GB o anche TB. Nella stessa introduzione poi si accenna a come in database OLTP il partitioning possa dare vantaggi in termini di gestione e “disponibilità” mentre in ambienti Datawarehouse possa dare miglioramenti in termini di prestazioni e gestione. Se abbiamo una tabella che pesa svariati GB e dobbiamo spostarla, abbiamo poco tempo a disposizione per lavorare “offline” e limitate risorse la cosa può essere problematica. Se la stessa tabella è divisa in parti più piccole possiamo lavorare sulle singole parti separatamente mantenendo le altre disponibili. Questo significa che ad esempio possiamo spostare di tablespace una singola partizione senza inibire l’accesso alla singola tabella. Se poi si usano indici partizionati localmente anche la gestione di questi sarà altrettanto più facile e comporta minor disservizio.

Migliore disponibilità

Come già scritto al capitolo “Facilitazione della Gestione” se ho necessità di spostare una tabella da una tablespace all’altra con una tabella partizionata posso spostare una partizione alla volta in modo più agevole mantenendo le altre disponibili e interrogabili senza succhiare tutte le risorse della macchina. si parla di indipendenza fra le partizioni. Per citare poi un esempio di Kyte anche il rebuild di una tabella per sistemare un po’ di “migrated rows” può essere più agevole e richiede meno spazio libero disponibile.

In questo ambito, sconfinando in quello che per me è il fantamondo dei database si può anche pensare al “restore & recovery” di singole partizioni da un backup. Operazione senz’altro relativamente facile e fattibile se le partizioni stanno su tablespace separate.

Vale la pena di ricordare che partizioni diverse di una stessa tabella possono avere strategie di compressione diverse. Anche qui aggiungo una piccola nota legata al mio piccolo mondo e quindi alla mia personale e ridotta esperienza. Nei libri e nella documentazione si spiega come si può risparmiare sullo storage dedicando disco più lenti e meno costosi a partizioni con dati più vecchi e meno usati. Si può poi usare strategie di compressione migliori riducendo ancora l’uso di risorsa spazio disco. Ora, io lavoro in un ambito relativamente piccolo ma abbiamo anche un cliente molto grande. La parte su cui lavoriamo noi è molto piccola ma già li vedo che nonostante non sembrano esserci problemi di licenze (cosa non secondaria) non ci pone neppure il problema sull’uso di storage il che mi fa da pensare abbia un costo che non preoccupa… o forse banalmente non è stata fatta una analisi dedicata.

Oracle Partitioning – Introduzione

La funzionalità Oracle Partitioning esiste da molto tempo ma fino ad oggi non ho mai avuto occasione di usarla e lavorarci e quindi di studiarne in modo approfondito i dettagli. Avendo avuto modo recentemente di fare dei test per valutarne l’utilizzo ho deciso di raccogliere un po’ di appunti e organizzarli pubblicandoli qui. L’argomento è molto più complesso e ampio di quanto si possa immaginare ad una prima occhiata alla documentazione introduttiva per cui spero e confido di riuscire a riassumere ed organizzare l’argomento in una breve serie di post di qui questo rappresenta l’introduzione.

Le fonti di studio che ho utilizzato sono primariamente i manuali Oracle (docs.oracle.com) poi vi sono libri interessanti, primo fra tutti “Expert Oracle Database Architecture” di Thomas Kyte.

La funzionalità “Partitioning” è stata introdotta da Oracle per la prima volta con la versione 8.0 del suo RDBMS. Il principio alla base di questa funzionalità è molto semplice: dividere oggetti (tabelle e indici) molto grossi in oggetti più piccoli e facilmente gestibili. L’obiettivo è da un lato semplificare la gestione dall’altro migliorare le prestazioni delle operazioni su tabelle e/o indici che possono essere molto grandi. Tutto questo viene fatto in modo trasparente per l’utente e/o l’applicazione, nel senso che ci accede ai dati non deve fare assolutamente nulla in conseguenza dell’uso di questa caratteristica perché la tabella e gli indici interessati vengono interrogati nello stesso identico modo nel caso siano partizionati o no.

Il “partitioning” (che possiamo benissimo tradurre con partizionamento) consiste nella suddivisione di una tabella ed eventualmente dei suoi indici in “pezzi” (partizioni) più piccole che possono essere gestite in modo separate perché corrispondo a oggetti fisici diversi.

In un database Oracle a una tabella di tipo heap standard corrisponde un oggetto “fisico” chiamato segmento che rappresenta la struttura di memoria in cui vengono salvati i dati della tabella. Quando una tabella viene “partizionata” essa viene logicamente suddivisa in più parti chiamate partizioni, a questo punto non c’è più un segmento associato alla tabella ma un segmento per ogni partizione.

I vantaggi comunemente attribuiti all’uso del partitioning sono solitamente:

  • Migliori prestazioni, sia delle query che delle operazioni “DML”

  • Facilitazione della gestione

  • Migliore “disponibilità”

Cercherò di analizzare nel dettaglio ciascuno di questi vantaggi spiegandone l’origine, i limiti e il valore. Siccome però ciascuno di questi punti richiede una discussione piuttosto lunga vi dedicherò ciascuno un post separato.

Appunti su Oracle Spatial: introduzione

Da qualche tempo sto cercando di studiare e approfondire una componente del database Oracle per me nuova: Oracle Spatial.

In realtà Oracle Spatial è una opzione del database che comprende un insieme abbastanza grande di funzionalità,  questo insieme estende una serie di funzionalità più ristrette che sono però incluse in una componente denominata Oracle Locator e parte della Standard Edition del database, quindi senza costi extra sulla licenza del database, a differenza di Spatial. Per quanto mi riguarda, a parte qualche isolata funzione, sono ancora allo studio delle funzionalità offerte gia con Oracle Locator e per assimilare meglio i concetti proverò a scrivere una serie di post in cui riporterò quello che ho appresso.

Oracle Locator comprende innanzi tutto la definizione di un tipo dato, chiamato SDO_GEOMETRY che serve per immagazzinare sul database, su normali tabelle HEAP, assieme ad altri dati di tipo semplice (numeri, date e stringhe) delle forme geometriche, partendo da cose semplici come punti, passando per linee spezzate (line strings) e arrivando a poligoni e collezioni di poligoni. Le tipologie di oggetti geometrici modellabili all’interno del tipo dato SDO_GEOMETRY pur non essendo tutte quelle possibili a questo mondo sono secondo me molte.

SDO_GEOMETRY è definito sul database come un tipo oggetto, qui c’è il riferimento alla documentazione ufficiale, come si può vedere SDO_GEOMETRY è un record composto a sua volta da numeri e altri oggetti che non sono altro che array ordinati di numeri (varray) in cui l’ordine segue delle regole ben precise. Riporto dal manuale la definizione di SDO_GEOMETRY:

CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);

CREATE TYPE sdo_point_type AS OBJECT (
   X NUMBER,
   Y NUMBER,
   Z NUMBER);
CREATE TYPE sdo_elem_info_array AS VARRAY (1048576) of NUMBER;
CREATE TYPE sdo_ordinate_array AS VARRAY (1048576) of NUMBER;

Come ricordato sul manuale c’è un limite subito da notare dato dalla dimensione massima degli array che si riflette sul numero massime di coordinate per un oggetto memorizzabili. Tale limite sarà più chiaro dopo aver visto cosa devono contenere tali array.

Prima di passare alla descrizione dettagliata dell’oggetto SDO_GEOMETRY darò qualche altra indicazione. Innanzitutto, la definizione dell’oggetto SDO_GEOMETRY, assieme ad alcuni package contenenti programmi di utilità per la gestione di geometrie si trovano tutti su uno schema dedicato chiamato MDSYS. Oracle Locator viene installata con la componente denominata in ORacle 11gR2 “Multimedia”, in precedenza chiamata “InterMedia”, quindi installando la componente standard Multimedia viene installato anche locator, quindi viene creato lo schema MDSYS con la definizione di SDO_GEOMETRY e altri  package PL/SQL.  Lo schema MDSYS si chiama così perché alla sua introduzione, in Oracle 7.2, questa componente di chiamava Oracle MultiDimension, a sua volta il prefisso SDO usato per il nome dell’oggetto SDO_GEOMETRY deriva da nome dato alla componente: Oracle Spatial Data Option.

Vediamo ora nel dettaglio la definizione di SDO_GEOMETRY:

SDO_GTYPE

il primo elemento è un numero, con un struttura precisa che definisce il tipo  di oggetto geometrico, SDO_GTYPE ha la forma dltt dove:

  • d è un numero da 1 e 4 e indica quante dimensioni ha l’oggetto
  • l indica la dimensione che indica la misura in un sistema di riferimento lineare (LRS), vale quindi quando  è 3 o 4, spero che la mia traduzione sia corretta perché in realtà non ho ancora affrontato l’argomento LRS e quindi fin’ora ho analizzato solo geometrie con l=0
  • tt indica il tipo di geometria, sono stati definiti valori da 00 a 07, tutti gli altri sono riservati per usi futuri.

Quindi riassumento la tabellina riportata sul manuale e facendo l’ipotesi di lavorare con sole due dimensioni questi sono i possibili valori di SDO_GTYPE con il loro significato:

  1. 2000: UNKNOWN_GEOMETRY, geometria non considerata da Oracle Spatial
  2. 2001: POINT, punto semplice, quindi composto normalmente da due coordinate x,y
  3. 2002: LINE or CURVE, un segmento di retta o un arco di cerchio
  4. 2003: POLYGON, un poligono senza “buchi”, può essere un poligono come un quadrato, un pentagono o un poligono irregolare
  5. 2004:  COLLECTION,  un insiemete eterogeneo di oggetti gemetrici
  6. 2005: MULTIPOINT,  un insieme di punti
  7. 2006: MULTILINE or MULTICURVE, un insieme di segmenti di retta o di archi
  8. 2007: MULTIPOLYGON, insieme di poligoni disgiunti

Essendo io ancora agli inizi e avendo avuto qualche esempio pratico escludo mentalmente le tipologie oggetto dalla 4 in su, considerando quindi solo punti, linee e poligoni.

SDO_SRID

Questo numero indica il sistema spaziale di riferimento, l’argomento non è banale e non me la sento di parlarne qui, in sostanza però serve  a indicare il significato che hanno le coordinate (la misura di un angolo, com’è per le classiche coordinate geografiche, piuttosto che la distanza da un punto di riferimento)

SDO_POINT

è una struttura semplice, un record di 4 elementi numerici, usato nel caso la geometria da memorizzare  sia un punto, in questo caso è consigliabile usarlo, altrimenti viene lasciato a null

SDO_ELEMENT_INFO

Si tratta di un record di numeri che hanno il compito di indicare come interpretare le coordinate che vengono memorizzate nell’elemento successivo che sarà SDO_COORDINATES. SDO_ELEMENT_INFO contiene delle triplette (quindi minimo tre numeri) in cui ciascun numero ha il seguente significato:

  1. SDO_STARTING_OFFSET, indice del primo elemento dell’array delle coordinate (l’elemento SDO_COORDINATES) cui si riferisce
  2. SDO_ETYPE, indica il tipo dell’elemento, quindi fornisce informazioni di dettaglio che si aggiungono a quelle che gia abbiamo dall’elemento SDO_GTYPE; SDO_ETYPE assume valori 1,2,1003 o 2003 per elementi semplici, altri valori per elementi più complessi
  3. SDO_INTERPRETATION, in coppia con SDO_ETYPE contribuisce a descrivere l’elemento geometrico

Spiegare bene il significato di SDO_ETYPE e SDO_INTERPRETATION è complicato, conviene fare alcuni esempi di triplette, considero ora per semplicita SDO_STARTING_OFFSET sempre 1

  • (1,0,n): con n qualunque è un tipo non supportato da Oracle Spatial
  • (1,1,1) punto
  • (1,1,n) con n>1 cluster di punti
  • (1,2,1) line string connessa da segmenti di retta
  • (1,2,2) line string connessa da archi di cerchio
  • (1,1003,1)   poligono semplice i cui vertici sono connessi da segmenti di retta
  • (1,1003,2)  poligono i cui vertici sono connessi da archi di cerchio
  • (1,1003,3) rettangolo
  • (1,1003,4) cerchio
  • (1,4,n) n>1 “compound line string”, quindi direi una linea spezzata composta da un misto di segmenti di retta e archi di cerchio)

Vi sono anche altri casi, ma prima di approfondire conviene continuare e fare qualche esempio pratico.

SDO_COORDINATES

un semplice array di numero che contiene una sequenza di coordinate, da interpretare in base al contenuto di SDO_ELEM_INFO. Nel caso di due dimensioni le coordinate sono sempre coppie x,y, quindi nel caso di un segmento di retta potrebbe essere SDO_COORDINATES=(1,1,1,2) oppure SDO_COORDINATES=12.9805953, 46.2552841, 12.980599, 46.2553036) in questo caso ho usato coordinate geografiche con longitudine e latitudine.

Le prove che ho fatto io fin’ora per farmi un po’ un’idea sono in ambito geografico, quindi con oggetti geometrici che possono rappresentare limiti amministrativi di una regione, un comune o strade o fiumi e così vai, quindi ciò che ho visto sono prevalentemente punti, poligoni (pochi) ma soprattutto linee spezzate (chiamate line strings, non so se la mia traduzione in linee spezzate sia corretta). L’uso di archi di cerchio è complesso e nella maggior parte dei casi superfluo perché approssimabile bene con segmenti di retta.

Vediamo se con il prossimo post riesco a preparare qualche esempio pratico.

External Tables, un’introduzione

Le External Tables sono una caratteristica introdotta per la prima volta con la versione 9i del database Oracle (quindi se non erro con la versione 9.0.1 ). Si tratta secondo me di uno strumento molto potente, fessibile e utile; esse sono molto simili a normali tabelle definite sul database, accessibili solo il modalità lettura (read-only) che  una volta create permettono di accedere a dati presenti su file esterni al database in modo trasparente. Fino alla versione 9i i file esterni potevano solo essere file di testo, poi con la versione 10g è stato aggiunto un nuovo access driver (chiamato ORACLE_DATAPUMP) che permette di accedere, sia in lettura che scrittura a file (con delle restrizioni) binari. Non ho mai usato questa soluzione, quindi non ne so molto. Il nome del driver mi aveva fatto pensare che il formato dei file binari fosse lo stesso dei dump creati con l’utility Oracle DataPump export, ma nella documentazione non ne parlano il che mi fa pensare che le due tipologie di file non siano interscambiabili.

Con la versione 11.2 è stata introdotta un’ulteriore funzionalità, il preprocessamento dei file esterni cioè la possibilità di aggiungere alla definizione della tabella esterna un clausola in cui si indica un programma esterno per preprocessare I file da leggere, l’esempio tipico è quello della decompressione di un file di testo compresso.

Le tabelle esterne forniscono funzionalità complementari (e in parte sovrapponibili)  a quelle fornite da SQL*Loader. Esse permettono di accedere a dati esterni come se fossero in tabelle del database con la limitazione che i dati sono accessibili in sola lettura. Il numero di righe che possono essere ritornate da una query dipende da che colonne vengono richieste perché le colonne di alcuni record possono dare errori di conversione e quindi causare il rigetto del record da parte del driver di accesso ai dati.

Il driver di accesso ai dati viene eseguito all’interno del database server a differenza di quanto accade per SQL*Loader che è un programma client a se stante. Questo ha  due implicazioni:

  1. il server deve avere accesso ai file da cui caricare i dati
  2. il server deve creare e scrivere il file creati dal file di accesso ai dati, ad esempio il file di log , il bad file e il discard file.

Non è possibile quindi indicare al server nomi di file qualunque, è necessario invece definire oggetti directory come locazioni da cui leggere e scrivere file. Ad esempio:

create directory load_src as ‘/usr/apps/datafiles’;

crea l’oggetto direcotory load_src.

Per creare un oggetto directory bisogna avere il ruolo di DBA oppure avere il privilegio “CREATE ANY DIRECTORY”. A questo punto chi crea l’oggetto directory deve anche concedere i privilegi READ e/o WRITE sulla directory ad eventuali altri utenti. Ad esempio:

grant read on directory load_src to scott;

L’utente SYS è l’unico utente che può possedere oggetti directory, ma l’utente SYS può concedere ad altri utenti il privilegio di creare oggetti directory.

Una volta definito un oggetto directory è possibile creare una tabella esterna ad esempio:

CREATE TABLE nome_tabella ( …)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_src
ACCESS PARAMETERS (
RECORD DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
(firs_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4))
)
LOCATION (‘file.dat’));

La parola chiave TYPE serve a indicare l’access driver per la tabella esterna. L’access driver è il modulo che si occupa per il database di interpretare i dati esterni, se viene omessa la specifica TYPE viene considerato come default il driver ORACLE_LOADER (quindi quello di accesso ai file di testo derivato direttamente da SQL*Loader).

Vengono poi speficiati gli ACCESS PARAMETERS che come indicato sulla documentazione sono opachi al database perché servono al driver. Quindi, mentre la prima lista di colonne definisce come viene vista lato database la tabella esterna (a tutti gli effetti analoga a una classica tabella HEAP), mentre la lista campi definita all’interno della sezione ACCESS PARAMETERS serve a indicare al driver come interpretare i dati sul file esterno e come mapparli sulla tabella oracle.

Per capire bene come definire una tabella esterna occorre quindi approfondire la definizione degli ACCESS PARAMETERS, qui mi occupo del driver ORACLE_LOADER, quindi la documentazione pertinente è questa : http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#g1031955

ACCESS_PARAMETERS per ORACLE_LOADER

commenti

Come prima parte e solo all’inizio è possibile inserire dei commenti, preceduti dal doppio trattino, poi seguono le altre parti.

record_format_info

Questa parte serve a indicare il formato dei record sul file di testo, per quanto mi riguarda il formato che uso sempre è quello delimitato da « accapo » quindi la clausola è :

RECORDS DELIMITED BY NEWLINE

come per sqlloader sono possibili anche altri formati di cui non parlo qui

poi sono definibili altri parametri come :

  • CHARACTERSET per indicare il charset del file
  • LANGUAGE/TERRITORY, qui la documentazione non è chiara e non ho ancora fatto delle prove, parrebero mutualmente esclusivi, comunque mi sembra possa tornare utile soprauttuo la TERRITORY per un caso neanche tanto particolare, quello dei numeri, infatti come indicato in seguito sulla documenazione per il parametro NLS_NUMERIC_CHARACTERS viene considerato il valore settato sul database server e non quello definito a livello di sessione.
  • BADFILE, file su cui vengono scritti record rigettati per errori
  • DISCARFILE file su cui vengo scritti record rigettati in base alla condizione speficata con la clausola LOAD WHEN
  • LOGFILE file di log
  • SKIP indica il numero di record da saltare dall’inizio del file. Su questa clausola occorre fare attenzione, perché a me in particolari casi non ha funzionato

field_definitions

Questa parte credo sia la più complessa e delicata. Se viene omessa vengono fatte le seguenti assunzioni da Oracle :

  • i campi sono delimitati dalla virgola « , »
  • i campi sono di tipo CHAR
  • la lunghezza massima del campo è 255
  • l’ordine dei campi nel file è nello stesso ordine in cui sono definiti i campi sulla tabella
  • gli spazi vuoti alla fine non vengono rimossi (no trim)

Prima della definizione dei singoli campi è possibile inserire alcune clausole :

  • delim_spec per questa riporto direttamente il grafo dalla documentazione :
  • trim_spec, può essere LRTRIM, NOTRIM, LTRIM, RTRIM, LDRTRIM
  • MISSING FIELD VALUES ARE NULL

Dopo le clausole precedenti si può mettere l’elenco dei campi, altrimenti valgono le seguenti regole :

  • se non viene specificato il tipo viene assounto CHAR(255) per campi delimitatati e CHAR(1) per campi non delimitati
  • se non vine specificata una lista campi allora oracle assume che i campi siano nello stesso ordine in cui sono definiti sulla tabella. I tipo dato viene assunto CHAR(255) se sulla tabella non è di tipo CHAR o VARCHAR2, altrimenti per la lunghezza viene preso il massimo fra 255 e la lunghezza definita per il campo sulla tabella.
  • Se non viene speficata ne’ la lista dei campi ne’ la clausola di specifica sulla delimitazione dei campi allora vale come sopra ma con tipo dato CHAR(255) per tutti e delimitatati da virgola.

Se invece viene specificato l’elenco dei campi il formato è sinteticamente (come riportato dalla documentazione ufficiale :

(field_name [pos_spec] [datatype_spec] [init_spec]) [,  (field_name [pos_spec] [datatype_spec] [init_spec])]…

Il « field_name » viene maiuscolizzato e confrontato  con in nomi dei campi nella tabella, se non ci sono corrispondenze il campo non viene caricato ma può essere usato in altre clausole di valutazione (ad esempio WHEN o NULLIF)

Per quanto riguarda il tipo, personalmente uso praticamente sempre il tipo CHAR, gli altri sono tipi dato in formato interno Oracle, quindi poco usati da me.

Nel caso di date è possibile usare la specifica « date_format », associata al tipo dato CHAR, ad esempio, sempre dal manuale :

employee_hire_date CHAR(10) date_format DATE mask « mm/dd/yyyy »

Qui è possibile vedere il grafo della specifica del date_format

Infine è possibile specificare quella che sul manuale chiama « init_spec Clause » che può essere :

  • DEFAULTIF condition_spec
  • NULLIF condition_spec

ad esempio : NULLIF campox=BLANKS per ulteriori dettagli  rimando sempre al manuale.

Dopo la definizione dei campi (field_definitions) è possibile aggiungere una ulteriore parte opzionale definta « column_trasforms » (http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#i1012621)

che permette di specificare come caricare colonne sulla tabella esterna che non mappano direttamente sui campi del file esterno ad esempio :

COLUMN TRANSFORMS (
campoy FROM CONSTANT 'NOT DEF',
campoz FROM NULL,
campog FROM CONCAT(campoa,CONSTANT 'stringa')
)

Un esempio :

drop table exttable1;
create table exttable1
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   BADFILE 'exttable1.bad'
   LOGFILE 'exttable1.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable1.csv'
   )
);
select * from exttable1;

il contenuto di exttable1.csv è :

82;’25/12/2009′;’15:25:31′;’Udine’;13,230234;46,068812;’99’

80;’23/12/2009′;’15:19:19′;’Udine’;13,230134;46,119412;’99’

81;’24/12/2009′;’15:20:16′;’Udine’;13,240134;46,068812;’99’

82;’25/12/2009′;’15:21:12′;’Udine’;13,230234;46,068812;’99’

83;’26/12/2009′;’15:22:52′;’Udine’;13,231134;46,059412;’99’

84;’27/12/2009′;’15:24:22′;’Udine’;13,222134;46,068412;’99’

85;’28/12/2009′;’15:25:31′;’Udine’;13,211134;46,079412;’99’

L’output è :


PK_ID LOCALITA XCOORD YCOORD DATA_FIELD TIME_FIELD TEST_NUMBER DUMMY_FIELD
---------- -------------------- ---------- ---------- ------------------- ------------------- ----------- ------------
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF
 NOT DEF
 80 Udine 46,119412 13,230134 23-12-2009 00:00:00 01-08-2012 15:19:19 99 NOT DEF
 81 Udine 46,068812 13,240134 24-12-2009 00:00:00 01-08-2012 15:20:16 99 NOT DEF
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:21:12 99 NOT DEF
 83 Udine 46,059412 13,231134 26-12-2009 00:00:00 01-08-2012 15:22:52 99 NOT DEF
 84 Udine 46,068412 13,222134 27-12-2009 00:00:00 01-08-2012 15:24:22 99 NOT DEF
 85 Udine 46,079412 13,211134 28-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF

Da notare che nel file i numeri hanno come separatore decimale, in questo caso il mio database è configurato in italiano, territorio italiano, se però nel file ho come separatore decimale il punto l’unica soluzione è aggiungere la specifica TERRITORY :

DROP TABLE EXTTABLE2;
create table exttable2
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   TERRITORY AMERICA
   BADFILE 'exttable2.bad'
   LOGFILE 'exttable2.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable2.csv'
   )
);
select * from exttable2;

Perché anche settando a livello di session il parametro NLS_TERRITORY non funziona.

Riferimenti:

Oracle 9i Utilities, Cap. 11;

Oracle 11gR2 Administrator’s Guide (http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#i1007424 );

Oracle 11gR2 Utilities (http://docs.oracle.com/cd/E11882_01/server.112/e22490/part_et.htm#i436567);