Caricamento veloce dati da file

una volta all’anno, o forse anche meno, mi capita di dover importare in velocità dei dati da file di testo (tipicamente csv o simili) ed ogni volta sono impreparato e in difficoltà. Il mio metodo preferito è l’uso di external tables ma questa volta dovevo importare un file con circa 150mila righe su un database cui non avevo accesso alla macchina per depositare il file. Siccome sto maturando una insana passione per Python ho pensato bene di fare al volo un programmino per fare questo lavoro. Effettivamente non ho speso più di mezz’ora per fare il programmino e caricare i dati. Poi non so perché ho fatto una piccola ricerca e mi sono reso conto se sono un po’ sciocco e che ho già a disposizione degli strumenti rapidi ed efficaci. Mi sono trovato su questa discussione su Stackoverflow dove Jeff Smith da degli spunti interessanti. Ne ha poi scritto in un post sul suo blog, ma c’è anche un post che riporta alcuni aggiornamenti della versione 20.2 che è quella che sto usando attualmente. A dirla tutta poi anche SQL Developer offre la stessa funzionalità in maniera guidata a prova di scimmia, senza dover impazzire con sql loader che risulta utile e necessario per ben altri volumi

Oracle SQLcl

Anche se la mia diffidenza mi spinge ad essere ancora prudente e a tenermi pronto per un piano di ritorno, sto passando da SQL*Plus a SQLcl. Il client a linea di comando alternativo a SQL*Plus facente parte di una suite che comprende tra gli altri il client grafico SQLDeveloper e SQL Data Modeler, sembra adesso abbastanza maturo ed compatibile quasi al 100% con SQL*Plus, del quale però estende le funzionalità e l’usabilità. Fra l’altro il pacchetto è anche più leggero. Rimane qualche minuscolo dettaglio che mi crea qualche turbamento ma direi che è ora di fare questo salto. Una delle cose che mi ha turbato è stata il problema del set autotrace di cui ho parlato nel post precedente, problema però che è comune a SQL*Plus, quindi è un falso problema. Trovo poi comodo la modalità di output “ansiconsole”, attivabile con il comando “set sqlformat ansiconsole”. Questa modalità ha il difetto che non fa funzionare le direttive “compute” o “break on”. Volevo aggirare il problema aggiungendo negli script il comando di salvataggio delle impostazioni su file (con il comando “store”), il cambiamento di impostazione per sqlformat e infine il caricamento delle impostazioni salvate. Purtroppo però con il comando STORE non mi viene salvata l’impostazione di sqlformat, quindi il trucco non funziona.

Per il resto il prodotto sviluppato da Jeff Smith e dai suoi collaboratori sembra proprio valido

Riferimenti:

SET AUTOTRACE

Si vede che sono un po’ fuori allenamento e dimentico le cose, quelle che imparai anni fa con tanto impegno e che registrai su questo blog proprio per evitare che ciò accadesse. E’ il caso di quanto scrissi in questo post,  l’unica scusante è che si tratta di un post di 12 anni fa; non posso ammetterla come scusante ma non posso neppure accettare di dimenticare cose che mi servono ancora nel mio lavoro.

Nei giorni scorsi stavo analizzando una query sottopostami da un collega, per mia sventura ho pensato di analizzare il piano di esecuzione usando l’opzione “set autotrace” di sqlplus, per fare più velocemente. Poi in realtà ho provato un passaggio a SQLcl che sembra un prodotto decisamente maturo e in grado di superare molti limiti di SQL*Plus anche se ho trovato qualche problemino con alcuni vecchi script. Premetto che il db su cui stavo facendo i test è un Oracle 12.1. A un certo punto noto una anomalia, il piano di esecuzione rimaneva lo stesso ma le statistiche, in particolare i “consistent gets” diminuivano molto dopo la prima esecuzione. A quel punto sono tornato sui miei passi ed ho utilizzato esplicitamente la cara vecchia “dbms_xplan.display_cursor” e mi sono accorto che in effetti la query aveva la particolarità di ottenere, grazie alle funzionalità di “adaptive query optimization”, un piano diverso alla seconda esecuzione.  Chiaramente il fatto di utilizzare un “explain plan” per calcolare il piano di esecuzione tagliava fuori questa casistica confondendomi. Per la verità quando mi sono accorto dell’inghippo stavo utilizzando SQLcl e quindi ho fatto una breve ricerca, imbattendomi in questo “twitt“; a questo punto ho fatto un test, anche se su un database su cui non c’erano gli stessi dati e la particolarità del cambio dati non si ripresenta. Ho abilitato il trace della sessione, abilitato il set autotrace, ho eseguito la query, fermato il trace e analizzato il trace. Dentro il trace ci ho trovato la solita “explain plan for” per cui ho chiesto informazioni commentando il twitt. Anche con l’ultima versione, 19.2 di SQLcl io registro lo stesso comportamento, quindi continuerò ad utilizzare la cara vecchia “dbms_xplan.display_cursor”

JSON in Oracle: Introduzione

JSON è la sigla per JavaScript Object Notation. Si tratta di un formato per lo scambio dati molto semplice, basato su un sottoinsieme del linguaggio di programmazione JavaScript. E’ un formato facile da leggere e capire per gli umani e per le macchine. Oserei dire che in questo aspetto è un ottimo concorrente di XML. Qui viene riportato un confronto tra le due rappresentazioni: https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-data.html#GUID-C347AC02-31E4-49CE-9F74-C7C0F339D68E.
L’idea che al momento mi sono fatto è che diventa un’ottima alternativa a XML in caso di dati con struttura molto semplice. La definizione del formato JSON è tanto semplice da stare in una paginetta abbastanza corta: https://www.json.org/
Negli anni JSON pare essere diventanto sempre più popolare e diffusamente usato, assieme ai cosiddetti database NoSQL. Questo formato è talmente diffuso che anche Oracle ne ha gradualmente introdotta la gestione sul suo database relazionale. L’introduzione è avvenuta con la versione 12cR1, penso come estensione delle funzionalità XML DB, o perlomeno ne ha aggiunto la documentazione al relativo manuale. (https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246). Un’ottima sintesi del manuale Oracle viene fornita da Tim Hall: https://oracle-base.com/articles/12c/json-support-in-oracle-database-12cr1

Apro una piccola parentesi con le impressioni e l’idea che mi sono fatto al momento, da novizio di JSON. Presumo che JSON si sia diffuso molto in contesti particolari, dove c’era l’esigenza di gestire dati con strutture molto semplici, assieme a questo formato mi sembra si sia diffuso l’uso di database NoSQL che hanno la capacità di gestire in modo più efficente questi dati e che in alcuni casi al costo di non soddisfare i requisiti di un database relazionali (ad esempio transazioni ACID) riescono ad avere migliori prestazioni e migliore “scalabilità”. Si tratta quindi di sistemi che sono in grado di gestire un carico crescente di lavoro senza andare in sofferenza. Ora, quello che si fa con JSON si può benissimo fare con un database relazionale, anzi, si tratta di implementare schemi molto semplici, per fare interrogazioni analoghe a quelle tipiche che si fanno su dati in formato JSON, su una analoga struttura relazionale bastano query SQL molto banali, non parliamo di join, raggruppamenti ecc. Il fatto di essere “schemaless” è un falso vantaggio o problema, anche in un database relazionale posso aggiungere a piacere colonne al volo a una tabella, senza problemi; è tutto nel come si scrive le applicazioni, se la mia applicazione è scritta come una volta si raccomandava, il fatto che in una tabella da un momento all’altro ci sia un campo in più non fa nessuna differenza. Ciò che un database come Oracle forse non può fare bene è gestire questi dati con prestazioni elavate, perché Oracle è appunto un database relazionale vero, ne rispetta tutte le regole, la visione dei dati è sempre consistente e in più Oracle ha una infrastruttura di monitoraggio e manutenzione. Per andare incontro al mercato e all’esigenza di interfacciarsi con il mondo anche Oracle si è adeguata; certo, trovo buffo dover fare su un database relazionale manovre per estrarre e manipolare dei dati quanto si possono usare dei semplici comandi sql, alla portata di qualunque programmatore degno di questo nome. E’ normale che Oracle abbia aggiunto il supporto a questo formato, per facilitare lo sviluppo di funzionalità di scambio dati con sistemi esterni senza dover forzare continue trasformazioni da un modello JSON a un modello relazionale e viceversa. Chiusa per ora la parentesi.

Alla base del modello dati JSON ci sono insiemi di coppie “chiave”-“valore”, dove la chiave è un’etichetta, il nome di una variabile, un identificativo a cui è associato appunto un valore. Il valore può essere un tipo base e in JSON sono previsti solo numeri, stringhe, valori booleani (true o false) o null. Altrimenti il valore può essere ricorsivamente un altro insieme di coppie chiave-valore oppure un array che si identifica usando come delimitatori dei suoi elementi le parentesi quadre. Un oggetto base, invece è delimitato dalle parentesi graffe. Quindi ecco un primo esempio di oggetto JSON:

{“nome” : “cristian”}

Se ci sono più coppie vengono separate da virgole:

{“nome” : “cristian”, “matricola” : 1234}

L’etichetta, come tutte le stringhe, anche quando sono valori, vanno racchiuse tra apici doppi. Ampliando e applicando ricorsivamente la struttura possiamo arrivare a un esempio più complesso (tratto dal manuale Oracle):

{“PONumber” : 1600,
“Reference” : “ABULL-20140421”,
“Requestor” : “Alexis Bull”,
“User” : “ABULL”,
“CostCenter” : “A50”,
“ShippingInstructions” : {…},
“Special Instructions” : null,
“AllowPartialShipment” : true,
“LineItems” : […]}

Per gestire il tipo dato “JSON” in Oracle non hanno introdotto un nuovo tipo dato, gli oggetti JSON possono essere messi in campi con tipo dato VARCHAR2, CLOB o BLOB. E’ stato introdotto un vincolo “IS JSON” applicabile alla colonna destinata a contenere i documenti JSON (https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/json-in-oracle-database.html#GUID-F6282E67-CBDF-442E-946F-5F781BC14F33). Il vincolo fa si che oracle verifichi che all’interno della colonna venga inserito un documento di tipo JSON valido. Il vincolo può essere rafforzato con la specifica “STRICT” che fa rende il vincolo più rigoroso (https://docs.oracle.com/database/121/ADXDB/json.htm#GUID-951A61D5-EDC2-4E30-A20C-AE2AE7605C77)

Con questo concludo questo post introduttivo, ne seguiranno altri con esempi e approfondimenti.

Oracle Instant Client e SQL*Loader

Sarò breve, volevo solo condividere la soddisfazione che ho provato nel vedere che finalmente all'”instant client“, il client leggero e portatibile per database Oracle è stato aggiunto il pacchetto che include gli eseguibili per import export, vecchi e datapump ma soprattutto, quello che trovo più utile, è stato aggiunto l’eseguibile di SQL Loader, il programma per caricare file di testo su database. In passato confesso di aver fatto qualche tentativo di utilizzarlo senza dover installare il client completo, però non ci ero mai riuscito. Mentre trovo poco utile la disponibilità degli eseguibili exp,imp, expdp,impdp, ritengo che il caricamento di file CSV o simili dal proprio pc sia un’operazione che capita abbastanza di frequente. Viceversa devo ancora capire l’utilità degli eseguibili di export e import data pump che richiedono sempre che il file di dump si trovi sul database server su cui deve essere creato anche un oggetto “directory”.

Oracle Data Redaction

Uno degli argomenti che ho studiato per conseguire la certificazione a Oracle 12 è stato questo modulo che va precisato che, a parte per le soluzioni cloud di Oracle, richiede la licenza per l’opzione “Oracle Advanced Security” che include un sacco di funzionalità legate alla gestione della “sicurezza”.

Al solito, quanto scrivo è tratto dalla documentazione ufficiale di Oracle e da miei test.

Oracle Data Redaction è un modulo che permette di mascherare (redact) dati restituiti dalle query lanciate dalle applicazioni. Il mascheramente avviene al momento dell’esecuzione delle query (runtime) senza influire sui vincoli di integrità o sulla modalità di memorizzazione dei dati, quindi è un sistema che può essere applicato a sistemi già in produzione. Per le sue caratteristiche è una componente complementare ad altri soluzioni facendi parte del pacchetto “Oracle Database Solutions”.

Oracle Data Redaction non fa cose poi tanto complesse, maschera i dati al volo sulla base di “policy” che stabiliscono cosa mascherare, come e quando. Il cosa tipicamente è la colonna di una tabella o vista. Il come ricade nelle sei seguenti possibilità:

  1. Full redaction (default, DBMS_REDACT.FULL)
  2. Partial redaction (DBMS_REDACT.PARTIAL)
  3. Regular Expressions (DBMS_REDACT.REGEXP)
  4. Random (DBMS_REDACT.RANDOM)
  5. No redaction (DBMS_REDACT.NONE)
  6. Nullify (DBMS_REDACT.NULLIFY) (da 12.2)

La creazione, la configurazione e la gestione delle policy avviene tramite il pacchetto PL/SQL DBMS_REDACT. Il pacchetto contiene varie costanti e le procedure per creare, modificare, abilitare, disabilitare o rimuore le policy.

Le policy mascherano i dati quanto una espressione fornita risulta vera. L’espressione può essere un banale “1=1” che indica sempre vero oppure qualcosa che si basa su varibili di contesto (context).

Non entro nel dettaglio mi limito a riportare un esempio di utilizzo con espressioni basate su “application context”).

L’esempio si basa su questo schema: c’è un utente “amministratore” che gestisce gli “application context” e le policy di data redaction, lo chiamo sysadmin_ctx come sul manuale; c’è un utente/schema che possiede una tabella con dati “sensibili”, lo chiamo “cristian” e infine c’è un utente che deve poter accedere ai dati della tabella con l’esclusione della colonna con la parte di dato “sensibile”, questo utente si chiama “lettore”. L’utente cristian ha una tabella che ho chiamato test1 con questo tracciato:

Name Type
----------- -------------------
ID NUMBER(10)
NOME VARCHAR2(40 CHAR)
COGNOME VARCHAR2(40 CHAR)
USERNAME VARCHAR2(40 CHAR)
PASSWORD VARCHAR2(171 CHAR)
INIZIO DATE
FINE DATE

 

Un primo esempio banale di policy è basato sul “ROLE” assegnato all’utente:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.FULL,
function_parameters => NULL,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'sys_context(''SYS_SESSION_ROLES'',''LETTORE_ROLE'')=''TRUE''');
END;
/

Il parametro “espression” specifica l’espressione che stabilisce quando la policy deve essere applicata e i dati mascherati. Il parametro function_type=> DBMS_REDACT.FULL specifica che il mascheramento deve essere completo, in questo caso per default le stringhe vengono sostituite da uno spazio vuoto.

LETTORE@svil183p1 > select sys_context('SYS_SESSION_ROLES','LETTORE_ROLE') FROM DUAL;

SYS_CONTEXT('SYS_SESSION_ROLES','LETTORE_ROLE')
------------------------------------------------------------------------------------------------------------------------
TRUE

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44
1
102
21
61
81
82
101
103
100
141
142
143
320
361
242
380
480
500
520
540
640
641
120
163
451
200
561
580
600
140
280
220
319
340
400
560
620

38 rows selected.

Se si vuole applicare modalità più sofisticate si possono definire appositi “context”:

CREATE OR REPLACE PACKAGE set_lettore_ctx_pkg IS 
PROCEDURE set_lettore_id; 
END; 
/
CREATE OR REPLACE PACKAGE BODY set_lettore_ctx_pkg IS
PROCEDURE set_lettore_id 
IS 
BEGIN 
DBMS_SESSION.SET_CONTEXT('cri_ctx_test2', 'lettore_id', 1); 
END;
END;
/
CREATE CONTEXT cri_ctx_test2 USING set_lettore_ctx_pkg;

CREATE TRIGGER set_lettore_ctx_trig AFTER LOGON ON DATABASE
BEGIN
if USER='LETTORE' THEN 
sysadmin_ctx.set_lettore_ctx_pkg.set_lettore_id;
end if;
END;
/

E quindi ecco un altro esempio di policy:

EXEC DBMS_REDACT.DROP_POLICY(OBJECT_SCHEMA=>'CRISTIAN',OBJECT_NAME=>'TEST1', policy_name => 'cristian_pol_3');
BEGIN
DBMS_REDACT.ADD_POLICY(
object_schema => 'CRISTIAN',
object_name => 'TEST1',
column_name => 'PASSWORD',
policy_name => 'cristian_pol_3',
function_type => DBMS_REDACT.REGEXP,
regexp_pattern => '.',
regexp_replace_string =>DBMS_REDACT.RE_REDACT_WITH_SINGLE_X,
regexp_position => DBMS_REDACT.RE_BEGINNING,
regexp_occurrence => DBMS_REDACT.RE_ALL,
regexp_match_parameter => DBMS_REDACT.RE_CASE_INSENSITIVE,
policy_description => 'nasconde password',
column_description => 'users passwords',
expression => 'SYS_CONTEXT(''cri_ctx_test2'',''lettore_id'') = 1');
END;
/

 

In questo caso oltre all’espressione ho modificato la modalità di mascheramento usando un caso banale di espressione regolare, qui chi ha dimestichezza con le espressioni regolari si può sbizzarrire, questo per mascherare pezzi di numeri di carte di credito, indirizzi email ecc.

LETTORE@svil183p1 > select id,password from cristian.test1;

ID PASSWORD
---------- -------------------------
44 XXXXXXX
1 XXXXXXX
102 XXXXXXX
21
61 XXXXXXX
81 XXXXXXX
82 XXXXXXX
101 XXXXXXX
103
100 XXXXXXX
141 XXXXXXX
142 XXXXXXX
143 XXXXXXX
320 XXXXXXX
361 XXXXXXX
242
380
480 XXXXXXX
500 XXXXXXX
520 XXXXXX
540 X
640 XXXXXXX
641 XXXXXXX
120
163 XXXX
451 XXXXXXX
200 XXXXXXX
561 XXXXXXX
580 XXXXXXX
600
140 XXXXXXX
280 XXXXXXX
220 XXXXXXX
319 XXXXXXX
340
400
560 XXXXXXX
620 XXX

 

 

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 – 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.

LOB e temporary tablespace in Oracle

I LOB (Large OBject) sono un tipo dato introdotto nei database relazionali per estenderne l’utilizzo per gestire tipi dato non strutturato quali grandi file binari e grandi file di testo. La caratteristica principale del tipo dato LOB, come indica il nome, è quella di poter immagazzinare oggetti molto grossi, quindi si può trattare di file pdf, fotografie digitali, file eseguibili o qualunque altro tipo di file binario di piccole o grosse dimensioni; possono essere anche file di testo come file html e xml. I file binari e i file di testo vengono gestiti in modo leggermente differente, per cui ci sono due tipi di LOB: BLOB (Binary LOB) per i dati binari e CLOB (Character LOB) per i dati formato testo. Non posso e non voglio fare qui una descrizione dettagliata dei LOB nei database relazionali, dico solo che sono una estensione che permette di consolidare la gestione anche di file all’interno di un database relazionale.

In Oracle il tipo dato LOB ha un limite sulla dimensione massima piuttosto alto, come spiegato qui il limite è i (2^32-1)*<CHUNK SIZE> byte e in condizioni di default la chunk size è pari alla dimensione del blocco. Con la classica dimensione del blocco da 8 KB fa un limite di quasi 32 TB.

I LOB, vista la possibilità che siano di grosse dimensioni, devono essere gestiti attraverso delle funzioni specifiche. Esiste una interfaccia PL/SQL ed essendo un tipo dato “standard” esiste una implementazione delle funzioni di gestione anche nei driver per Java JDBC. Siccome dove lavoro si usa quest’ultima interfaccia su questa mi sono dedicato un po’ e la uso per descrivere l’argomento oggetto di questo post.

Tutto è nato dal fatto che su una installazione del cliente abbiamo verificato che le sessioni aperte sul database dall’applicazione mantengono una quota di spazio nella temporary tablespace dell’utente Oracle allocata. Dopo aver fatto un classica ricerca, prima genericamente su internet e poi sul supporto Oracle ho trovato qualche indizio ed ho cominciato a fare qualche test con lo scopo di circoscrivere le possibili cause e individuare delle soluzioni.

l riferimento per utilizzare LOB su un database Oracle in programmi java è il manuale “Oracle JDBC Developer’s Guide. Ho cercato di leggerlo ma piuttosto frettolosamente quindi non ho ancora ben chiari tutti i meccanismi. La parte che mi interessa sembra essere quella del paragrafo 14.5 dove si parla di “Temporary LOB” i quali sono fatti per dati di passaggio (transient data). Quando in java si utilizza un metodo che implementa l’interfaccia java.sql.Connection.createBlob viene creato un temporary lob. Il temporary blob è di fatto un variabile di tipo LOB che utilizza spazio allocato sulla temporary tablespace configurata per l’utente. Ho verificato che se da un programma java “prelevo” un campo blob con questo tipo di codice:

Blob blob_ris;
rs.next();
blob_ris = rs.getBlob(“contenuto”);

non viene creato un temporary lob, in questo caso si ottiene un puntatore direttamente al LOB interessato. Non viene allocato spazio sulla TEMP (interrogando la v$tempseg_usage non esce nulla). Utilizzando la chiamata ((BLOB)blob_aris).isTemporary() se ne ha la conferma. Viceversa, se si deve creare un dato di tipo LOB da zero da un programma Java è possibile utilizzare la chiamata del codice simile a questo:

Blob myblob =conn.createBlob();

Come scritto sopra si tratta di una chiamata di un metodo che implementa l’interfaccia java.sql.Connection.createBlob, che crea un temporary lob che utilizza spazio allocato sulla temporary tablespace.

Uno dei problemi che può capitare con i temporary lob è che lo spazio allocato nella temp non venga rilasciato. La nota del supporto Oracle 802897.1 spiega come in effetti se all’interno della sessione si effettua la chiamata al metodo “free” (o freeTemporary) lo spazio viene rilasciato all’interno della sessione e può essere riutilizzato all’interno della stessa sessione ma non è disponibile per le altre sessioni. Per liberarlo definitivamente occorre chiudere la sessione. La stessa nota dice che dalla versione 10.2.0.4 è stato introdotto l’evento 60025 che permette di liberare completamente lo spazio senza dover chiudere la sessione. Si deve quindi eseguire il comando:

alter session set events ‘60025 trace name context forever’;

L’evento si dovrebbe poter attivare a livello di sistema, però questo non l’ho provato.

La nota 1384829.1 riporta un programmino java che dimostra come funziona l’evento 60025. La premessa nella nota è che il test è stato fatto con driver jdbc 11.2.0.3 su datatabase 11.2.0.3. Io ho cominciato a giocare con il programma e fare delle prove con il driver che utilizziamo al momento maggiormente, versione 12.1 con una patch. Con questo driver il programma non funziona come descritto, cioè lo spazio nella temporary tablespace non viene liberato se non con la chiusura della connessione. Ho provato con varie versioni di driver 12.1, con java 6 e java 7 ma il risultato è sempre lo stesso, lo spazio non viene rilasciato. Poi mi sono deciso a provare con il driver 18.3; dalla 12.2 in poi il driver jdbc viene fornito solo per jdk 8, se si vuole usare versioni jdk precedenti Oracle suggerisce di usare driver precedenti. Con la versione 18.3 l’evento 60025 torna a funzionare, ovvero la chiamata lob.free() libera lo spazio definitivamente sulla temporary tablespace. Presumo quindi che ci sia qualche problema con i driver 12.1 e l’evento 60025; anche con un driver 11.2.0.3 il programma di test funziona. Ho provato a fare una ricerca più mirata sul supporto Oracle e la cosa più pertinente che ho trovato è stata la nota 2297060.1 che fa riferimento a questo problema e indica fra le versioni interessate dall 9.2 alla 12.2. Quello che viene scritto nelle conclusioni e nella sezione “solution” non mi è affatto chiara. Ho provato allora a fare delle varianti del programmino java per capirne di più. Il risultato è che a un certo punto non capivo proprio nulla perché sembrava che anche con i driver 12.1 funzionasse la liberazione dello spazio TEMP.  La verità è che effettivamente con l’evento 60025 attivo la chiamata del metodo lob.free() libera definitivamente lo spazio occupato dal temporary LOB. Il motivo per cui sembra che l’esempio non funzioni con i driver 12.1 è la riga di codice:

System.out.println(rs.getString(1));

che si trova prima dell’instanziazione della variabile/oggetto lob. Commentando via quella riga di codice l’esempio funziona anche con i driver 12.1.

Riassumento quello che ho capito. Se eseguo una query come questa:

select to_clob(‘bla’) from dual; 

con il codice:

ResultSet rs = stmt.executeQuery(SQL1_syntax);

viene gia creato un temporary lob, viene allocato dello spazio nella temporary tablespace. Anche chiudendo il ResultSet lo spazio non viene liberato; l’unico modo per liberarlo è instanziare l’oggetto, ad esempio con una semplice chiamata come questa:

my_clob = rs.getClob(1);

e poi chiamare il metodo free dell’oggetto:

my_clob.free();

Se l’evento 60025 è stato attivato lo spazio nella temporary tablespace viene liberato.

La chiamata rs.getString(1) probabilmente crea un riferimento che impedisce per motivi che ignoro che lo spazio nella temp venga liberato.

Riferimenti:

  1. How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (Doc ID 802897.1)
  2. How to Release Temporary LOB Segments without Closing the JDBC Connection (Doc ID 1384829.1)