Oracle Lessons

Oracle Tipi Dati Astratti Lezione 1

In questo documento vedremo come utilizzare i tipi di dato astratti. Verranno trattati temi di amministrazione, di sicurezza e indicizzazione dei tipi di dato astratti, e dei loro attributi. Verrà descritta anche la creazione di metodi e l’impiego di viste e trigger di tipo ISTEAD OF.

Importante.

 Quando è necessario utilizzare i tipi di dati astratti:

 Quando sorge la necessità di modellare e gestire dati la cui struttura e le cui interrelazioni non sono riconducibili direttamente alle strutture tabellari del modello relazionale.

• Nel modello relazionale la modellazione di un oggetto complesso implica una sua suddivisione in un ampio insieme di tuple e la necessità di eseguire un gran numero di join quando si ha la necessità di ricostruirlo. Ad esempio un aeroplano è formato da un gran numero di componenti di base (ali, fusoliera, timone, motori, ecc...) i quali a loro volta sono costruiti a partire da componenti più elementari (tralici, fibre plastiche, parti metalliche) e così via. La modellazione di tali entità con un insieme di tuple rende necessario eseguire un gran numero di join ogni volta che si desidera reperire tutte le informazioni relative a un intero aeroplano.

• Necessità di esprimere proprietà e relazioni dinamiche dei dati e di gestirne l'evoluzione temporale. Si pensi alle difficoltà che si incontrano con il modello E/R e conseguentemente con quello relazionale nella modellazione del tempo e dell'evoluzione dinamica delle entità.

Di contro attualmente:

• Mancanza di un modello dei dati e di un linguaggio di query standard pienamente accettati;

• Mancanza di un linguaggio di query dichiarativo (SQL-like); 

Il modello relazionale ad oggetti

I DBMS relazionali ad oggetti (object-relational) nascono dall’esigenza di assicurare le funzionalità dei RDBMS rispetto alla gestione di dati tradizionali, estendendo il modello dei dati con la possibilità di gestire dati complessi, tipica degli OODBMS.

ORDBMS: caratteristiche generali: 

Vanno incontro alle esigenze attuali di gestire nuovi tipi di dato quali:

• testi, immagini, audio/video, dati geografici, ecc.;

• tipi di dato user-defined;

• tipi collezione;

Metodi per modellare le operazioni sui tipi definiti dall'utente (es. Java, C).

Nuovi modi per modellare le associazioni.

La filosofia per la gestione dei dati è però ancora quella relazionale:

• Tutti gli accessi ai dati avvengono tramite SQL;

• Tutti le entità di interesse sono modellate tramite tabelle.

Oggi quasi tutti i principali produttori di RDBMS (Oracle, Informix, DB2,..) hanno esteso i loro DBMS con caratteristiche object-relational. Tali estensioni presuppongono anche una estensione del linguaggio SQL. Allo stato attuale ogni RDBMS ha un’estensione proprietaria object-relational.

Le estensioni differiscono per:

• Le funzionalità che supportano;

• Il modo di realizzarle;

• Le estensioni apportate al linguaggio SQL.

E questo nonostante SQL-99 che è uno standard creato appositamente.

SQL-99 è un tentativo di standardizzazione dell’estensione object-relational del modello relazionale. Al momento della definizione di SQL-99 i maggiori produttori di RDBMS avevano già la loro versione delle estensioni object-relational. SQL-99 non standardizza tutte le funzionalità object-relational presenti nei DBMS commerciali.

E’ quindi ancora presto per capire quando e in che misura lo standard sarà recepito a livello commerciale. La sensazione è che sarà necessario un ulteriore standard che medi tra tutte le estensioni proprietarie.

Estensione del sistema di tipi. 

In SQL-92 i tipi di un attributo in una relazione possono essere:

• numerici (interi, reali, ecc.);

• carattere (stringhe di lunghezza fissa o variabile, caratteri singoli);

• temporali (date, time, datetime, interval);

• booleani (true, false);

• non strutturati (BYTE, TEXT, BLOB, CLOB);

Per ogni tipo built-in esistono un insieme fisso e predefinito di operazioni che su di esso possono essere eseguite. Questo fatto introduce delle limitazioni rendono spesso difficile la rappresentazione di dati reali.

Per questo motivo è stato introdotto un’estensione del sistema dei tipi che prevede: 

• Tipi semplici;

• Abstract data types;

• User-defined types;

• Tipi riferimento;

• Tipi complessi: tipi record e tipi collezione;

Oracle Tabelle Esterne Appendice

Qualche esempio tratto da esperienza reale.

- quando il campo della tabella è di tipo date il campo del record va definito char e va applicata la giusta formattazzione. Esempio, nel file si ha 20100616 che rappresenta il 6 Giugno 2010 nella definizione del record ho  

data_op char(8) date_format date mask "YYYYMMDD"

- quando si hanno campi di testo con degli spazi ed è necessario rimuoverli si utilizza il comando LRTRIM nella definizione degli access parameters

fields terminated by ";" LRTRIM

- se nel file da caricare si hanno dei numeri formattati con il "." come separatore decimale, esempio 1.23, bisogna impostare il settaggio corretto del territorio, in questo caso è necessario impostare negli access parameters il comando

TERRITORY "AMERICA"

Oracle Tabelle Esterne Lezione 5

Limitazioni, vantaggi e usi possibili delle tabelle esterne.

Le più importanti limitazioni che abbiamo sulle tabelle esterne sono le seguenti:

1. Le tabelle esterne sono soggette a limitazioni che potrebbero renderle inadatte per alcune applicazioni per l’elaborazione di transazioni in linea: se nel corso di una transazione viene modificato il contenuto di una tabella esterna sostituendo il file a cui si riferisce, la transazione potrebbe perdere la sua consistenza;

2. Nelle tabelle esterne non si possono effettuare operazioni di aggiornamento o cancellazione.

3. Più dinamica è l’informazione contenuta in una tabella esterna meno appropriato è il suo uso.

4. Le tabelle esterne non possono essere indicizzate, quindi se il numero dei record presenti è consistente i tempi di accesso all’informazione possono degradare;

5. In una tabella esterna non è possibile specificare dei vincoli. Persino il tentativo di creare un vincolo not null o quello di definizione di chiave esterna avrà esito negativo;

6. Per analizzare una tabella esterna si deve utilizzare il package DBMS_STAT, non è possibile analizzarla con il comando analyze;

Nonostante queste limitazioni, le tabelle esterne mettono a disposizione molte soluzioni utili:

1. possono essere messe in join con altre tabelle esterne o con tabelle standard. Si può anche ricorre ai suggerimenti per costringere lì ottimizzatore a scegliere percorsi di join differenti, e i risultati saranno visibili nei percorsi di esecuzione delle query;

2. come alternativa al caricamento dei dati, le tabelle esterne offrono ai DBA e agli sviluppatori delle applicazioni la possibilità di accedere ai dati senza dover sviluppare programmi di caricamento;

3. dato che i file possono essere modificati a livello di sistema operativo, è possibile sostituire i dati di una tabella molto rapidamente senza doversi preoccupare delle transazioni in sospeso che modificano la tabella;

4. se il file contiene molti record, è possibile frammentarlo su più file, sui quali costruire più tabelle esterne e successivamente un'unica vista in union all, dando così origine ad una vista di partizione tra più file (il cui accesso è più veloce considerando il parallelismo).

5. se il partizionamento in più file di un file contenente molti record segue logiche opportune, si potranno gestire i dati di ogni tabella separatamente, a livello di file system, sostituendone il contenuto in base alle esigenze.

6. considerata la possibilità di eseguire una query su di una tabella esterna, quest’ultima potrà fungere da origine di dati per un comando “insert as select”. Per migliorare ulteriormente le prestazioni dell’operazione “insert as select”, si dovrebbe usare il suggerimento APPEND per forzare gli inserimenti a livello di blocco;

7. quando si specifica il grado di parallelismo per l’operazione “insert as select”, Oracle avvia più driver di accesso ORACLE_LOADER, per elaborare i dati in parallelo;

8. la disattivazione di “badfile” (con “nobadfile”) elimina i costi associati alla creazione di file e alla gestione del contesto della riga originale.

9. durante l’operazione “insert as select” si possono eseguire funzioni sui dati mentre questi vengono elaborati. Queste funzioni possono essere inserite nella definizione della tabella esterna. Questa capacità sottolinea un vantaggio importante garantito dalle tabelle esterne, ossia la possibilità di centralizzare i requisiti di rappresentazione e elaborazione dei dati, creando così le routine di conversione nelle definizioni delle tabelle.

10. nelle query le tabelle esterne consento di selezionare gruppi di dati specifici tramite la clausola “load when”;

11. la possibilità di accedere in modo limitato ai dati consente di definire delle regole di sicurezza anche per i dati esterni (ad esempio si potrebbe pensare di tenere dei dati riservati fuori dal database in una directory sicura dove solo alcuni utenti hanno il diritto di accesso in lettura);

Se nell’architettura del database si utilizzano tabelle esterne, ci si dovrà accertare che i piani di backup e recupero, tengano conto anche di questi file. Se i file esterni cambiano più rapidamente di quelli del database, potrebbe essere necessario eseguire dei backup con maggiore frequenza per sfruttare le capacità di completo recupero da parte di oracle.

Oracle Tabelle Esterne Lezione 4

Modifica di tabelle esterne.

E’ possibile modificare la definizione di una tabella esterna per cambiare il modo in cui Oracle interpreta il file piatto. Le opzioni disponibili sono descritte in dettaglio nei paragrafi seguenti.

 Parametri di accesso

E’ possibile modificare i parametri di accesso senza dover eliminare e ricreare la definizione della tabella esterna, conservando in tal modo concessioni e privilegi, definizioni di file e così via. Per esempio ecco come aumentare il numero di record da saltare nella tabella BIBLIOTECA_EXT_4:

alter table BIBLIOTECA_EXT_4

access parameters (records delimited by newline

skip 10

fields terminated by “;”

(Titolo char(100),

Editore char(20),

NomeCategoria char(20),

Classificazione char(2)

)

);

 Add column

E’ possibile utilizzare la clausola “add columns” del comando “alter table” per aggiungere una colonna alla tabella esterna ricorrendo alla stessa sintassi impiegata per le tabelle standard.

 Default directory

E’ possibile utilizzare la clausola “default directory” del comando “alter table” per cambiare la directory predefinita per i file esterni cui accede la tabella. La directory deve essere creata con il comando “create directory”.

 Drop Column

E’ possibile utilizzare la clausola “drop column” del comando “alter table” per eliminare una colonna dalla tabella esterna ricorrendo alla stessa sintassi impiegata per le tabelle standard. I dati del file rimangono immutati.

 Location

E’ possibile cambiare i file cui accede la tabella esterna con la clausola “location” del comando “alter table”. Si può utilizzare questa opzione per aggiungere nuovi file all’elenco o cambiare l’ordine in cui la tabella esterna accede ai file.

 Modify column

E’ possibile utilizzare la clausola “modify column” del comando “alter table” per modificare una colonna della tabella esterna ricorrendo alla stessa sintassi impiegata per le tabelle standard.

 Parallel

E’ possibile utilizzare la clausola “parallel” del comando “alter table” per cambiare il gradi di parallelismo per la tabella esterna ricorrendo alla stessa sintassi impiegata per le tabelle standard.

 Project Column

La clausola “project column” del comando “alter table” comunica al driver di accesso come convalidare le righe nelle query successive. Se si utilizza l’opzione “project column referenced”, il driver di accesso elabora solo le colonne selezionate dalla query. Se poi si interroga un gruppo di colonne diverso della tabella esterna, i risultati potrebbero non essere coerenti con quelli della prima query. Se si usa l’opzione “project column all”, il driver di accesso elabora tutte le colonne definite sulla tabella esterna, producendo un gruppo di risultati coerente. L’opzione predefinita è “project column referenced”.

 Reject Limit

E’ possibile utilizzare la clausola “reject limit” del comando “alter table” per cambiare il numero consentito di righe rifiutate per tabella esterna. Ecco un esempio:

alter table table_name reject limit 5;

 Rename to

E’ possibile utilizzare la clausola “rename to” del comando “alter table” per cambiare il nome della tabella esterna ricorrendo alla stessa sintassi impiegata per le tabelle standard. Ecco un esempio:

alter table table_name rename to new_table_name;

Oracle Tabelle Esterne Lezione 3

Opzioni per la creazione di tabelle esterne.

Nella clausola organization external sono contenute quattro sottoclassi principali:

• Type;

• Default directory;

• Access parameters;

• Location;

Quando si crea una tabella esterna, si possono utilizzare queste clausole per personalizzare il modo in cui Oracle visualizza i dati esterni.

Type e default directory

La sintassi del componente type è :

([ type tipo_driver_accesso ] proprietà_dati_esterni )

[reject limit {intero | unlimited }]

Per le tabelle esterne il driver di accesso è l’API utilizzato per trasformare i dati esterni, si utilizzi il componente type ORACLE_LOADER, come mostrato negli esempi precedenti, o ORACLE_DATAPUMP se si usa il Data Pump.

Il driver ORACLE_DATAPUMP consente di scaricare i dati in una tabella esterna e di ricaricarli in un database di Oracle. E’ necessario specificare il driver di accesso ORACLE_DATAPUMP se si utilizza la clausola as subquery per scaricare i dati da un database e ricaricarli in un altro. Il driver di accesso ORACLE_LOADER è quello di default.

NOTA: Dato che il driver di accesso fa parte del software di oracle, solamente ai file che sono accessibili dal database si potrà accedere come tabelle esterne. I file cui gli utenti Oracle non possono accedere non potranno essere utilizzati come tabelle esterne.

Dopo la dichiarazione di type è possibile impostare un valore “reject limit”. Per default nessuna riga può essere rifiutata; qualsiasi tipo di problema con una riga qualunque comporterà la restituzione da parte dell’istruzione select di un errore. Si crei un’altra copia dei dati di BIBLIOTECA in un file separato, ma questa volta si dovranno lasciare nel file le linee aggiuntive che SQL*Plus inserisce durante l’operazione di spool. Successivamente si crei una nuova tabella che fa riferimento a questo file di spool, comunicando a Oracle di saltare il primo record (skip 1) e di consentire un altro errore (reject limit 1). Ciò spiega il simbolo “/” nella prima linea, e “SQL>spool off” nell’ultima linea:

create table BIBLIOTECA_EXT_2

(Titolo varchar2(100),

Editore varchar2(20),

NomeCategoria varchar2(20),

Classificazione varchar2(2))

organization external

(type ORACLE_LOADER

Default directory LIBRO_DIR

Access parameters (record delimited by newline

Skip 1

fields terminated by “;”

(Titolo char(100),

Editore char(20),

NomeCategoria char(20),

Classificazione char(2)

)

)

Location (‘bookshelf_dump.lst’)

)

Reject limit 1

;

La clausola default directory specifica quale oggetto di directory utilizzare per tutti quei file di dati che non specificano un’altra directory. Se si usano più file esterni collocati in più directory, si potrà chiamare una di queste come directory predefinita e specificare le altre con nomi di directory nella clausola location. Nella clausola location si dovranno usare nomi di oggetti di directory (come LIBRO_DIR) e non il percorso della directory completo.

Parametri di Accesso

La clausola “access parameters” comunica a Oracle come associare le righe contenute nel file alle righe contenute nella tabella.

Per prima cosa si comunica a Oracle come creare un records, se la sua lunghezza è fissa o variabile, e poi come sono delimitate le righe.

Se in una linea singola ci fossero più righe, si potrebbe usare una stringa di caratteri come separatore per le varie righe. Visto poi che i dati esterni potrebbero arrivare da un database esterno non Oracle, vengono supportati più set di caratteri e più dimensioni di stringa.

Come accadeva con SQL*Loader, esiste la possibilità di specificare una clausola “when” per limitarla ai soli libri contenuti nella categoria ILLUSRAGAZZI.

create table BIBLIOTECA_EXT_3

(Titolo varchar2(100),

Editore varchar2(20),

NomeCategoria varchar2(20),

Classificazione varchar2(2))

organization external

(type ORACLE_LOADER

Default directory LIBRO_DIR

Access parameters (record delimited by newline

Load when NomeCategoria = ‘ILLUSRAGAZZI’

Skip 1

fields terminated by “;”

(Titolo char(100),

Editore char(20),

NomeCategoria char(20),

Classificazione char(2)

)

)

Location (‘bookshelf_dump_2.lst’)

)

Reject limit 1

;

La tabella BIBLIOTECA_EXT_3 accede allo stesso file di BIBLIOTECA_EXT_2, tuttavia mostra solo i record per la categoria ILLUSRAGAZZI per via della sua clausola load when.

Come accadeva con il SQL*Loader, esiste la possibilità di creare un file di log, un file non valido e un file di scarto. Le righe che non rispettano le condizione “load when” verranno scritte nel file di scarto. Le righe che non rispettano la condizione di “access parameters” verranno scritte nel file non valido, mentre i dettagli di caricamento verranno scritte nel file di log. Per tutti e tre i tipi di file, è possibile specificare un oggetto di directory insieme al nome file, in modo che sia possibile scrivere l’output in una directory diversa da quella del file di dati di input.

E’ possibile specificare “nodiscardfile”, “nobadfile” e “nologfile” per impedire la creazione di questi file. 

Occorre utilizzare i nomi di oggetti di directory (come LIBRO_DIR) quando si specificano le posizioni dei file di scarto, dei file non validi e dei file di log. 

Se non si specificano delle posizioni per i file di log, i file non validi e i file di scarto, Oracle li creerà nella directory predefinita con i nomi generati dal sistema.

Nella clausola “access parameters” si dovranno specificare anche definizioni e delimitatori di campo, come:

fields terminated by “;”

(Titolo char(100),

Editore char(20),

NomeCategoria char(20),

Classificazione char(2)

)

Per impostare i valori di colonna NULL si può ricorrere alla clausola “missing field values are null”, tuttavia occorre prestare molta attenzione quando si usa questa opzione perché possono essere trattate anche righe che sarebbero da scartare per non conformità di definizione, nel caso not null (ad esempio righe del tipo SQL>spool off perché ha null alcune colonne).

Access parameters (record delimited by newline

Load when NomeCategoria = ‘ILLUSRAGAZZI’

Skip 1

fields terminated by “;”

missing field values are null

Nella maggior parte dei casi però l’integrità dei dati verrà garantita meglio costringendo le righe a fallire (in file non validi o di scarto) e valutando le righe fallite separatamente dai file generali.

Location

Nella clausola “location” si specificano i file di dati da utilizzare come dati di origine per la tabella. Nella clausola “location” è possibile nominare più file se si trovano tutti in oggetti directory per i quali l’utente possiede il privilegio READ. L’esempio seguente associa due file di spool di BIBLIOTECA separati per illustrare la capacità di combinare più file in un'unica tabella esterna:

create table BIBLIOTECA_EXT_4

(Titolo varchar2(100),

Editore varchar2(20),

NomeCategoria varchar2(20),

Classificazione varchar2(2))

organization external

(type ORACLE_LOADER

Default directory LIBRO_DIR

Access parameters (record delimited by newline

Skip 1

fields terminated by “;”

(Titolo char(100),

Editore char(20),

NomeCategoria char(20),

Classificazione char(2)

)

)

Location (‘bookshelf_dump_2.lst’,’bookshelf_dump.lst’)

)

Reject limit 1

;

L’ordine dei file è importante:

‘skip 1’ si riferisce al primo file e non al secondo.