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

venerdì 5 maggio 2017 alle 05:23 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

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

CAST e MULTISET

mercoledì 3 maggio 2017 alle 03:33 | Pubblicato su 11g, 12c, Diario, SQL | Lascia un commento

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

giovedì 20 aprile 2017 alle 20:10 | Pubblicato su 12c, SQL, Varie | 1 commento
Tag:

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

mercoledì 7 ottobre 2015 alle 07:14 | Pubblicato su PL/SQL, SQL, Varie | Lascia un commento

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

giovedì 16 gennaio 2014 alle 16:13 | Pubblicato su 12c, SQL | 4 commenti
Tag: , ,

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

giovedì 1 agosto 2013 alle 01:23 | Pubblicato su 11g, Diario, SQL, Varie | Lascia un commento
Tag:

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

lunedì 3 dicembre 2012 alle 03:36 | Pubblicato su SQL | 6 commenti
Tag: , ,

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.

Il costrutto SQL CASE

mercoledì 12 settembre 2012 alle 12:57 | Pubblicato su 11g, PL/SQL, SQL | Lascia un commento
Tag: , ,

Pur essendo il mio strumento di uso quotidiano da oltre 10 anni l’SQL è un linguaggio di cui ancora non ho piena padronanza, anche perché per le mie esigenze in realtà molti costrutti li ho usati poche volte. Un po’ alla volta però i nodi vengono al pettine e mi scontro con la necessità di approfondire l’uso di espressioni che in precedenza sono riuscito ad evitare. Fra queste ci oggi parlo delle espressioni “CASE“. Comincio con il dire, come apprendo dal libro “Oracle Database 11g SQL” di Jason Price che la CASE espression serve a implementare la classica logica IF-Then-else dentro statement SQL, è supportata da Oracle 9i in su (anche se sul sito di Tim Hall si parla di prima apparizione in Oracle 8i, evidentemente con limitazioni). Le espressioni CASE funzionano in modo simile alla funzione DECODE, ma la espressione CASE oltre ad essere più leggibile ha il vantaggio di essere conformo allo standard ANSI SQL/92, cosa che pare confermata da questa bozza di tale standard.

Per un’ottima descrizione di questo tipo di espressioni non mi posso esimere da riportare come primo riferimento il manuale ufficiale oracle: “Oracle Database SQL language reference“. E’ molto sintetico ma credo dica tutto e riporta anche due semplici esempi. La cosa che a me ha sempre confuso è il fatto che esistono due versioni di espressioni CASE:

  1. le Simple case expressions: che funzionano esattamente come la DECODE
  2. le Searched case expressions: che sono un po’ più complesse

A parte il vincolo sulla omogeneità dei tipi dato, non ci sono vincoli particolari sul tipo dato su cui possono lavorare le condizioni. In realtà noto la mancanza sul manuale a riferimenti a tipi data, però facendo un paio di prove a me pare funzioni:


SVILUPPO40@perseo10 > select case trunc(Sysdate) when to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > alter session set nls_date_format='yyyy-mm-dd';

Modificata sessione.

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then 1 else 2 end case1 from dual;

CASE1
----------
 1

SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual;
select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else 2 end case1 from dual
 *
ERRORE alla riga 1:
ORA-00932: tipi di dati incoerenti: previsto DATE, ottenuto NUMBER
SVILUPPO40@perseo10 > select case when sysdate>to_date('12-09-2012','dd-mm-yyyy') then to_date('01-02-2010','dd-mm-yyyy') else to_date('07-02-2010','d
d-mm-yyyy') end case1 from dual;

CASE1
----------
2010-02-01

L’errore che ho ricevuto nel penultimo esempio mi fa supporre che non sia una questione di conversione implicita ma una vera e propria gestione del tipo DATE.

Un bell’articoletto che spiega il CASE si può trovare sul sito databasejournal.com, anche se noto che parla solo delle “searched case expressions”. Invece Tim Hall fa degli esempi di entrambe le versioni, sia in SQL che in PL/SQL. Mi sento di dire che mentre le “simple case expressions” gestiscono gli stessi casi gestiti dalla decode, quindi solo uguaglianze su un campo, le “searched case expressions” permettono, sempre nel rispetto dell’omogeneità del tipo dato, costruzioni molto complesse usando anche operatori di comparazione quali “>” e “<“.

ORA-01461 e VARCHAR2

venerdì 28 ottobre 2011 alle 28:03 | Pubblicato su Diario, PL/SQL, SQL | 2 commenti
Tag: , , , ,

Più volte mi è stato segnalato sulla nostra applicazione l’errore ORA-01461, il cui testo è “can bind a LONG value only for insert into a LONG column“. Prima di tutto è curioso che abbia dovuto risalire fino alla documentazione della versione 9.2 del database per rintracciare la documentazione dell’errore, quando tale errore l’ho riprodotto fino alla versione 11.2.0.2.

Altra cosa bizzarra e che forse spiega la rimozione dalla documentazione ufficiale di tale errore è che nei nostri database il tipo LONG cui si riferisce il testo del messaggio di errore non è mai esistito.

Un indizio che mi aiutò all’inizio a capire da cosa potesse derivare questo errore è il fatto che l’errore abbia fatto le prime comparse dopo che siamo passati sui nostri database al character set UTF-8 (mentre prima si usava ISO-8859 o equivalente). Potrei dedicare un intero post solo a descrivere le mie peripezie a cercare di fare test con la gestione di caratteri non ASCII con il character set UTF-8 ma per ora soprassiedo.

Oltre alle difficoltà a fare dei test con l’UTF-8, per la cui gestione da windows ho ancora delle difficoltà che ho aggirato usando putty e collegandomi su macchine linux, sono stato ingannato dal comportamento di funzioni come RPAD che fa cose come questa:


SVILUPPO42@perseo10 > select length(rpad('a',4500,'b')) from dual;

LENGTH(RPAD('A',4500,'B'))
--------------------------
4000

Comportamento del tutto lecito e anche logico, ritornando tale funzione un tipo dato VARCHAR2 che ha come limite appunto 4000 (byte), ma non essendo espressamente specificato nella documentazione non davo per scontato. Poi sulle funzioni SQL in Oracle occorre sempre fare attenzione, se sono usate in SQL valgono delle regole, in PL/SQL le cose sono leggermente diverse (e un varchar2 in PL/SQL può arrivare a 32767 (byte)

Riassumendo riporto le prove che ho fatto:


SQL> create table test_char (a varchar2(1300 char));

Table created.

Primo tentativo di aggirare il limite di RPAD


SQL> insert into test_char values (rpad('a',4000,'b')||'test');
insert into test_char values (rpad('a',4000,'b')||'test')
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

Al che ho deciso di provare con bind variables ma ho incontrato un’altra anomalia:


SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 28 14:44:27 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> var v_test varchar2(4000 char);
SP2-0676: Bind variable length cannot exceed 1000 characters

Il che è dovuto, come ho scoperto in seguito, semplicemente al fatto che avevo settato (erroneamente) la variabile d’ambiente

NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Quindi ho deciso di passare a un test con PL/SQL:


SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',2000,'a');
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-12899: value too large for column "TEST"."TEST_CHAR"."A" (actual: 2000,
maximum: 1300)
ORA-06512: at line 5

Dove si ottiene l’errore che mi aspetto. In realtà questo test funziona anche con una insert diretta con RPAD direttamente via SQL, ma il caso interessante è questo:


SQL> declare
a varchar2(5000 char);
begin
a :=rpad('a',4000,'a')||'test';
insert into test_char values (a);
end;
/  2    3    4    5    6    7
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 5

Che è l’errore che cercavo. Ho cercato di analizzare il trace generato attivando l’evento 10046 ma non ci ho trovato nulla di interessante.  L’errore si verifica anche da Java con driver JDBC, parrebbe che essendo una  stringa con lunghezz superiore ai 4000 caratteri la variabile venga convertita in tipo long e da ciò deriva l’errore, è una supposizione che faccio.

La funzione TRUNC con TIMESTAMP

giovedì 23 dicembre 2010 alle 23:24 | Pubblicato su SQL | Lascia un commento
Tag: , , , ,

Qualche giorno fa mi è stato segnalato un problema sulla nostra applicazione, che però si verificava solo in ambiente di test presso il cliente, mentre da noi l’errore non c’era. L’errore era:

ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

I programmatori hanno inizialmente pensato a un problema con i driver o con java, però quando ho visto la query mi è venuto subito un forte sospetto, la query conteneca una cosa analoga a questa:


... trunc(to_timestamp('25-12-2010 17:55','dd-mm-yyyy hh24:mi'))

in realtà al posto del to_timestamp c’era una parametro e  pezzo che ho riportato compariva nella clausola WHERE di una query.  Siccome mi risultava che la funziona TRUNC avesse come parametro solo tipo DATE o NUMBER (faccio riferimento ancora alla documentazione delle versione 10gR2 perché è ancora la versione che maggiormente usiamo, in realtà in questo caso il cliente adotta ancora 9iR2 ma sulla documentazione non ho visto differenze) ho subito fatto un doppio controllo, pratico:


D:\ORACLE\instantclient_11_2>sqlplus sviluppo30/Sviluppo30@perseo92

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 23 10:12:32 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.8.0 - Production
JServer Release 9.2.0.8.0 - Production


PL/SQL procedure successfully completed.


Session altered.

SVILUPPO30@perseo92 > select trunc(to_timestamp('25-12-2010 17:55','dd-mm-yyyy hh24:mi')) from dual;


TRUNC(TO_TIMESTAMP(
-------------------
25-12-2010 00:00:00

Con un risultato che mi ha un po’ sorpreso, quindi ho fatto un controllo “teorico”, verificando la documentazione della 9iR2 che però per quanto riguarda la funzione TRUNC non dice nulla di diverso da quanto dice la documentazione della 10gR2.

Mi è venuto il dubbio che livelli di patchset diversi potessero influire, allora nell’ordine ho provato su una 9.2.0.6 (il più basso che ci è rimasto in sede) e su una 9.2.0.5 presso un’altro cliente con lo stesso identico risultato. Non convinto allora ho chiesto l’accesso al database di test del cliente interessato, scoprendo che si trattava di una 9.2.0.4:

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 23 10:18:27 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select trunc(to_timestamp('25-12-2010 17:55','dd-mm-yyyy hh24:mi')) from dual;
select trunc(to_timestamp('25-12-2010 17:55','dd-mm-yyyy hh24:mi')) from dual
 *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

Ne deduco che dalla patchset 9.2.0.5 Oracle abbia introdotto una piccola modifica alla funzione TRUNC affinché lavori anche con un parametro di tipo TIMESTAMP, cosa non documentata neppure sulla 11.2. Nel nostro caso l’errore è nato perché fino a un paio d’anni fa lavoravamo solo con tipi dato DATE, sembra si tratti di un problema incontrato da altri prima e a cui oracle ha (inspiegabilmente) posto una soluzione sua.

Da parte mia, pur ritenendo strano che si lavori ancora (fra l’altro per un progetto nuovo) con una 9.2.0.4 ho suggerito hai programmatori di correggere la query, anche per evitare possibili problemi di performance.

Pagina successiva »

Crea un sito o un blog gratuitamente presso WordPress.com.
Entries e commenti feeds.