SQL4DBA - Novita' SQL in Oracle 12c

Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script relativi alle novita' SQL nella versione Oracle 12c.

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.
Informazioni di dettaglio si possono trovare nella relativa documentazione.

Introduzione

Il dialetto SQL di un produttore ha sempre un'evoluzione particolare. L'SQL di Oracle e' stato innovativo su moltissime clausole e funzioni (eg. il PL/SQL introdotto nella versione 6.0 nel 1988) ma anche in ritardo su altre (eg. fino alla 8i non si poteva cancellare una colonna)...

Anche con la versione 12c di Oracle vengono introdotte novita' interessanti nell'SQL ma anche costrutti che su altri dialetti erano presenti da tempo!
In ogni caso l'SQL di Oracle risulta uno dei piu' completi e potenti disponibili.

Fetch

Il controllo del numero di righe restituite da una query e la paginazione dei dati e' una funzionalita' implementata su molti database (LIMIT in MySQL o SQLite) ma assente nell'SQL di Oracle. Come trucco per aggirare l'assenza della clausola di controllo con Oracle si e' sempre utilizzata la virtual column ROWNUM [NdE oppure, piu' di recente, un'analitic function], ma con alcuni problemi sulle condizioni e sull'ordinamento.

select * from scott.emp order by sal desc fetch first 5 rows only;

La gestione del numero di righe e della paginazione introdotta da Oracle con la versione 12c e' molto completa e potente, ma comunque semplice.
E' possibile indicare il numero di righe da estrarre (FIRST o NEXT) e quante saltarne (OFFSET). E' anche possibile indicare il numero di record con una percentuale (PERCENT) oppure con l'indicazione a pari merito (WITH TIES).

Chiavi automatiche

Prima della versione 12c con Oracle non era possibile creare colonne con chiavi univoche automatiche (eg. SERIAL in MySQL). La versione 12c introduce due differenti possibilita' per farlo!

CREATE SEQUENCE aziende_seq; CREATE TABLE aziende ( id NUMBER DEFAULT aziende_seq.NEXTVAL, ragione_sociale VARCHAR2(80), partita_iva VARCHAR2(11) ); CREATE TABLE aziende2 ( id NUMBER GENERATED ALWAYS AS IDENTITY, ragione_sociale VARCHAR2(80), partita_iva VARCHAR2(11) );

Il primo esempio utilizza una normale SEQUENCE Oracle cui fa riferimento come valore di default della colonna ID nella creazione della tabella. Il secondo esempio utilizza la nuova clausola IDENTITY.
In entrambe i casi una normale INSERT sulla tabella generera' un nuovo codice univoco senza necessita' di trigger o scrittura di codice.
In realta', dal punto di vista tecnico, Oracle utilizza sempre una SEQUENCE, pero' lo fa in modo piu' semplice per le applicazioni e piu' efficiente.

With

Nella versione 12c e' possibile definire funzioni in PL/SQL e valutare con esse il risultato di selezioni in modo molto efficiente:

WITH FUNCTION PIVA_OK ( PIVA varchar2 ) return varchar2 is type ck_array is varray(10) of number; PIVA_CHK ck_array := ck_array(0, 2, 4, 6, 8, 1, 3, 5, 7, 9); C integer; S integer := 0; begin if PIVA is NULL or PIVA = '00000000000' then return NULL; elsif length(PIVA) <> 11 then return NULL; end if; for I in 1 .. 11 loop C := ascii( substr(PIVA, I, 1) ); if C between 48 and 57 then C := C - 48; else return NULL; end if; if I mod 2 = 1 then S := S + C; else S := S + PIVA_CHK(C+1); end if; end loop; if S mod 10 <> 0 then return NULL; end if; return PIVA; end PIVA_OK; select ragione_sociale, PIVA_OK(partita_iva) partita_iva from aziende; /

Nell'esempio il numero di partita IVA viene visualizzato solo se risulta formalmente corretto.

Colonne invisibili

In Oracle 12c e' possibile definire colonne come invisibili. Queste non verranno richieste quando si omette l'elenco delle colonne in una INSERT e non verranno visualizzate quando si utilizza il carattere * nella clausola di SELECT [NdA costrutti entrambe da evitare ma purtroppo molto diffusi].

alter table aziende ADD (descrizione varchar2(128) invisible);

Naturalmente facendo riferimento esplicito al nome della colonna l'invisibilita' sparisce!

VARCHAR2 a 32K

Con la versione Oracle 12c e' possibile utilizzare i datatype VARCHAR2, NVARCHAR2 e RAW fino alla dimensione di 32767 byte; basta impostare il parametro MAX_STRING_SIZE = EXTENDED. Oracle utilizzera' la normale modalita' di memorizzazione fino a 4000 byte e sfruttera' la tecnica del LOB se la dimensione e' superiore.
Naturalmente se il parametro MAX_STRING_SIZE = STANDARD (che e' il default) le dimensioni massime restano quelle valide nelle precedenti release: 4000 byte per VARCHAR2 e NVARCHAR2, 2000 byte per RAW.

Facile? Sembra... ma in realta' l'impostazione del parametro richiede un'upgrade! Ecco riassunti i comandi da utilizzare:

alter system set max_string_size=EXTENDED scope=spfile; shutdown immediate startup upgrade @?/rdbms/admin/utl32k.sql startup

Per RAC, DG, PDB, ... naturalmente e' richiesto qualche passo in piu' per impostare il parametro MAX_STRING_SIZE.

Pattern matching

Con la versione 12c sono disponibili complesse funzioni di pattern matching direttamente nell'SQL. In questo modo e' possibile effettuare analisi dei dati (andamenti, fluttuazioni) con semplici query.
Esempio estratto dalla documentazione ufficiale Oracle:

SELECT * FROM Ticker MATCH_RECOGNIZE ( PARTITION BY symbol ORDER BY tstamp MEASURES STRT.tstamp AS start_tstamp, LAST(DOWN.tstamp) AS bottom_tstamp, LAST(UP.tstamp) AS end_tstamp ONE ROW PER MATCH AFTER MATCH SKIP TO LAST UP PATTERN (STRT DOWN+ UP+) DEFINE DOWN AS DOWN.price < PREV(DOWN.price), UP AS UP.price > PREV(UP.price) ) MR ORDER BY MR.symbol, MR.start_tstamp;

Ammetto di aver copiato l'esempio... appena avro' tempo cerchero' di farne uno piu' semplice, ma non sara' facile!

Tutto qui?

No! La versione 12c contiene molte altre nuove funzionalita' nell'SQL che descrivero' quando avro' tempo [NdE allora mai!]: SQL in Multitenant Option, Adaptive Sql Plan Management, indici multipli sulle stesse colonne, ...

Ulteriori informazioni

Un'introduzione ad Oracle si trova in Oracle RDBMS mentre Novita' Architettura Oracle 12c riporta gli ultimi aggiornamenti introdotti nella versione 12c. L'evoluzione delle funzionalita' dell'RDBMS Oracle nel tempo e' stata notevole... La storia delle versioni Oracle negli ultimi 20 anni viene descritta in C'era una volta... Maggiori dettagli tecnici sulle diverse versioni di Oracle e le date di rilascio di ogni versione sono riportate in questo documento.
Il sito Oracle ufficiale contiene tutta la documentazione ufficiale.

DBA SQL Scripts

Novita' SQL in Oracle 12c