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.

 

 

 

Annunci

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.

Stessa query con risultati diversi

venerdì 29 maggio 2009 alle 29:23 | Pubblicato su SQL | 4 commenti
Tag: , , ,

Alcuni giorni fa mi è stato sottoposto una query molto particolare che si comportava in modo anomalo: facendo SELECT count(*) …. dava un numero, facendo SELECT * …. dava un numero diverso di record.  Analizzando un po’ la query ho subito notato che la differenza nei due casi era il piano di esecuzione (francamente spero non vi siano altre possibilità). Ho provato un pochino ad analizzare i piani di esecuzione ed ha trasformare la query ma senza capire molto. Oggi però sono riuscito, senza capire dove sta esattamente l’inghippo, a riprodurre il problema su uno degli schemi di esempio di Oracle.

Breve parentesi, con Oracle 10g gli schemi di esempio (HR,SH, OE, ecc.)  sono inclusi nel “companion CD”, oggi l’ho scaricato ed ho provato a lanciarne l’installazione, ma pare che per installare qualche KByte di script sia necessario installare oltre 700 MB di roba sulla macchina, spazio che sulla macchina di sviluppo dove volevo installare tali schemi non ho, allora mi sono estratto manualmente dal pacchettone di installazione solo gli script che mi servivano.

Fine della parentesi.

Come dicevo sono riuscito in qualche modo a riprodurre un caso analogo a quello segnalatomi dal nostro laboratorio di sviluppo, in cui in sostanza la stessa query da risultati diversi. Ho cercato fra gli schemi di esempio uno che avesse una struttura adatta a riprodurre il mio caso, lo schema che ho individuato è OE,  la query che ho scritto, inspirandomi non ad una logica sensata ma all’obbiettivo di riprodurre con il minimo sforzo il caso è questa:


select
 count(*)
 from
 products pi , inventories inv
 where
 pi.product_id=inv.product_id(+)
 and inv.warehouse_id(+) =
 case when
 exists
 (select ct.category_id
 from  categories_tab ct,
 product_prices pp
 where ct.category_id=pp.category_id
 and ct.category_id=pi.category_id)
 then to_number(11)
 else to_number(1)
 end

Sono stato molto fortunato, perché non avevo la forza di pensare molto e nonostante ciò in poco tempo sono riuscito a riprodurre il caso che volevo. Fra l’altro la logica originale non era poi così distante dal caso che ho inventato io perché si trattava proprio di una query su articoli, listini, magazzini ecc.

Questa è la versione che credo corretta:


OE@perseo10 > select
 2     count(*)
 3    from
 4    products pi , inventories inv
 5   where
 6   pi.product_id=inv.product_id(+)
 7   and inv.warehouse_id(+) =
 8    case when
 9     exists
 10      (select ct.category_id
 11      from  categories_tab ct,
 12             product_prices pp
 13      where ct.category_id=pp.category_id
 14             and ct.category_id=pi.category_id)
 15      then to_number(11)
 16      else to_number(1)
 17    end
 18  /

 COUNT(*)
----------
 288

Piano di esecuzione
----------------------------------------------------------
Plan hash value: 2199577315

-----------------------------------------------------------------------------------------------
| Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                     |     1 |    21 |    10   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE         |                     |     1 |    21 |            |          |
|   2 |   NESTED LOOPS OUTER    |                     |   288 |  6048 |     5   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER   |                     |   288 |  4032 |     5   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | PRODUCT_INFORMATION |   288 |  2016 |     5   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN   | PRD_DESC_PK         |     1 |     7 |     0   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN     | INVENTORY_IX        |     1 |     7 |     0   (0)| 00:00:01 |
|   7 |     NESTED LOOPS        |                     |    11 |   165 |     5   (0)| 00:00:01 |
|*  8 |      INDEX UNIQUE SCAN  | SYS_C0056077        |     1 |     2 |     0   (0)| 00:00:01 |
|   9 |      VIEW               | PRODUCT_PRICES      |    11 |   143 |     5   (0)| 00:00:01 |
|  10 |       SORT GROUP BY     |                     |    11 |    33 |     5   (0)| 00:00:01 |
|* 11 |        TABLE ACCESS FULL| PRODUCT_INFORMATION |    17 |    51 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

 5 - access("D"."PRODUCT_ID"(+)="I"."PRODUCT_ID" AND
 "D"."LANGUAGE_ID"(+)=SYS_CONTEXT('USERENV','LANG'))
 6 - access("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM  (SELECT
 "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION" "PRODUCT_INFORMATION" WHERE
 "CATEGORY_ID"=:B1 GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB" "CT" WHERE
 "CT"."CATEGORY_ID"=:B2) THEN 11 ELSE 1 END  AND "I"."PRODUCT_ID"="INV"."PRODUCT_ID"(+))
 8 - access("CT"."CATEGORY_ID"=:B1)
 11 - filter("CATEGORY_ID"=:B1)

A questo punto, per forzare il cambiamento di piano di esecuzione (e di risultato) utilizzo un hint:


OE@perseo10 > select
 2    /*+ RULE */ count(*)
 3    from
 4    products pi , inventories inv
 5   where
 6   pi.product_id=inv.product_id(+)
 7   and inv.warehouse_id(+) =
 8    case when
 9     exists
 10      (select ct.category_id
 11      from  categories_tab ct,
 12             product_prices pp
 13      where ct.category_id=pp.category_id
 14             and ct.category_id=pi.category_id)
 15      then to_number(11)
 16      else to_number(1)
 17    end
 18  /

 COUNT(*)
----------
 36

Piano di esecuzione
----------------------------------------------------------
Plan hash value: 2133092042

---------------------------------------------------------
| Id  | Operation                 | Name                |
---------------------------------------------------------
|   0 | SELECT STATEMENT          |                     |
|   1 |  SORT AGGREGATE           |                     |
|*  2 |   FILTER                  |                     |
|   3 |    NESTED LOOPS OUTER     |                     |
|   4 |     NESTED LOOPS OUTER    |                     |
|   5 |      TABLE ACCESS FULL    | PRODUCT_INFORMATION |
|*  6 |      INDEX UNIQUE SCAN    | PRD_DESC_PK         |
|*  7 |     INDEX RANGE SCAN      | INVENTORY_IX        |
|   8 |      MERGE JOIN           |                     |
|*  9 |       INDEX UNIQUE SCAN   | SYS_C0056077        |
|* 10 |       FILTER              |                     |
|  11 |        VIEW               | PRODUCT_PRICES      |
|  12 |         SORT GROUP BY     |                     |
|  13 |          TABLE ACCESS FULL| PRODUCT_INFORMATION |
|  14 |    MERGE JOIN             |                     |
|* 15 |     INDEX UNIQUE SCAN     | SYS_C0056077        |
|* 16 |     FILTER                |                     |
|  17 |      VIEW                 | PRODUCT_PRICES      |
|  18 |       SORT GROUP BY       |                     |
|  19 |        TABLE ACCESS FULL  | PRODUCT_INFORMATION |
---------------------------------------------------------

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

 2 - filter("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM
 (SELECT "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION"
 "PRODUCT_INFORMATION" GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB"
 "CT" WHERE "CT"."CATEGORY_ID"=:B1 AND
 "CT"."CATEGORY_ID"="PP"."CATEGORY_ID") THEN 11 ELSE 1 END )
 6 - access("D"."PRODUCT_ID"(+)="I"."PRODUCT_ID" AND
 "D"."LANGUAGE_ID"(+)=SYS_CONTEXT('USERENV','LANG'))
 7 - access("INV"."WAREHOUSE_ID"(+)=CASE  WHEN  EXISTS (SELECT 0 FROM
 (SELECT "CATEGORY_ID" "CATEGORY_ID" FROM OE."PRODUCT_INFORMATION"
 "PRODUCT_INFORMATION" GROUP BY "CATEGORY_ID") "PP","CATEGORIES_TAB"
 "CT" WHERE "CT"."CATEGORY_ID"=:B1 AND
 "CT"."CATEGORY_ID"="PP"."CATEGORY_ID") THEN 11 ELSE 1 END  AND
 "I"."PRODUCT_ID"="INV"."PRODUCT_ID"(+))
 9 - access("CT"."CATEGORY_ID"=:B1)
 10 - filter("CT"."CATEGORY_ID"="PP"."CATEGORY_ID")
 15 - access("CT"."CATEGORY_ID"=:B1)
 16 - filter("CT"."CATEGORY_ID"="PP"."CATEGORY_ID")

Note
-----
 - rule based optimizer used (consider using cbo)

Nel caso originale in realtà non era necessario alcun hint, ma era sufficente passare da count(*) a * nella select list per forzare l’ottimizzatore ad utilizzare un piano di esecuzione diverso ed a produrre un “result set” diverso.

Il punto sta nella parte:


and inv.warehouse_id(+) =
 case

Il piano di esecuzione corretto prevede solo “NESTED LOOPS OUTER” quello che produce un risultato “non corretto” (in pratica non applica l’operatore (+) ) usa in questo caso un MERGE JOIN. Nella query originale, molto più complessa per la verità, non vi sono MERGE JOIN c’è solo un HASH OUTER JOIN, ne riporto un tratto:

—————————————-
| Id  | Operation                      |
—————————————-
|   0 | SELECT STATEMENT               |
|   1 |  SORT ORDER BY                 |
|*  2 |   FILTER                       |
|*  3 |    HASH JOIN OUTER             |
|*  4 |     HASH JOIN                  |
|*  5 |      TABLE ACCESS FULL         |
|*  6 |      TABLE ACCESS FULL         |
|   7 |     TABLE ACCESS FULL          |
|   8 |    NESTED LOOPS                |
|*  9 |     TABLE ACCESS BY INDEX ROWID|
|* 10 |      INDEX RANGE SCAN          |
|* 11 |     INDEX UNIQUE SCAN          |
—————————————-

Ora, purtroppo non ho ancora studiato bene i piani di esecuzione, ma sotto compare:

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

2 – filter(“campo_fk”(+)=CASE  WHEN  EXISTS

e non mi è chiaro se questo filtro viene applicato dopo la HASH JOIN OUTER della riga 3.

Il database originale è 11g, quello che ho usato io per il test è 10gR2, il caso originale l’ho riprodotto anche su 9iR2. La query fa’ un utilizzo  un po’ ardito dell’operatore (+) ma è sintatticamente corretta e tradurla in sintassi ANSI JOIN non mi pare cosa fattibile (perché in effetti una join non è… o si?) soprattutto non mi sento in grado di farlo con l’assoluta certezza dell’equivalenza.

La documentazione Oracle sull’operatore (+) non dice molto, qualcosa in più lo dicono qui, e la mia impressione in effetti è che il costrutto è molto simile a una “lateral view” un concetto che da un po’ sto cercando di digerire ma che sta ancora li, non ben definito nella mia testa.

SQL JOIN – aggiornamento

venerdì 8 maggio 2009 alle 08:48 | Pubblicato su SQL | Lascia un commento
Tag: , ,

Quasi due anni fa ho scritto un post sui Join in Oracle. Tre giorni fa Jonathan Lewis ha scritto sul suo blog un post intitolato “Dependent Plans” in cui descrive una query per ottenere tutti i piani di esecuzione in cache di query che riferiscono oggetti dipendenti da un particolare oggetto specificato; nel descrivere la query Lewis cita il meccanismo del “lateral join” e mette un link a un suo vecchio post intitolato “Lateral LOBs“, che parla in realtà di lateral views . In questo post viene descritta un’interessante funzione per estrarre un lob a pezzi di varchar2. Jonathan rimanda per la definizione di lateral views a un post del blog “Inside the Oracle Optimizer” intitolato “OuterJoins in Oracle“.

Sono due giorni che studio tutti questi post e i link che ho trovato nei commenti per capire bene il concetto di lateral view. Uno dei link citati rimanda a un post di Jonathan Gennick del 2002 intitolato “What’s in a Condition?” che da un indicazione su come intendere il concetto di join per comprendere meglio i diversi risultati che si possono avere sulle outer join spostando delle condizioni dalla sezione “ON” della join alla sezione “WHERE”. Ebbene, il punto è  che come spiegano quel gruppo di sviluppo dell’ottimizzatore Oracle, almeno in Oracle, le clausole inserite nella parte ON sono valutate prima della JOIN, quelle nella WHERE dopo.

Confrontando però gli esempi che portai quasi due anni fa nel mio post sulle join con la spiegazione di JOIN non riuscivo a capire bene:

SVILUPPO40@perseo10 > create table a (num number);

Tabella creata.

SVILUPPO40@perseo10 > create table b (num number);

Tabella creata.

SVILUPPO40@perseo10 > insert into a values (1);

Creata 1 riga.

SVILUPPO40@perseo10 > insert into b values (2);

Creata 1 riga.

SVILUPPO40@perseo10 > select * from a full outer join b on (1=1);

NUM        NUM
———- ———-
1          2

SVILUPPO40@perseo10 > select * from a full outer join b on (1=0);

NUM        NUM
———- ———-
1
2

SVILUPPO40@perseo10 > select * from a,b;

NUM        NUM
———- ———-
1          2

Dalla spiegazione di Gennick non riuscivo a spiegarmi il caso di select * from a full outer join b on (1=0); in cui ho due record. Ebbene, nella sua spiegazione manca un pezzo, perché dal prodotto cartesiano si in questo caso si ottiene una sola riga. Il suo modello funziona bene per left o right outer join, per la full, probabilmente occorre fare una estensione. Un full outer join in effetti può essere vista come l’unione di una left outer join e una right outer join,  e questa forse è sufficente e corretta come precisazione del modello mentale. Nel mio caso la condizione (1=0) sempre falsa fa eliminare l’unico record risultante dal prodotto cartesiano, quindi subentra il punto 3. dell’articolo di Gennick, che va applicato prima per una tabella e poi per l’altra.

L’articolo di “Inside the Oracle Optimizer” è molto interessante ed istruttivo, l’unico punto un  po’ vago è quello proprio sulle “Lateral Views”, che sono un concetto che fa parte di qualche standard SQL (secondo il manuale SQL Foundation 2003) e in Oracle è implementato tramite la funzione “TABLE” che in sostanza serve a tirare fuori gli elementi da una collection direttamente via SQL, oppure, come mostra Jonathan Lewis nei suoi interessanti esempi, si utilizza con le funzioni “PIPELINED” (ne ho parlato qui).

Oracle SQL Developer – II

martedì 29 luglio 2008 alle 29:52 | Pubblicato su Diario, SQL | 3 commenti
Tag: , , ,

Oggi, grazie alla mia grande agitazione nel battere sulla tasteria ho fatto una scoperta che mi ha fatto fare un urlo di gioia: con l’ultima versione di SQL Developer (di cui ho scritto qualche giorno fa) è ancora possibile scorrere la “history” degli statement eseguiti, è solo cambiata la combinazione di tasti.  Come ho gia detto nella versione precedente si usava la combinazione <ctrl>-<freccia su> o <ctrl>-<freccia-giu> per scorrere in su o in giu tale history e sostituire lo statement da eseguire, ora con quella combinazione viene appeso il comando dalla history con una logica alla quale non riesco ad adeguarmi.

Però con la combinazione <ctrl>-<shift><freccia su> e <ctrl>-<shift><freccia su> si ha lo stesso comportamento di prima, provare per credere.

Quindi ora posso dire che SQL Developer è uno strumento quasi perfetto.

Blog su WordPress.com.
Entries e commenti feeds.