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

Annunci

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/

Il mistero di ORA-00333

lunedì 12 gennaio 2015 alle 12:18 | Pubblicato su 11g, Backup and Recovery, Diario | 3 commenti

Un paio di nostri database server si appoggiano a una NAS, usando iscsi, per lo spazio disco. Uno è un server linux e l’altro window, entrambi hanno dei dischi iscsi su questa Nas esterna e su questi dischi ci sono dei database di sviluppo e test interni. Ogni tanto, per svariati motivi capita che la NAS ha un mancamento, in questa situazione quello che accade sulla macchina linux è che il filesystem, quanto la NAS si riprende, viene automaticamente rimontato in modalità read-only, cosa che naturalmente non piace ad Oracle. Sulla macchina Windows il disco sembra venga rimontato normalmente, ciò nonostante Oracle non la prende comunque bene.

Questa mattina è successo di nuovo, in precedenza è andata sempre abbastanza bene, sulla macchina linux basta fermare Oracle, smontare e rimontare in modalità read-only il disco e riavviare Oracle, sulla macchina Windows basta solo riavviare Oracle, fino ad oggi tali passi erano stato sufficienti. Stamattina però le cose sono andate storte, sulla macchina Windows i due database segnalavano in fase di apertura l’errore

ORA-00333: errore lettura redo log blocco 34873 conteggio 7758

Naturalamente cambiavano i numeri di “blocco” e “conteggio”, quello su linux data un errore di disallineamento dei controlfile:

ORA-00214: control file
‘/opt/oracle/storage/recovery_area/marte112/control02.ctl’ version 1093680
inconsistent with file ‘/opt/oracle/storage/data/marte112/control01.ctl’
version 1093666

Quest’ultimo problema l’ho  affrontato copiando il file con il numero di versione più alto sopra l’altro. Fatto ciò anche qui in fase di apertura del database ho ricevuto il messaggio:

ORA-00333: redo log read error block 55748 count 8192

Ho cercato di mantenere la calma e vedere cosa si poteva fare. Per due database avevo a disposizione un backup della notte prima, quindi ero abbastanza tranquillo, per l’altro non c’è backup per motivi di spazio e perché non è così importante. Ho fatto delle ricerce sull’errore, senza capire però effettivamente la sua origine, perché parrebbe dovuto al fatto che si sia corrotto un online redolog, però facendo una ricerca su internet si trova, anche sul forum Oracle come soluzione un recovery incompleto e la successiva apertura del database con l’opzione RESETLOGS. Messa così la cosa ha senso, si ferma il recovery senza applicare il redo contenuto negli online redo, che a quanto pare sono corrotti e si riesce a far ripartire il database perdendo solo quanto contenuto negli online redolog.

Le cose però non stanno proprio così, perché quello che ho fatto io è stato di applicare anche il redo degli online redolog:

SQL> recover database until cancel;
ORA-00279: change 10685641157734 generated at 01/11/2015 01:14:20 needed for
thread 1
ORA-00289: suggestion :
/opt/oracle/storage/recovery_area/MARTE112/archivelog/2015_01_12/o1_mf_1_28964_%
u_.arc
ORA-00280: change 10685641157734 for thread 1 is in sequence #28964

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/storage/data/marte112/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-00333: redo log read error block 55748 count 8192

 

Ecco, siccome questo era uno dei database di cui avevo il backup ho deciso di fare un piccolo azzardo, pensando non fosse tanto azzardato, ho cercato di aprire il database con l’opzione NORESETLOGS, così da vedere cosa succedeva. Il punto è che il mio recovery mi sembrava completo, non incompleto, perché aveva terminato da solo, non in seguito a un mio “CANCEL”, il redo che ho indicato era l’unico che dalla vista V$LOG risultava attivo e contenente la sequenza richiesta dalla procedura di recovery (gli altri due risultavano anche archiviati), quindi in questa fase Oracle è riuscito ad aprire e leggere l’online redolog senza problemi, senza darmi ORA-00333, come mai? Io non l’ho capito.

Torniamo adesso al mio azzardo/esperimento di aprire il database con l’opzione NORESETLOGS, i miei successivi tentativi sono stati questi:

SQL>  alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/opt/oracle/storage/data/marte112/system01.dbf’

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/opt/oracle/storage/data/marte112/system01.dbf’

 

Al che ho cominciato a preoccuparmi perché mi scocciava dover ripristinare tutto il backup. Al che ho deciso di fare un disperato tentativo, disperato perché per la verità senza cognizione di causa:

SQL>  recover database using backup controlfile until cancel;
ORA-00279: change 10685641181646 generated at 01/11/2015 03:06:33 needed for
thread 1
ORA-00289: suggestion :
/opt/oracle/storage/recovery_area/MARTE112/archivelog/2015_01_12/o1_mf_1_28964_%
u_.arc
ORA-00280: change 10685641181646 for thread 1 is in sequence #28964

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/storage/data/marte112/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

 

Ho cioè deciso di provare il recovery incompleto aggiungendo l’opzione “using backup controlfile”, e mi è andata bene 🙂

Il mio controlfile era sempre lo stesso, non era stato ripristinato da un backup. Che cosa significhi veramente la clausola “using backup controlfile” non sono sicuro di averlo ancora capito bene, la migliore spiegazione che ho trovato è questa. Però mi sfugge cosa abbia fatto il mio primo tentativo si recovery… mi piacerebbe capirne di più

P.S.

naturalmente sugli altri due database è stato più semplice, è bastato usare subito  l’opzione RESETLOGS

Ricerche case sensitive in Oracle 11gR2 e 12cR1

venerdì 22 agosto 2014 alle 22:03 | Pubblicato su 11g, 12c, Diario, Performance Tuning | Lascia un commento
Tag: , ,

Questo post è un aggiornamento del post “Database case sensitive su 10.2.0.3“,  aggiornamento necessario, essendo il post risalente ad oltre sette anni fa. In realtà ho fatto qualche test in questi sette anni ma non ben formalizzato, quando poi mi è stato chiesto per l’ennesima volta se e come era possibile fare ricerche case insensitive su database Oracle (versione 11.2) ho prima dato la solita risposta, poi però mi è venuto lo scrupolo di ricontrollare se fosse cambiato qualcosa sulla versione che attualmente usiamo maggiormente (11.2.0.3 o 11.2.0.4) e sulla nuova 12.1 che installai qualche mese fa per i primi test sulle novità della versione. Riporto sotto il test completo che ho eseguito rispettivamente su un Oracle 10.2.0.5, un 11.2.0.3 e un 12.1.0.1, prima però anticipo alcune conclusioni: con la 11.2.0.3 le cose sono migliorate rispetto alla 10.2, mentre fra la 11.2 e la 12.1 non noto differenze rilevanti, neanche confrontanto velocemente lo stesso capitolo della documentazione nelle tre versioni:

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 15:57:31 2014

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

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

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@geotutf8 > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@geotutf8 > create sequence stest_ci_search;

Sequence created.

CRISTIAN@geotutf8 > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search where id<=100000
;

100000 rows created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@geotutf8 > commit;

Commit complete.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@geotutf8 > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@geotutf8 > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

CRISTIAN@geotutf8 > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@geotutf8 >  set autotrace on
CRISTIAN@geotutf8 > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.012672 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.031840 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.051397 PM

300004 cristian                       Cudizio                                 1 22-08-2014 15:57:54
22-AUG-14 03.57.54.832366 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 18365057

------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |    46 |  3772 |   913   (1)| 00:00:11 |
|*  1 |  TABLE ACCESS FULL| TEST_CI_SEARCH |    46 |  3772 |   913   (1)| 00:00:11 |
------------------------------------------------------------------------------------

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

1 - filter("STRING1" LIKE 'cris%')

Note
-----
- dynamic sampling used for this statement

&nbsp;


C:\tmp>sqlplus cristian/cristian@//10.110.3.52/salespdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 22 13:08:07 2014

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

ERROR:
ORA-28002: the password will expire within 7 days

Last Successful login time: Wed Apr 09 2014 15:21:53 +02:00

Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

PL/SQL procedure successfully completed.

Session altered.

CRISTIAN@//10.110.3.52/salespdb > create table test_ci_search (id number,string1 varchar2(30),string2 varchar2(30) ,numero number,data date, datastamp timestamp);

Table created.

CRISTIAN@//10.110.3.52/salespdb > create sequence stest_ci_search;

Sequence created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search
2  select stest_ci_search.nextval, dbms_random.string('A',30), dbms_random.string('P',30), dbms_random.random,sysdate, systimestamp
3  from dual connect by level <= 100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, upper(string1), upper(String2), numero, data,datastamp from test_ci_search;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search select stest_ci_search.nextval, lower(string1), lower(String2), numero, data,datastamp from test_ci_search wh
ere id<=100000;

100000 rows created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'Cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CriStian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'CRISTIAN','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb > insert into test_ci_search values (stest_ci_search.nextval,'cristian','Cudizio',1,sysdate,systimestamp);

1 row created.

CRISTIAN@//10.110.3.52/salespdb >
CRISTIAN@//10.110.3.52/salespdb > commit;

Commit complete.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 ='cristian';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

CRISTIAN@//10.110.3.52/salespdb > create index test_ci_search_idx on test_ci_search (nlssort(string1,'nls_sort=binary_ci'));

Index created.

CRISTIAN@//10.110.3.52/salespdb > set autotrace on
ERROR:
ORA-28002: the password will expire within 7 days

SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
CRISTIAN@//10.110.3.52/salespdb > select * from test_ci_search where string1 like 'cris%';

ID STRING1                        STRING2                            NUMERO DATA
---------- ------------------------------ ------------------------------ ---------- -------------------
DATASTAMP
---------------------------------------------------------------------------
300001 Cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.457087 PM

300002 CriStian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.466520 PM

300003 CRISTIAN                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.475596 PM

300004 cristian                       Cudizio                                 1 22-08-2014 13:11:06
22-AUG-14 01.11.06.484698 PM

Execution Plan
----------------------------------------------------------
Plan hash value: 1977262958

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   780 |   159K|  1125   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_CI_SEARCH     |   780 |   159K|  1125   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_CI_SEARCH_IDX |  1404 |       |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

2 - access(NLSSORT("STRING1",'nls_sort=''BINARY_CI''')>=HEXTORAW('6372697300') AND
NLSSORT("STRING1",'nls_sort=''BINARY_CI''')<HEXTORAW('6372697400'))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)

In sintesi, fino a Oracle 10.2.0.5 era possibile abilitare ricerche su stringhe in modalità case insensitive, il problema era però che dall’uso dell’indice linguistico erano escluso l’operatore LIKE, dalla versione 11.2 in poi invece anche con l’operatore LIKE viene usato l’indice. Ho riportato solo i test per 10.2.0.5 e 12.1.0.1, per 11.2.0.3 il risultato è uguale a quello di 12.1.0.1

Novità sulla gestione dello spazio in Oracle 11gR2

venerdì 30 maggio 2014 alle 30:22 | Pubblicato su 11g | 1 commento

Sono un po’ indietro,  questo spiega un post su una novità introdotta con Oracle 11gR2 (in realtà parte con 11.2.0.2) a diversi mesi dall’uscita di oracle 12c. Sto cercando di non rimanere indietro e di non perdermi informazioni, quindi sto ripassando il manuale “Oracle® Database New Features Guide” versione 11gR2, quando avrò finito con quello potrò permettermi di passare all’equivalente manuale versione 12c R1. La maggior parte delle nuove funzionalità e caratteristiche introdotte da Oracle non mi interessa, cerco di contentrarmi su quelle che posso sfruttare utilmente, senza sprecare risorse a studiare a tappeto tutto. Una su cui secondo me vale la pena soffermarsi, o meglio due, sono la gestione dello spazio, tramite l’uso del comando “TRUNCATE” e tramite l’uso del package DBMS_SPACE_ADMIN.

Il comando TRUNCATE è stato esteso per poter richiedere o  meno il rilascio dello spazio allocato dalla tabella. Il comando usato come nelle versioni precedenti, ad esempio:


TRUNCATE TABLE test_truncate;

Si comporta come nelle versioni precedenti di Oracle (lasciando allocati gli extent eventualmente indicati con il parametro MINEXTENT). Quanto sopra è equivalente, con la nuova estensione a:


TRUNCATE TABLE test_truncate DROP STORAGE;

Vi sono poi due possibilità nuove, la prima è lasciare lo spazio allocato per la tabella allocato, in tal caso il comando usa l’opzione “REUSE STORAGE”:


TRUNCATE TABLE test_truncate REUSE STORAGE;

All’estremo opposto vi è la possibilità di rilasciare tutto, ma proprio tutto lo spazio allocato dalla tabella eliminando il segmento, in questo caso l’opzione è “DROP ALL STORAGE”:


TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Cerco di riportare un po’ di esempi per fare capire meglio.


SVILUPPO40@SVIL112_METHONE > create table test_truncate(a number, b varchar2(4000));

Table created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name = 'TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > begin
2  for i in 1..10000 loop
3  insert into test_truncate values (1,rpad('testxx',4000,'z'));
4  end loop;
5  end;
6  /

PL/SQL procedure successfully completed.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                             80

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate REUSE STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                             80

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate ;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                          .0625

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate REUSE STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                          .0625

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > DROP TABLE TEST_TRUNCATE PURGE;

Table dropped.


SVILUPPO40@SVIL112_METHONE > create table test_truncate(a number, b varchar2(4000)) STORAGE (MINEXTENTS 100);

Table created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE > INSERT INTO test_truncate values (1,'a');

1 row created.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                            104

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

SEGMENT_NAME                                                                              MB
--------------------------------------------------------------------------------- ----------
TEST_TRUNCATE                                                                            104

SVILUPPO40@SVIL112_METHONE > TRUNCATE TABLE test_truncate DROP ALL STORAGE;

Table truncated.

SVILUPPO40@SVIL112_METHONE > select SEGMENT_name,BYTES/1024/1024 MB from user_segments where segment_name like '%TEST_TRUNCATE' ;

no rows selected

SVILUPPO40@SVIL112_METHONE >

Parallelamente al package DBMS_SPACE_MANAGEMENT sono state aggiunte due procedure:

  1. DROP_EMPTY_SEGMENTS
  2. MATERIALIZE_DEFERRED_SEGMENTS

Il primo può essere utile in caso di database migrati, in cui ci sono magari un sacco di tabelle vuote per cui, provenendo da versioni Oracle precedenti alla 11.2 dove non esisteva la “deferred segment creation”, vengono comunque creati i segmenti. Va detto che nei miei casi si tratta comunque di briciole di spazio per cui non so quanto valga la pena rompersi, ma se uno vuole raschiare il fondo così può farlo 🙂 Il package DBMS_SPACE_ADMIN anche se non scritto in modo chiarissimo sul manuale, mi risulta utilizzabile solo da utente SYS, invocando la procedure DROP_EMPTY_SEGMENTS specificando solo il primo parametro (lo schema) viene spazolato tutto lo schema e per le tabelle vuote vengono eliminati i segmenti, altrimenti è possibile specificare le tabelle per cui si vuole agire.

L’utilità della procedura MATERIALIZE_DEFERRED_SEGMENTS mi viene ancora più difficile da immaginare, comunqu, per completezza fa esattamente il contrario della precedente, crea il segmento di partenza per tutte le tabelle.

Standby database per Oracle standard edition 11gR2

mercoledì 15 gennaio 2014 alle 15:36 | Pubblicato su 11g, Installation and Configuration | Lascia un commento
Tag: , ,

Sono passati oltre quatto anni da quando scrissi questo post in cui parlai dell’implementazione del sistema di disaster recovery basato sullo standby database su Oracle standard edition. Qualche tempo fa mi è stato chiesto se eravamo in grado di mettere in piedi questa soluzione, siccome però sono passati gli anni  però, ora la nostra versione di riferimento è oracle 11.2 e non più 10.2 con la quale feci i test a suo tempo. C’è da dire che a suo tempo ho anche messo in piedi una implementazione completa, con tutta la gestione automatizzata del trasferimento degli archived log e della loro applicazione sullo standby database su una installazione con Oracle 9.2.

Con un po’ di pazienza sono riuscito a crearmi un ambiente virtualizzato (con virtual box, che per questi test sembra funzionare molto bene). Ho creato quindi una macchina virtuale con Oracle Linux 6.3 64 bit, vi ho installato Oracle Database 11.2.0.3. A questo punto ho clonato la macchina e in qualche modo ho sistemato il nome e l’indirizzo IP. Ho creato un database sulla prima macchina, poi ho seguito i miei appunti usati a suo tempo per Oracle 9.2 per creare lo standby database e fare qualche prova: il risultato è che funziona tutto allo stesso modo in cui funzionava su Oracle 9.2 e Oracle 10.2. L’unico inghippo l’ho riscontrato quando ho fatto un test che forse a suo tempo non avevo fatto: l’aggiunta di una nuova tablespace con relativo database; in questo caso mi sono imbattuto nel problema ben descritto qui, così ho scoperto l’esistenza del parametro STANDBY_FILE_MANAGEMENT. Per rendermi la vita facile quello che ho fatto è stato di modificare il parametro e ricreare da zero lo standby database.

Ieri sono riuscito a fare un ulteriore test, il cui esito al momento pare positivo, cioè applicare l’aggiornamento alla versione 11.2.0.4.  Mi sono trovato un po’ spiazzato con questo aggiornamento, per due motivi: il primo è che non ho trovato al suo interno le solite istruzioni (ho trovato la guida “Upgrade” fra la documentazione ufficiale ma questa, a voler essere pignoli, si riferisce alla versione 11.2.0.3; il secondo motivo di spiazzamento è che fare l’aggiornamento “in-place” è stato reso complicato da Oracle. Ho trovato comunque lo spazio per fare l’aggiornamento “out-of-place” come richiesto, quindi creando una nuova Oracle Home. Alla fine della parte di aggiornamento della parte “software” l’installer lancia automaticamente DB Upgrade Assistant (DBUA) che nonostante i miei timori ha eseguito la parte di aggiornamento del database senza errori.

Il bello però viene nell’aggiornamento dello standby database. La mia idea era quella di aggiornare il software e sperare che la sincronizzazione tramite l’applicazione degli archived logs funzionasse aggiornando anche lo standby database. Ciò che è accaduto è che lanciando anche sullo standby l’installer di 11.2.0.4 è andato tutto bene fino alla parte di aggiornamento del software, poi è partito DBUA il quale però manco trovava le informazioni sul database da aggiornare, in quanto in effetti non aveva le informazioni registrate sul file /etc/oratab. Poco male, anzi stavo per aggiungerle quando mi sono reso conto che comunque DBUA non avrebbe potuto fare nulla perché lo standby database non è apribile in scrittura (se non con una switch-over) . Io qui ho interrotto la procedura ed ho provveduto manualmente ad applicare gli archived logs generati sul primary e tutto pare aver funzionato bene, ho fatto nuovi test e pare funzionare come atteso (l’unica cosa che non ho detto è che ho copiato il contenuto della directory “dbs” dalla vecchia alla nuova Oracle Home.

Prossimamente conto  di fare lo stesso test con Oracle 12.1

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

Ancora Character Set, ORA-00600 [kafspa:columnBuffer2]

giovedì 1 agosto 2013 alle 01:55 | Pubblicato su 11g, Diario, Installation and Configuration, Varie | Lascia un commento
Tag: , ,

Non c’è dubbio che la gestione del Character Set sui database Oracle sia una cosa tutt’altro che banale e poco chiara ai più. Ieri controllando i log di un database interno utilizzato per i test ho trovato il seguente errore:

ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kafspa:columnBuffer2], [4053], [4000], [], [], [], [], []

Il database è un 10.2.0.5 su linux 64 bit, recentemente migrato da una vecchia macchina dove c’era però una versione 10.2.0.3. Ho passato i dati dal vecchio database al nuovo tramite expdp/impdp dei vari schemi (approfittando per fare un po’ di pulizia). L’errore veniva generato durante il calcolo delle statistiche notturno e nell’alert.log c’era rimando a un file di trace su cui ho trovato anche dettagli sulla tabella che provocava l’errore. Il secondo e terzo argomento dell’errore ORA-00600 mi ha fatto subito sospettare il tipo di problema (che fra l’altro in forme diverse ho gia incontrato altre volte. In effetti ho individuato nella tabella un campo definito come VARCHAR2(4000) facendo una query simile a questa:


SELECT NOMECAMPO FROM NOMETABELLA WHERE LENGTH(NOMECAMPO)>4000

ottenevo sempre l’errore  ORA-00600.

Facendo una ricerca sul supporto Oracle ho trovato poco, facendone una Google sono arrivato qui, come dire: a volte ritornano. Infatti in coda al post che non ricordavo di aver gia letto ho trovato dei miei commenti. Avendo anche cambiato il titolo del blog non mi ricordavo neanche chi fosse l’autrice 🙂

In realtà ho scoperto con un po’ di sorpresa che lo schema proveniva in effetti da un dump fornitoci dal cliente, che però io credevo avesse database con character set UTF-8 solo di recente sono riuscito a scoprire che in realtà il cliente utilizza charset ISO8859-15 e che quindi ero incappato sullo stesso problema. Il bello è che per trovare una soluzione, dopo alcuni banali tentativi per isolare i record con stringhe lunghe più di 4000 byte all’interno del campo incriminato, ho provato a fare un export con data pump e importare la tabella su un’altro schema dello stesso database, con una certa sorpresa mi sono trovato sul nuovo schema una copia della tabella con lo stesso problema.

Dopo vari esperimenti, alla fine, sfruttando la chiave primaria della tabella, che è un id generato da sequence,  ho isolato un record con il problema; in realtà ho trovato anche altri 6 record che su un campo varchar2(600) avevano stringhe che occupavano ben più di 600 byte ma questi record creavano problemi solo nei tentativi di CREATE TABLE AS …

In teoria ho risolto facendo un update sul campo per chiave:


UPDATE NOMETABELLA SET NOMECAMPO=SUBSTR(NOMECAMPO,1,4000) WHERE CAMPOCHIAVE=XXX

che con un certo mio sconcerto ha funzionato (apparentemente), in seguito ho fatto alcune prove, compreso il ricalcolo delle statistiche, senza riscontrare problemi, stamattina però nell’alert.log ho ritrovato l’errore sulla stessa tabella, in realtà mi aspettavo di trovare l’errore sulla copia che ho mantenuto con l’errore per fare ulteriori verifiche è così è stato anche se in un primo momento non era chiaro guardando il trace.

Stamattina ho avuto modo di fare ricerche più approfondite e mirate sul problema, leggendo nota del supporto Oracle intitolata:

“Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)”

al paragrafo 1.d ho trovato la seguente frase:

Do NOT use Expdp/Impdp when going from (AL32)UTF8 to a single byte NLS_CHARACTERSET on ALL 10g versions lower then 10.2.0.4 (including 10.1.0.5). Also 11.1.0.6 is affected.
It will provoke data corruption unless Patch 5874989 is applied on the Impdp side. Expdp is not affected, hence the data in the dump file is correct.Also the “old” exp/imp tools are not affected.
This problem is fixed in the 10.2.0.4 and 11.1.0.7 patch set.
Fixed in 11.2.0.1 and up

 

che mi ha un po’ illuminato. In realtà ho dovuto un po’ ricostruire la storia. Sul nostro nuovo database di test 10.2.0.5 (che dovrebbe essere esente dal bug) ho due schemi dello stesso cliente, uno importato direttamente dal dump fornito dal cliente e su cui non si presenta il problema, infatti nei log dell’import trovo:

ORA-02374: conversion error loading table “SCHEMA2″.”NOMETABELLA”
ORA-12899: value too large for column NOMECAMPO (actual: 3995, maximum: 4000)
ORA-02372: data for row:

L’altro schema, sui ho trovato il problema, proveniva dallo stesso dump, ma era passato prima dal vecchio database 10.2.0.3, quindi era stato importato sul vecchio db, dove si era generata la corruzione (senza che io me ne accorgessi), poi da li è stato esportato  e importato sul nuovo database, senza errori nei log di data pump. Mi pare quindi che Oracle abbia corretto parzialmente il problema, perché impdp non genera più la corruzione sulla tabella ma scarta i dati in importazione, però, se la tabella è gia corrotta essa viene esportata e importata da datapump tale e quale, corruzione compresa. Prossimamente conto di provare a fare dei test su un Oracle 11.2.0.3, magari se ne trovo uno con character set ISO8859 posso fare dei test più completi.

Riguardo la gestione del character set su Oracle riporto qui un paio di riferimenti a note Oracle che vale la pena leggere:

  • AL32UTF8 / UTF8 (Unicode) Database Character Set Implications (Doc ID 788156.1)
  • Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET (Doc ID 1283764.1)
  • NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

Operatori Spaziali

mercoledì 12 settembre 2012 alle 12:37 | Pubblicato su 11g, Oracle Locator, Oracle Spatial | Lascia un commento
Tag: , , ,

Facendo riferimento al paragrafo 5.2 del manuale “Oracle Spatial user’s guide and reference” versione 11.2, cerco di parlare un po’ degli operatori spaziali. Prima di tutto dico che gli operatori fanno ancora parte della componente Locator (Licensing Information) il che significa che il loro utilizzo è gia possibile con la Standard Edition senza sovrapprezzo.

Gli operatori spaziali sono delle particolari funzioni che si utilizzano nelle clausole WHERE degli statemente per filtrare i dati in base a logiche geometriche; il formato di questi operatori è generalmente questo:


<nome  operatore>(
geometria_tabella SDO_GEOMETRY,
geometria_riferimento SDO_GEOMETRY,
[,stringa_parametri IN VARCHAR2
[,tag IN NUMBER]]
)='TRUE'

Gli operatori funzionano solo su campi SDO_GEOMETRY gia indicizzati, in mancanza di un indice spaziale valido sul campo usato come primo argomento dell’operatore viene restituito un errore.

Il manuale spiega che le query spaziali vengono normalmente eseguite con una logica a due livelli, prima viene fatta una selezione approssimata e poi sul risultato di questa prima selezione viene fatta una selezione esatta. Cosa si intende con selezione approssimata lo si può capire andando a vedere il primo operatore spaziale: SDO_FILTER, questo operatore controlla non se due geometrie interagiscano ma se i loro MBR interagiscono. L’MBR, Minimum Bounding Rectangle di una geometria è il reattangolo più piccolo possibile che contiene la geometria. Per un punto l’MBR è il punto stesso, per un segmento è il segmento stesso, per poligoni più complessi è facile immaginare cosa sia l’MBR. Gli indici spaziali lavorano proprio su MBR e questo permette di fare una prima selezione in modo molto efficente. Riporto paro paro dal manuale un esempio di uso di questo operatore:


SELECT A.Feature_ID FROM TARGET A WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
 SDO_elem_info_array(1,1003,3),
 SDO_ordinate_array(x1,y1, x2,y2))
 ) = 'TRUE';

Gli operatori spaziali vengono usati sempre così, dentro la clausola WHERE ponendo l’uguaglianza alla stringa ‘TRUE’. Quindi SDO_FILTER fa solo una selezione “primaria”, approssimata quindi nell’esempio vengono restituiti tutti i record per cui l’MBR del campo  SHAPE è adiacente o contenuto nel rettangolo i cui vertici estremi sono x1,y1,x2,y2.

Per interrogazioni esatte vi sono altri operatori, il primo citato nella documentazione è SDO_RELATE che serve a trovare geometrie che si “sovrappongono” in qualche maniera; il terzo parametro serve a specificare il tipo di sovrapposizione che si vuole ricercare, un esempio è ‘mask=anyinteract’ ma al posto di anyinteract (il cui significato mi sembra intuitivo) si può specificare contains, covers, inside, equal, ecc. per le combinazioni diverse da anyinteract il significato può essere poco intuitivo ma qui non voglio approfondire questo aspetto e mi limito a una veloce carrellata degli operatori, precisando che esistono per ciascuna delle possibilità degli operatori dedicati, ad esempio SDO_CONTAINS, SDO_ANYINTERACT, ecc.

Per trovare oggetti entro una certa distanza da un riferimento si può utilizzare l’operatore SDO_WITHIN_DISTANCE, specificando come stringa per il terzo parametro qualcosa come ‘distance=5’ dove l’unità di misura nel caso di sistema di riferimento geodetico dovrebbe essere sempre metri. Faccio notare che nella documentazione c’è qualcosa che non va, si dice che l’operatore non è efficente per fare join fra due tabelle, però l’esempio per una soluzione alternativa usa la funzione SDO_BUFFER senza speficare il package (SDO_GEOM) e usando la parola UNRECOVERABLE che stando alla documentazione è deprecata.

L’ultimo operatore di cui parlo è SDO_NN, NN sta per Nearest Neighbor, quindi questo operatore serve a trovare gli oggetti più vicini a un certo oggetto (quello specificato nel secondo argomento). Senza specificare nulla per il terzo parametro usando questo operatore si trovano tutti gli oggetti in ordine di distanza crescente, altrimenti specificando come terzo parametro ‘sdo_num_res=n’ dove n è un numero intero si trovano i primi n oggetti  più vicini (però non più in ordine di distanza crescente).

Sia per l’operatore SDO_WITHIN_DISTANCE che per l’operatore SDO_NN viene specificato che non sono supportati per fare join spaziali, quindi non sono supportate query del tipo:


select * from tab1 a, tab2 b where sdo_nn(a.geom,b.geom,'sdo_num_res=1')='TRUE'

Infatti io ho provato e può accadere che funzioni come che dia errore tipo questo:

ERROR at line 1:
ORA-13249: SDO_NN cannot be evaluated without using index
ORA-06512: at “MDSYS.MD”, line 1723
ORA-06512: at “MDSYS.MDERR”, line 17
ORA-06512: at “MDSYS.PRVT_IDX”, line 9

anche se entrambi i campi sono indicizzati. Di anomalie sul funzionamento se ne parla anche qui, ma io ad esempio sono riuscito a farlo funzionare solo con l’hint INDEX, ma visto l’avviso di non supporto dato sulla documentazione non so quanto sia affidabile.

Assieme all’operatore SDO_NN è possibile usare l’operatore discendente (ancillary) SDO_NN_DISTANCE che messo nella sezione SELECT ritorna anche la distanza fra gli oggetti selezionati e quello di riferimento.

Per eseguire vere e proprie join spaziali occorre usare l’opertore SDO_JOIN, che però come si può vedere dalla documentazione non è un vero operatore ma una funzione (una table function). Non ho ancora fatto molti test per poter dire se SDO_JOIN sopperisce completamente ai problemi che ho avuto sopratutto con SDO_NN nel caso di join fra tabelle, spero di dedicarci un post apposito.

 

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

Pagina successiva »

Blog su WordPress.com.
Entries e commenti feeds.