[sql] DB Oracle: devo usare istruzioni PL/SQL?

[yssj485]

Innanzi tutto.. non sono affatto un esperto di sql, e ancor meno di Oracle, sono però stato buttato nella mischia e devo cercare di cavarmela.

La situazione è questa.. ho una tabella di questo tipo:

Chiave primaria composta da: ( in maiuscolo il nome dei campi)
- un numero di utenza : UTZ
E
- un numero, molto più piccolo, progressivo.
Ogni record ha due date associate che ne indicano l'intervallo di validità, DAT_DAL e DAT_AL ( quindi sia scadute che attive, con validità fino al 2099 ), uno status STATUS( attivo o cessato, da cui dipendono chiaramente le due date) ed altri dati al momento non necessari.

Insomma, ogni utente può avere diversi record, grazie al progressivo, ma può averne uno solo attivo e svariati cessati.
Le possibilità sono:
- tutti cessati
- uno attivo
- uno attivo con uno/più record cessati

Tra i miei vari obiettivi, c'è quello di andare a recuperare, per gli utenti con un record attivo, il record attivo ma anche quello scaduto più di recente (SE posseduto) e non basandomi sul progressivo che invece potrebbe essere fuorviante per problemi nel sistema.

Il problema è, direi oltre le mie conoscenze di sql e di oracle, e l'unica idea che mi era venuta è quella di affrontarlo diciamo più dal punto di vista della programmazione classica, appoggiandomi a dei costrutti di loop e/o if-then-else di pl/sql, che ho sbirciato sulla rete.
La domanda quindi è.. è possibile fare quello che mi è richiesto, solo con comandi sql? L'uso dei costrutti loop e if, è conveniente? Sono performanti?

Al momento stavo pensando di cercare ordinare per data e poi prendere la prima riga ( tra le cessate chiaramente), in modo di andare a prendere la cessata più recente, ma farlo per tutte le utenze interessante è problematico..

Scusate per la lunghezza ma dovevo cercare di essere chiaro!

[zuper]

se posti delle righe di esempio magari ci è + facile...

come linee guida farei:

tutti gli attivi

select utente from tabella where stato='attivo'

quello scaduto per ultimo tra quelli attivi

select utente,max(datascadenza) from tabella where utente in (select utente from tabella where stato='attivo') and status<>'attivo'

ma chiaramente sono solo idee base che vanno adattate al tuo contesto

[FabryHw]

Questa query dovrebbe fare quello che chiedi

;WITH Attivi AS (
  SELECT
    UTZ,
    PROG,
    DAT_DAL,
    DAT_AL,
    STATUS
  FROM
    TuaTabella
  WHERE
    STATUS = 1 --Supponendo che il campo sia di tipo Bit quindi 0/1
), Filtro AS (
  SELECT
    UTZ,
    MAX(DAT_AL) AS ULTIMO_DAT_AL
  FROM
    TuaTabella
  WHERE
    STATUS = 0 --Supponendo che il campo sia di tipo Bit quindi 0/1
  GROUP BY
    UTZ
), Disattivi AS (
  SELECT
    UTZ,
    PROG,
    DAT_DAL,
    DAT_AL,
    STATUS
  FROM
    TuaTabella T1 
  INNER JOIN
    Filtro T2 ON
     T1.UTZ = T2.UTZ AND
     T1.DAT_AL = T2.ULTIMO_DAT_AL
)
SELECT
  A.*,
  D.*
FROM
  Attivi A
LEFT OUTER JOIN
  Disattivi D ON
    A.UTZ = B.UTZ

Visualizza un record per ogni Utenza attiva e sullo stesso record visualizza l'eventuale (potrebbe non essere presente) ultimo record disattivo.

Note:

• Il codice sopra indicato è per SQL Server 2005, ma al 95% dovrebbe essere uguale anche sotto Oracle (che io non conosco)
• Il codice usa, per migliorare la leggibilità, le Common Expression Table.
  Esse dovrebbero essere presenti anche in Oracle, comunque non è difficile riscrivere il tutto con subquery (dette viste inline sotto Oracle)
• Si suppone che il record più recente sia quello con DATA_AL maggiore
• Si suppone anche che a parità di utenza NON ci possano essere 2 record con DATA_AL uguale.
  Se succede otterrai record duplicati nel risultato della query

Ciao

[yssj485]

Innanzi tutto, un ringraziamento ad entrambi
Adesso sto installando Oracle a casa e farò qualche prova con i vostri consigli..
in effetti non avevo pensato all'utilizzo della funziona max per una data. Si vede che sono a digiuno di sql!

Buono pure il consiglio per le Common Expression Table, in effetti sembrano molto utili per la leggibilità e, a guardare in giro, sembra siano supportate da Oracle.

Originariamente inviato da FabryHw

Questa query dovrebbe fare quello che chiedi

Visualizza un record per ogni Utenza attiva e sullo stesso record visualizza l'eventuale (potrebbe non essere presente) ultimo record disattivo.

Note:

• Si suppone anche che a parità di utenza NON ci possano essere 2 record con DATA_AL uguale.
  Se succede otterrai record duplicati nel risultato della query

Beh, diciamo che il mio obiettivo è creare una tabella contenente un sottoinsieme dei record della tabella iniziale.. quindi il fatto di ottenere un prodotto cartesiano del record attivo con il suo record scaduto più recente, se presente, non è sufficiente.
Indubbiamente mi basterebbe fare due diverse query, una per gli attivi, ed una per gli scaduti più recenti.. ma, una curiosità, è possibile scorrere una tabella e fare per ogni riga due operazioni?
In questo caso prendere i primi campi per creare ed inserire un record "attivo", ed i rimanenti per un record "scaduto".

Grazie ancora

ps. se mi date qualche consiglio su cose che potrei studiarmi/impararmi, del tipo delle CTE, PL/sql, o materiale online, fareste una cosa graditissima

[yssj485]

Originariamente inviato da zuper

s

quello scaduto per ultimo tra quelli attivi
select utente,max(datascadenza) from tabella where utente in (select utente from tabella where stato='attivo') and status<>'attivo'

Ho tradotto in codice quello che dicevi.. Diciamo che i record che andrebbe a prendere sono giusti, il problema è che, come dicevo prima, io devo creare una tabella con un sottoinsieme dei record della tabella principale, quindi devo prendere tutti i campi.

In questo caso, nella select esterna vado a raccogliere sì i record giusti, ma non mi faccio restituire la chiave primaria(composta anche dal numero progressivo, oltre che dall'utenza) e quindi non posso andare a recuperare tutti i dati dei record in maniera "pulitissima". Ad esempio, ggiungendo una query che confronta num_utz e dat_al, sembra che vada, e non credo che dovrei avere problemi, ma non usare la seconda parte della chiave per identificare il record, direi che non mi piace troppo.. che dici?
Prestazionalmente poi come credi che vada? Considera che dovrò buttarla su una tabella con almeno un milione di record..

Ecco il codice ( ho usato subito le CTE):

WITH SCADUTI AS(
  SELECT NUM_UTZ, MAX(DAT_AL) AS DAT_AL
  FROM TABELLA
  wHERE NUM_UTZ IN (SELECT NUM_UTZ
                    FROM TABELLA
                    WHERE COD_STAT = 'attivo' )
        AND
        COD_STAT = 'scaduto'
  GROUP BY NUM_UTZ )
SELECT D.*
FROM TABELLA T
INNER JOIN SCADUTI S
ON T.NUM_UTZ = S.NUM_UTZ
WHERE T.DAT_AL = S.DAT_AL;

[yssj485]

Originariamente inviato da FabryHw

Questa query dovrebbe fare quello che chiedi

Ciao

Ho modificato ora qualcosina della tua, nella SELECT finale, per ottenere quello che mi serve.. il problema ora è capire tra le due, quella che dovrebbe essere più veloce.. mi piacerebbe capire meglio la questione delle performance e dei costrutti preferibili da questo punto di vista.

[FabryHw]

Originariamente inviato da yssj485

Beh, diciamo che il mio obiettivo è creare una tabella contenente un sottoinsieme dei record della tabella iniziale.. quindi il fatto di ottenere un prodotto cartesiano del record attivo con il suo record scaduto più recente, se presente, non è sufficiente.
Indubbiamente mi basterebbe fare due diverse query, una per gli attivi, ed una per gli scaduti più recenti.. ma, una curiosità, è possibile scorrere una tabella e fare per ogni riga due operazioni?
In questo caso prendere i primi campi per creare ed inserire un record "attivo", ed i rimanenti per un record "scaduto".

Si puoi eseguire codice procedurale un record alla volta o aprendo un cursore o copiando i dati in una tabella temporanea con un indice progressivo e aprendo un loop.
Non ti faccio vedere un esempio di cursore perché probabilmente la sintassi Sql Server è diversa da quella Oracle.

Poi elaborando un record alla volta puoi fare tutte le operazioni che vuoi, cmq nel tuo caso non sarebbero lo stesso 2 operazioni sullo stesso record (riga) dato che record attivo e ultimo record scaduto sono appunto 2 record.

Cmq su un DB relazionale il codice procedurale dovresti evitarlo il più possibile e non usarlo mai quando ne puoi fare a meno.

Esempio questo codice dovrebbe fare al caso tuo ma senza parsare 1 record alla volta:

;WITH Lista AS (
  SELECT
    UTZ,
    PROG,
    DAT_DAL,
    DAT_AL,
    STATUS,
    ROW_NUMBER() OVER (PARTITION BY UTZ ORDER BY DAT_AL DESC) AS ROW_ID
  FROM
    TuaTabella T1 
)
SELECT
    L.UTZ,
    L.PROG,
    L.DAT_DAL,
    L.DAT_AL,
    L.STATUS
FROM
  Lista L
WHERE
  ROW_ID <= 2

La query ordina per data_al ed assegna un contatore progressivo che riparte da 1 ad ogni utenza.
Di conseguenza con ID 1 avrai tutti i record attivi e con ID da 2 in poi i record disattivi in ordine dal più recente al più vecchio.
Filtrando per tenere solo gli id 1 e 2 ottieni proprio quello che volevi tu, ossia per ogni utenza ti dà 1 record attivo e l'eventuale record disattivo.

Row_Number() è una delle funzioni di rango introdotte con Sql Server 2005, non so se Oracle le ha, ma supporrei di si.

[FabryHw]

Originariamente inviato da yssj485

Ho modificato ora qualcosina della tua, nella SELECT finale, per ottenere quello che mi serve.. il problema ora è capire tra le due, quella che dovrebbe essere più veloce.. mi piacerebbe capire meglio la questione delle performance e dei costrutti preferibili da questo punto di vista.

Quasi sempre ci sono più strade per ottenere un risultato.
Basta pensare che la stessa cosa la puoi fare con un procedurale che looppa che con una query per vedere che hai già 2 soluzioni possibili.

Poi quale sia tra le N soluzioni la più performance dipende da molte variabili (indici sui campi che sono interessati, bontà del codice che scrivi, ...ecc).
Di solito sta all'abilità del programmatore scegliere la soluzione migliore o il migliore compromesso (es. metterci 3 giorni a fare un codice che poi è solo il 30% più veloce di un codice che potevi fare in 4 ore, è forse uno spreco).

Prova più soluzioni e poi studiati il piano di esecuzione del DB per vedere quando è efficiente il codice.

Ciao

PS
Se hai tabelle con milioni di record, l'approccio procedurale un record alla volta è un suicidio per il DB ed impiegherà tempi notevoli (dell'ordine di minuti o anche ore a seconda della potenza della macchina).

[zuper]

Originariamente inviato da FabryHw

Quasi sempre ci sono più strade per ottenere un risultato.
Basta pensare che la stessa cosa la puoi fare con un procedurale che loopa che con una query per vedere che hai già 2 soluzioni possibili.

Poi quale sia tra le N soluzioni la più performance dipende da molte variabili (indici sui campi che interessati, bontà del codice che scrivi, ...ecc).
Di solito sta all'abilità del programmatore scegliere la soluzione migliore o il migliore compromesso (es. metterci 3 giorni a fare un codice che poi è solo il 30% più veloce di un codice che potevi fare in 4 ore, è forse uno spreco).

Prova più soluzioni e poi studiati il piano di esecuzione del DB per vedere quando è efficiente il codice.

Ciao

PS
Se hai tabelle con milioni di record, l'approccio procedurale un record alla volta è un suicidio per il DB ed impiegherà tempi notevoli (dell'ordine di minuti o anche ore a seconda della potenza della macchina).

quoto ogni singola parte
soprattutto quella di provare varie soluzioni per poi implementare quella che secondo te è corretta!

aggiungerei anche che dovresti sempre tenere conto di eventuali sviluppi futuri...
se per esempio questa query dovesse essere adattata a diverse situazioni, oppure ampliata per nuove richieste è meglio cercare di scrivere qualcosa di customizzabile...

insomma noi possiamo darti delle linee guida (e mi pare che hai già da lavorare abbastanza ) ma non conoscendo fino in fondo la situazione nn è mai facile trovare la soluzione "perfetta" anche perchè nn c'+ quasi mai

metti che devi lanciare le estrazioni una tantum e di notte, si potrebbe optare per una soluzione magari + macchinosa ma + customizzabile appunto.
se invece dovessi estrarre quelle info magari al volo e in qualsiasi momento, beh la snellezza prevale!

insomma...devi capire bene tu

[yssj485]

Beh ragazzi, vi ringrazio nuovamente, e posso dire di aver imparato più cose chiacchierando con voi e studiandomi le cose di cui avete parlato, che riguardandomi il libro di Basi di Dati

Il discorso delle prestazioni è più che chiaro, però vorrei capire se ci sono occasioni in cui è meglio utilizzare una certa struttura piuttosto che un'altra.. non esistono, vi chiedo da perfetto ignorante, software che valutano l'efficienza di una query in base al Database considerato? Ho paura che mi toccherà comprare qualche libro di Oracle per capirci qualcosa.

Per quanto riguarda il nuovo esempio di FabryHw.. proprio ora ero rimasto incuriosito dal Partition By, che pensavo sarebbe potuto essere utile, e te me lo hai presentato al volo, grande

[yssj485]

Una domanda diciamo più o meno sulle basi.. approfitto del topic.

Su Oracle, ma credo sia una questione generale, c'è una differenza prestazionale immensa tra un Join, a TRE TABELLE ( molto ricche di record )del tipo

FROM Tabella1 T1, Tabella2 T2, TABELLA 3
WHERE T1.cod = T2.cod AND T2.cod2 = T3.cod2

ed un inner join

FROM Tabella 1 T1
INNER JOIN Tabella2 T2
ON T1.cod = T2.cod
INNER JOIN Tabella3 T3
ON T2.cod2 = T3.cod2

Perchè? Su Oracle, a quanto ho letto, nel primo caso viene eseguito un equijoin sul campo specificato nel where, mentre nel secondo caso viene effettuata chiaramente una inner join.. il risultato è che la prima query è, senza esagerare, centinaia di volte più lenta della seconda, che in alcuni casi è quasi immediata. Che differenza c'è, a basso livello e come operazioni che fa il database? Ne sapete qualcosa?

Grazie

[Kenger]

Visto che un equijoin è solo un sottotipo di inner join mi sembra strano

A meno che in qualche modo non sia stato disabilitato l'ottimizzatore di Oracle e quindi lui prima faccia il prodotto cartesiano e poi controlla i where ma mi sembrerebbe incredibilmente strano, non so neanche se è possibile

Magari a qualcun'altro viene un'idea.

EDIT: Ma dove l'hai letto?

[FabryHw]

Originariamente inviato da yssj485

Una domanda diciamo più o meno sulle basi.. approfitto del topic.

Su Oracle, ma credo sia una questione generale, c'è una differenza prestazionale immensa tra un Join, a TRE TABELLE ( molto ricche di record )del tipo

FROM Tabella1 T1, Tabella2 T2, TABELLA 3
WHERE T1.cod = T2.cod AND T2.cod2 = T3.cod2

ed un inner join

FROM Tabella 1 T1
INNER JOIN Tabella2 T2
ON T1.cod = T2.cod
INNER JOIN Tabella3 T3
ON T2.cod2 = T3.cod2

Perchè? Su Oracle, a quanto ho letto, nel primo caso viene eseguito un equijoin sul campo specificato nel where, mentre nel secondo caso viene effettuata chiaramente una inner join.. il risultato è che la prima query è, senza esagerare, centinaia di volte più lenta della seconda, che in alcuni casi è quasi immediata. Che differenza c'è, a basso livello e come operazioni che fa il database? Ne sapete qualcosa?

Grazie

Come ti hanno già detto tutte le volte che usi in una INNER JOIN solo l'operatore "=" (E nel 99% dei casi probabil. sarà così) stai facendo una EquiJoin.

Vedi : http://it.wikipedia.org/wiki/Join_%28SQL%29

Inoltre la tua prima sintassi non è altro che il VECCHIO metodo per scrivere le INNER JOIN, prima che implementassero delle keyword apposite.
Tra l'altro puoi pure portare nella join condizioni WHERE esterne alla relazione di join tra le tabelle.

Esempio il codice

FROM 
  Tabella1 T1 
INNER JOIN 
  Tabella2 T2 ON 
    T1.id = T2.id 
WHERE 
  T1.cod = 10

potrebbe benissimo essere scritto (questione di gusti) come:

FROM 
  Tabella1 T1 
INNER JOIN 
  Tabella2 T2 ON 
   T1.id = T2.id AND 
   T1.cod = 10

Di conseguenza i 2 codici da te proposti devono essere perfettamente equivalenti (e su Sql Server sarebbe così).
Perché Oracle non esegua il primo codice in modo efficiente non lo so, prova a confrontare i piani di esecuzione e vedi cosa cambia.

[yssj485]

Beh su una guida ad Oracle di html.it, l'elencare le tabelle sul FROM dice che sia trattato come un normale Equijoin. Il fatto che siano così diversi prestazionalmente infatti non mi quadra moltissimo ( parlo di 4 sec contro 430 secondi..).
Provo allora a vedere se da qualche parte riesco ad ottenere qualche informazione in più su come si comporti realmente il DB, soprattutto in casi con join tra tre più o più tabelle.

[shinya]

Originariamente inviato da yssj485

Beh su una guida ad Oracle di html.it, l'elencare le tabelle sul FROM dice che sia trattato come un normale Equijoin. Il fatto che siano così diversi prestazionalmente infatti non mi quadra moltissimo ( parlo di 4 sec contro 430 secondi..).
Provo allora a vedere se da qualche parte riesco ad ottenere qualche informazione in più su come si comporti realmente il DB, soprattutto in casi con join tra tre più o più tabelle.

Incolla l'execution plan.
Hai messo gli indici sulle FK?

[Kenger]

Nel caso non lo sapessi per vedere l'execution plan devi usare il comando

EXPLAIN PLAN FOR

seguito dalla query. Io a fini didattici ho usato solo Oracle 8i e quindi non se con delle versioni meno vetuste ti mostra il piano direttamente. Sulla 8i ti risponde "Spiegato." e poi devi dare un altro comando. Lì ti devi arrangiare :P

Se lo sapevi già +1 :P

[yssj485]

Ok, ci ho provato, ma questo è il risultato.

EXPLAIN PLAN FOR SELECT *
FROM FATTURA F
INNER JOIN ESITO_FATTURA E
ON F.COD_ESIFAT = E.COD_ESIFAT
INNER JOIN ENTE_ESATTORE ES
ON F.COD_ESATTORE = ES.COD_ENTE
Report errori:
Errore SQL: ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace 'CONTAB_DATI'
00604. 00000 - "error occurred at recursive SQL level %s"
*Cause: An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Immagino di non avere qualche permesso o qualcosa del genere(chiaramente è un DB remoto)..