MAX/MIN con KEEP (DENSE_RANK FIRST/LAST ORDER BY …)

Devo dire che mi mancava questo aspetto del mio lavoro, trovare soluzioni a problemi, scoprendo ed approfondendo nuove cose. Partendo dalle fonti che ho trovato per il mio post precedente (al riguardo ho aggiunto al “blogroll” l’interessante blog di Stewart Ashton, dal quale sono partito oggi) ho avuto modo di leggere a approndire un post che ha cinque anni, cinque anni in cui non ricordo di aver mai visto l’operatore di cui parla. La cosa effettivamente molto bizzarra è che la sezione KEEP… oggetto di quel post non viene menzionata nella documentazione in corrispondenza delle funzioni MIN e MAX, neanche nella sezione dedicata alle funzioni analitiche (anche perché non sembra rientrare in quella categoria) ma come indicato da Rob Van Vijk nel post viene descritta (sommariamente direi) alle funzioni FIRST e LAST  che sono in realtà solo una parte della clausola KEEP. I link alla documentazione Oracle riportati nel post del 2012 nel frattempo sono stati invalidati da un cambiamento fatto da Oracle, c’è sicuramente lo stesso problema anche in molti miei vecchi post.

Ho poco da aggiungere all’ottimo post di Rob Van Vijk che riporta un semplice caso di test e le sue prove. Io direi che l’utilizzo delle funzioni MAX/MIN(..) KEEP (DENSE_RANK FIRST/LAST ORDER BY ..) mi pare possa snellire la query nelle casistiche interessate rispetto all’uso della funzione analitica. Ovviamente non prendo neanche in considerazione l’approccio con il NOT EXIST che aveva senso il secolo scorso prima che fossero introdotte le funzioni analitiche

Annunci

CAST e MULTISET

Oggi partendo da una procedura commissionatami  che doveva fare un aggiornamento dei dati sono arrivato ad approfondire un operatore che forse ho visto di sfuggita in passato ma certamente non ho usato ne’ capito, ragion per cui oggi, avendo un attimo di tempo ho deciso di dedicarmici.

Il punto di partenza è una organizzazione dei dati di cui ignoro le motivazioni alla base ma che mi sembra, in un database relazionale, poco corretta; si tratta infatti di un campo di tipo VARCHAR2 che contiene degli id numerici che fanno riferimento ad un campo di un’altra tabella separati da una virgola. Concedo il beneficio del dubbio ma non ho capito perché non si è usato una tabella di relazione come in un database relazionale si dovrebbe fare. Il fatto è che a livello di SQL fare anche una banale join per estrapolare i dati diventa una cosa assai complicata.  Per cercare di spiegarmi cerco di riportare un caso di test:

create table T1 (
 t1_id number,
 t1_desc varchar2(30)
);
create table T2 (
 t2_id number,
 t2_t1ids varchar2(90),
 t2_desc varchar2(30)
);

insert into T1 values (1,'a');
insert into T1 values (2,'b');
insert into T1 values (3,'c');
insert into T1 values (4,'d');
insert into T2 values (1,'1,2','uno');
insert into T2 values (2,'2,3,4','due');
insert into T2 values (3,'1,4','tre');
insert into T2 values (4,'4','quattro');

Ora sul campo T2.T2_T1IDS ho degli id separati da virgola in un campo di tipo stringa che dovrebbero fare riferimento al campo T1.T1_ID. In un database relazionale ci sarebbe una tabella T3 che come struttura avrebbe due campi, uno che punta a T1.T1_ID e uno che punta a T2.T2_ID, se poi voglio estrarre i dati li ricostruisco con una join sulle tre tabelle; qui non è così facile, magari a me sfugge qualcosa ma di sicuro se scrivo questo non va bene:

SVILUPPO@svil112 > select * from t2 join t1 on (t1_id in t2_t1ids);
select * from t2 join t1 on (t1_id in t2_t1ids)
 *
ERROR at line 1:
ORA-01722: invalid number

Io sono giunto alla conclusione che bisogna in qualche modo fare un parsing del campo T2.T2_T1IDS. In passato ho fatto delle procedurine per fare una cosa simile ma siccome non mi piacevano molto e mi sembravano più complesse del necessario ho pensato di fare una nuova ricerca su internet, in pratica ho cercato una funzione inversa rispetto a LISTAGG e sono arrivato a questa discussione su ORAFAQ. Qui ho trovato due interessanti soluzioni che ho deciso di approfondire e capire e questo è il motivo di questo post. La prima, applicata al mio caso di test dovrebbe essere più o meno così:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

 T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d

Da quello che ho capito io la funzione regexp_substr(t2_t1ids, ‘[^,]+’, 1, level) prende una occorrenza di tutto ciò che non contiene al suo interno una virgola, “level” indica quale occorrenza, quindi la sottoquery restituisce un record per ogni id nel campo t2_t1ids.  Nella discussione viene fatto un intervento che spiega come quella prima soluzione non funziona nel caso di più record estratti dalla query nella sezione “WITH”, infatti:

SVILUPPO@svil112 > with test as
 2 (select * from t2 where t2_desc='due' or t2_desc='tre' )
 3 select * from
 4 (select t2_id,regexp_substr(t2_t1ids, '[^,]+', 1, level) t1id
 5 from test
 6 connect by level <= length(regexp_replace(t2_t1ids, '[^,]+')) + 1)
 7 join t1 on t1_id=t1id;

  T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 3 1 1 a
 2 2 2 b
 2 3 3 c
 2 3 3 c
 2 4 4 d
 3 4 4 d
 2 4 4 d
 2 4 4 d
 3 4 4 d
 2 4 4 d

La “connect by level<….” provoca una moltiplicazione dei record nel risultato che non va bene. Anche qui avrei da approfondire perché confesso che non ho chiarissimi alcuni dettagli del funzionamento delle query gerarchiche. Se non ho capito male al primo livello ci sono tutti i valori della tabella diciamo n, al secondo livello per ogni valore ci sono  n figli e così via; qui la cosa si complica e richiederà altri approfondimenti.

La variante suggerita per risolvere il problema nel caso più generico è un po’ più complessa ed applicata al mio caso di test dovrebbe essere così:

SVILUPPO@svil112 > WITH test AS
 2 (SELECT * FROM t2 WHERE t2_desc='due' or t2_desc='tre' )
 3 SELECT * FROM
 4 (SELECT t2_id,REGEXP_SUBSTR(t2_t1ids, '[^,]+', 1, b.column_value) t1id
 5 FROM test a CROSS JOIN
 6 TABLE
 7 (
 8 CAST
 9 (
 10 MULTISET
 11 (
 12 SELECT LEVEL FROM DUAL
 13 CONNECT BY LEVEL <= REGEXP_COUNT(a.t2_t1ids, '[^,]+')
 14 )
 15 AS SYS.odciNumberList
 16 )
 17 ) b
 18 )
 19 join t1 on t1_id=t1id;

T2_ID T1ID T1_ID T1_DESC
---------- ---- ---------- ------------------------------
 2 2 2 b
 2 3 3 c
 2 4 4 d
 3 1 1 a
 3 4 4 d

Così mi pare corretta, seppur un po’ lunga.

Nell’ultima soluzione proposta ci sono alcune cose che io non conoscevo molto bene, la prima è l’utilizzo della sintassi CROSS JOIN che serve a generare un prodotto cartesiano con la sintassi ANSI della JOIN. La seconda cosa è quello che sembra un operatore MULTISET, in realtà è parte dell’operatore CAST. Come dice la documentazione MULTISET informa Oracle di prendere il risultato della sottoquery e restituire un valore “collection”. Questa “collection” viene convertita nel tipo predefinito SYS.odciNumberList che è documentato qui. A suo volta questo tipo può essere dato in pasto all’operatore TABLE e così di fatto la lista di valori è trasformata al volo in una tabella.

Non credo e spero di dover ritornare su situazioni analoghe a questa, in ogni caso quando capiterà avrò pronto qui qualche appunto che mi aiuterà.

 

Riferimenti:

Tipi predefiniti (Database Data Cartridge Developer’s Guide 11.2) , per 12.1 qui.

OraFaq

 

P.S.

2017/05/04: credo valga la pena di riportare qui un altro post interessante che ho trovato oggi: https://stewashton.wordpress.com/2016/06/22/new-improved-in-lists/

SQL*Plus 12.2

Oggi ho provato a installare la versione 12.2 del nuovo instant client Oracle versione 12.2 sul mio pc aziendale con sistema operativo Win7. Non ci sono motivi particolari per cui ci sia la necessità di passare ad usare questa versione se non al puro scopo di testarla e tal proposito mi sono ricordato di un post di Mike Dietrich (anche perché l’ho letto ieri 🙂 ) dove si parla che una fra le novità di questa versione di SQL*Plus è la diversità di comportamento nel caricamento del file “login.sql”. Io normalmente uso sul mio pc una configurazione per cui lancio uno script .cmd che a sua volta chiama un’altro script che setta le opportune variabili d’ambiente, fra cui SQLPATH affinché punti a una directory dove trovo una serie di script di utilità (per molti dei quali vanno i ringraziamenti a Tanel Poder)  e l’utilissimo login.sql che imposta l’ambiente iniziale per SQL*Plus come garba a me. Con la nuova versione di SQL*Plus effettivamente l’esecuzione automatica di login.sql che si trova sotto il percorso puntato dalla variabile d’ambiente SQLPATH non funziona. Infatti il post di Franck Pachot cui fa riferimento Dietrich fa tutti i test dettagliati su ambiente Linux, peccato però che come scrive Dietrich alla fine del suo post:

On Windows please be aware of (credits to Tim Hall):

  • Bug 25804573SQL PLUS 12.2 NOT OBSERVING SQLPATH IN REGISTRY OR ENV VARIABLE FOR LOGIN.SQL

 

Ti pareva se su Winzoz non c’era qualche fastidio…

Sinceramente non mi è chiaro come aggirare il problema, la soluzione che ho adottato io sembra bruttina ma è l’unica che ho trovato in modo veloce: ho rinominato sqlplus.exe in sqlplus_orig.exe, ho creato un file sqlplus.cmd il cui contenuto è:

sqlplus_orig.exe %1 @login

Ora, prima ho provato a non rinominare il file .exe confidando che la logica di ricerca degli eseguibili mettesse prima i .cmd e dopo i .exe ma non sembra esssere così. La cosa che invece mi perplede abbastanza è il fatto che se metto @login.sql non funziona, @login si. Anche se invoco successivamente @login.sql non funziona, funziona se lo invoco con il percorso completo

SYSTEM@svil121 > @login.sql
SYSTEM@svil121 > @c:\oracle\conf\login.sql

PL/SQL procedure successfully completed.


Session altered.

Questa particolarità vale solo per il file login.sql, gli altri script funzionano sia che metta l’estensione  e sia che non la metta

 

 

Campi varchar2 con stringhe vuote

Post molto sintetico:


set serveroutput on size unlimited

declare
a varchar2(10);
begin
a := '';
if (a is null) then dbms_output.put_line('empty = null');
else dbms_output.put_line('empty is not null');
end if;
if (a = '') then dbms_output.put_line('empty = empty');
else dbms_output.put_line('empty <> empty');
end if;
a := null;
if (a is null) then dbms_output.put_line('empty = null');
else dbms_output.put_line('empty is not null');
end if;
if (a = '') then dbms_output.put_line('empty = empty');
else dbms_output.put_line('empty <> empty');
end if;
end;

TEST@test112 > /
empty = null
empty <> empty
empty = null
empty <> empty

Oracle 12c: top n query e default

Questo post nasce da un lungo giro, come mi è capitato più volte in passato; ieri approfittando di un momento di calma ho dato una occhiata ai miei “feed”, strumento che ormai pare obsoleto e rimpiazzato per i più (ma non da me) da twitter che però non ritengo possa rimpiazzare come funzionalità l’accopiata blog+feed, direi anzi che twitter va verso la direzione di creare confusione e far perdere informazioni preziose… ma sto divagando. Ieri quindi ho dato una letta a qualche articolo sui blog che seguo ormai da anni e che parlano di database Oracle, in particolare leggevo gli ultimi post di Jonathan Lewis, che rappresenta ancora una preziosissima risorsa. Finito di leggere un post l’occhio mi è caduto sul blogroll che in testa riporta questo interessante link; si tratta di una pagina in cui a sua volta sono raggruppati un sacco di link a fonti di informazione sul nuovo Oracle 12c. Scorrendo i link mi sono soffermato su questo, non tanto per l’argomento quanto per l’autore che mi era un nome noto. Arrivato al post di Connor Mcdonald l’argomento mi ha incuriosito e sono andato alla sua fonte, un articolo di Tom Kyte. Come si vede un giro abbastanza lungo (almeno per il livello medio di molti utilizzatori di internet); comunque sull’articolo di Tom Kyte ho deciso di approfondire. Ho così rispolverato la macchina virtuale con Oracle 12c, mi ci è voluto un attimo per riorientarmi e trovare il container, creare un utente (e prima una tablespace dedicata), poi sono riuscito a ripetere gli esempi mostrati, prima sui Default sui valori delle colonne nelle tabelle, dove Oracle ha sicuramente colmato una lacuna, permettendo di definire come valore di default per una colonna il numero generato da una sequence, creata esplicitamente e separatamente o implicitamente e legata alla tabella con la specifica “generated as [default] identity“. Un po’ più bizzarra la funzionalità “default on NULL” che però evidentemente per molti può risultare utile. Non ho testato la miglioria sull’aggiunta di colonne con valori di default, ma sembra anche essa una grande cosa (ho passato brutti momenti in passato con l’aggiunta di colonne con valore di default a tabelle molto grandi).

Infine l’argomento “top n query“: anche questo mi è  parso interessante, finché non ho notato una differenza fra le mie prove e quanto mostrato da Tom. Ho ripetuto le prove anche su una precedente installazione di Oracle 12c in cui ho installato Enterprise Edition (mentre in ambiente virtuale ho installato una standard edition per fare prove sulla “Multitenant Architecture“) e il risultato è diverso da quello mostrato di Tom, a livello di piano di esecuzione, ecco i miei risultati:

CRISTIAN@10.110.3.52/salespdb > create table t2 as select * from all_objects;

Table created.

CRISTIAN@10.110.3.52/salespdb > create index idx_t2 on t2(owner,object_name);

Index created.

CRISTIAN@10.110.3.52/salespdb > select owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 3975347511

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76285 | 21M| | 1101 (1)| 00:00:01 |
|* 1 | VIEW | | 76285 | 21M| | 1101 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 76285 | 2756K| 3600K| 1101 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T2 | 76285 | 2756K| | 362 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)


Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 1300 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 1 sorts (memory)
 0 sorts (disk)
 5 rows processed

La cosa che mi “perprime” è che con la classica tecnica del ROWNUM ho questo:


CRISTIAN@10.110.3.52/salespdb > select * from ( select owner,object_name,object_id from t2
 2 order by owner,object_name) where rownum<=5;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2015243804

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 725 | 8 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 5 | 725 | 8 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 76285 | 2756K| 8 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | IDX_T2 | 5 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=5)


Statistics
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 9 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 5 rows processed

Nel mio caso non usa l’indice, mi è venuto lo scrupolo di provare a vedere le statistiche e il costo del piano usando un hint per forzare l’uso dell’indice:


CRISTIAN@10.110.3.52/salespdb > select /*+ INDEX(T2 IDX_T2) */owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2588503356

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 76285 | 21M| 62581 (1)| 00:00:03 |
|* 1 | VIEW | | 76285 | 21M| 62581 (1)| 00:00:03 |
|* 2 | WINDOW NOSORT STOPKEY | | 76285 | 2756K| 62581 (1)| 00:00:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 76285 | 2756K| 62581 (1)| 00:00:03 |
| 4 | INDEX FULL SCAN | IDX_T2 | 76285 | | 454 (1)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)


Statistics
----------------------------------------------------------
 1 recursive calls
 0 db block gets
 9 consistent gets
 0 physical reads
 0 redo size
 614 bytes sent via SQL*Net to client
 475 bytes received via SQL*Net from client
 2 SQL*Net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 5 rows processed

Le statistiche sono migliorate, ma il costo indicato per il  piano di esecuzione è elevato. Come spiega bene Christian Antognini sul suo libro “Troubleshooting Oracle Performance” (e come la mia esperienza conferma) piani di esecuzione non ottimali molto spesso sono generati da previsioni sbagliate, infatti (ma si intravedeva gia prima):

</pre>
CRISTIAN@10.110.3.52/salespdb > select /*+ gather_plan_statistics */ owner,object_name,object_id from t2
 2 order by owner,object_name fetch first 5 rows only;

OWNER OBJECT_NAME OBJECT_ID
----------------------------- ------------------------------ ----------
APEX_040200 APEX 87057
APEX_040200 APEXWS 86726
APEX_040200 APEX_ADMIN 88996
APEX_040200 APEX_APPLICATIONS 87697
APEX_040200 APEX_APPLICATION_ALL_AUTH 87765

Elapsed: 00:00:00.07
CRISTIAN@10.110.3.52/salespdb > select plan_table_output from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID g467gxjwprt0g, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner,object_name,object_id from
t2 order by owner,object_name fetch first 5 rows only

Plan hash value: 3975347511

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.04 | 1300 | | | |
|* 1 | VIEW | | 1 | 76285 | 5 |00:00:00.04 | 1300 | | | |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 76285 | 5 |00:00:00.04 | 1300 | 4096 | 4096 | 4096 (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 76285 | 76285 |00:00:00.13 | 1300 | | | |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
 2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)
22 rows selected.

Elapsed: 00:00:00.06
<pre>

Cioè nel mio caso oracle non si aspetta 5 righe ma tutte le 76285 presenti nella tabella, nonostante il fatto che sia scritto nella query che il risultato conterrà 5 righe. Perché a me non funziona come dovrebbe quindi non è ancora chiaro, ho provato a ricalcolare statistiche a cambiare client (oltre che server) ma non ho visto miglioramenti, un tema da indagare.

P.S.

Attenzione che sulla funzionalità FETCH..OFFSET è gia stato rilevato un baco, se ne parla qui, io ho effettuato lo stesso test riportato da Connor Mcdonald nel primo commento con lo stesso risultato.

 

 

 

Inghippi con il Character Set – Conclusione

Un titolo decisamente ottimista per un post su questo argomento, la mia speranza è comunque di chiudere l’argomento ripreso non molto tempo fa con il post “Inghippi con il Charcter SET“.  L’ispirazione mi è venuta leggendo la nota del supporto Oracle

NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

che a sua volta mi ha rimandato alla nota:

The correct NLS_LANG in a Microsoft Windows Environment (Doc ID 179133.1)

Entrambe le note precisano che per gestire in modo corretto il character set è meglio utilizzare SQL Developer, però danno anche altri spunti interessanti. Ad esempio viene specificato:

The “Unicode” “chcp 65001” and “chcp 65000” values are NOT supported with sqlplus.exe.

Poi usano un character set che forse non avevo mai notato: WE8PC850 che pare andare molto d’accordo con il CP 850 settato di default sulle macchine Windows.

Sempre adottando i corretti settaggi ho verificato che si può aggirare i limiti di sqlplus adottando comandi registrati su file e poi invocandoli da sqlplus tramite il comando START, bisogna però fare attenzione alla codifica del file su cui si salvano i comandi, ecco  un esempio:

C:\tmp>chcp 65001
Tabella codici attiva: 65001

C:\tmp>set NLS_LANG=.AL32UTF8

C:\tmp>more insert_test_charset.sql
Memoria insufficiente.

C:\tmp>SQLPLUs sviluppo50/sviluppo50@svil112_methone

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 1 13:17:23 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
PL/SQL procedure successfully completed.
Session altered.

SVILUPPO50@svil112_methone > delete from test_charset;

8 rows deleted.

SVILUPPO50@svil112_methone > @insert_test_charset.sql

1 row created.
1 row created.

SVILUPPO50@svil112_methone > SELECT * FROM TEST_CHARSET;

A
—————————————————————————————————-
KKKKà
NNNè

SVILUPPO50@svil112_methone >

Il file è formato utf-8 (senza il famigerato BOM) e si noti come il comando more fallisca con il messaggio “memoria insufficente”

Il contenuto del file insert_test_charset.sql è:

INSERT INTO TEST_CHARSET VALUES (‘KKKKà’);
insert into test_charset values (‘NNNè’);

In realtà poi, lavorando con caratteri compresi nella codifica West-Europe, usare UTF-8 lato client nel mio caso è superfluo, credo che d’ora in poi userò WE8PC850

Il comando MERGE

La prima volta che ho visto il comando SQL MERGE con un esempio di utilizzo è stato quando ho letto il libro “Expert Oracle Database Architecture. 9i and 10g Programming Techniques and Solutions”; si tratta di  qualche anno fa, non ricordo esattamente quando, perché sul libro, contrariamente a ciò che a volte faccio non ho segnato le date in cui ho iniziato a leggere i vari capitoli. Quello che però ricordo è che mi colpì e mi segnai questo comando come promemoria. Poi credo di averlo studiato un po’, credo anche che all’epoca il database prevalentemente in uso da me fosse il 9i (quindi è decisamente qualche anno fa) perché una delle cose per cui non l0 avevo trovato utile per me era il fatto che sezione “WHEN NOT MATCHED” era obbligatoria. Siccome però ultimamente ho riscoperto l’utilità di questo comando ho deciso di dedicarci  un bel post per decriverlo.

Il comando MERGE, come fa un po’ intuire il nome, serve a unire il risultato di una query su una tabella o una vista in un’altra tabella, ad esempio Tom Kyte fa vedere, a pagina 118 del libro che sopra ho citato, come utilizza il comando per mantere aggiornata una tabellina con delle statistiche. Quindi la situazione tipica di utilizzo di MERGE è quando si hanno due tabelle collegate fra loro da una chiave e si vuole aggiornare un attributo su una prendendo il dato sull’altra tabella. Riporto un caso di test banale:


create table test_a (aid number, atext varchar2(50));
create table test_b (bid number, btext varchar2(50));

insert into test_a values (1,'testoa1');
insert into test_a values (2,'testoa2');

insert into test_b values (1,'testb1');
insert into test_b values (3,'testb3');

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > select * from test_b;

BID BTEXT
---------- --------------------------------
 1 testb1
 3 testb3

Per dare un’idea, la tabella TEST_A potrebbe essere una tabella di anagrafiche di articoli di produzione, la tabella TEST_B una tabella con aggiornamenti che possono essere nuovi articoli o articoli modificati. Ecco un esempio di MERGE sulle due tabelle:


SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 when not matched then insert (aid,atext) values (tb.bid,tb.btext)
 6 ;

2 di righe unite.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2 testoa2
 3 testb3

Ora, dalla versione 10 (non ho verificato sulla 10.1 ma dalla documentazione parrebbe gia uguale alla 10.2), la sezione “WHEN NOT MATCHED” non è più obbligatoria come lo era sulla versione 9.2, quindi è possibile fare questa cosa:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 ;

Unita 1 riga.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- -------------------------------------------
 1 testb1
 2 testoa2

Io in alternativa al MERGE per questi casi sono sempre riuscito a utilizzare un  update semplice, che però non va sempre bene, come in questo esempio:


SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > update test_a ta set atext=(select btext from test_b tb where ta.aid=tb.bid);

Aggiornate 2 righe.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2

Per evitare questo caso che raramente è quanto desiderato occorre una clausola WHERE sull’UPDATE, ad esempio:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > update test_a ta set
 2 atext=(select btext from test_b tb where ta.aid=tb.bid)
 3 where atext is null;

Aggiornate 0 righe.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

Siccome recentemente mi sono imbattuto in un caso in cui mi era comodo il merge (che come prestazioni è superiore all’UPDATE) , non avevo una condizione di WHERE applicabil all’UPDATE, ma non mi serviva la parte “WHEN NOT MATCHED” sono ricorso all’unico trucco che mi è venuto in mente, ovvero l’inserimento di record con dati fasulli e a seguire una DELETE:


SVILUPPO40@perseo10 > rollback;

Rollback completato.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testoa1
 2 testoa2

SVILUPPO40@perseo10 > merge into test_a ta using
 2 (select bid,btext from test_b) tb
 3 on (tb.bid=ta.aid)
 4 when matched then update set ta.atext=tb.btext
 5 when not matched then insert (aid,atext) values (-1,'X-X')
 6 ;

2 di righe unite.

SVILUPPO40@perseo10 > delete from test_a where aid=-1 and atext='X-X';

Eliminata 1 riga.

SVILUPPO40@perseo10 > select * from test_a;

AID ATEXT
---------- --------------------------------------------------
 1 testb1
 2 testoa2

Come ho appena accennato, un buon motivo per ricorrere a questo espediente è che comunque le prestazioni del comando MERGE sono nettamente superiori a quelle dell’UPDATE con la sotto-query.