INDEX e CONSTRAINT

Gli Indici servono per ridurre il tempo di accesso alle righe delle tabelle.

Se una query restituisce soltanto una riga piuttosto che analizzare l'intera tabella, riga per riga (full table scan), un indice fornisce un puntatore all'esatta posizione della stessa. Naturalmente anche l'indice deve essere ricercato ma è più veloce che analizzare l'intera tabella.
Gli Indici possono essere creati su Tabelle e Cluster.

Gli Indici sono di due tipi: UNIQUE e NONUNIQUE

I vincoli o Constraint sono invece: PRIMARY, UNIQUE e FOREIGN KEY

Un Indice di tipo UNIQUE è creato automaticamente quando è definita su una o più colonne una Primary key (pk) e una Unique key (uk) e conserva lo stesso nome della constraint.

SQL> create table pdbtsadm.dept

         (deptno number(2), 

            dept_name varchar2(100),

            constraint deptno_pk  primary key (deptno));

SQL> select * from dba_indexes;

Dal risultato della query si evince che l'indice si chiama deptno_pk come la pk.

Perchè?

Ricordiamo che una pk o una uk è un attributo che identifica univocamente un record.

Quindi se devo inserire dei nuovi record su una tabella che ha una colonna (o più colonne) con un vincolo pk o uk, Oracle deve verificare che non esiste già una riga con lo stesso valore di pk o uk effettuando la scansione di tutta la tabella, controllando ogni riga. La scansione dell’intera tabella può essere accettabile se ci sono poche righe, ma per una tabella con migliaia di righe non è praticabile.

Invece un indice dà accesso ai valori chiave, in modo da verificare quasi istantaneamente l'esistenza di quel valore da inserire. Se non esiste il valore allora si può effettuare la insert.

A differenza di una pk constraint la(e) colonna(e) di una uk constraint può contenere vaolre NULL.

Foreign key (fk) constraints relaziona una colonna della tabella figlio con una pk o uk constraint della tabella padre. Pertanto una fk non richiede necessariamente la definizione di un indice ma quest'ultimo deve esistere sulla tabella padre. Quando viene eseguita una INSERT nella tabella figlio, Oracle verifica se esiste il corrispondente indice nella tabella padre e in caso affermativo permette l'inserimento.
Conviene in generale creare un indice per le colonne fk nella tabella figlio per migliorare le performance; infatti se viene eseguita un DELETE di alcune righe nella tabella padre, Oracle può utilizzare gli indici per cercare nella tabella figlio le corrispondenti righe referenziate.

In definitiva l'indice ha due funzioni principali: applicare i vincoli di pk e uk e migliorare le performance.

Un indice è una lista di valori chiave strutturati per rendere la ricerca molto efficiente e ogni valore chiave è un puntatore ad una riga delle tabella.
Inoltre il risultato di una SELECT è ordinata in automatico secondo le colonne indice.

TIPI DI INDICI
I tipi di indice di interesse sono B*TREE (è quello di default) e BITMAP. Entrambe possono essere unique e nonunique (default). Un indice Unique non permette di inserire due record che hanno lo stesso valore chiave.
Un indice B*TREE (B = balanced) è una struttura ad albero in cui la radice (root) punta a molti nodi del secondo livello (Branch blocks) che a sua volta punta ad altri nodi foglie (Laef nodes) e così via. La profondità dell'albero aumenta con il numero di righe della tabella o con la lunghezza del valore della chiave indice. I nodi foglie contengono le Index Entry che contengono un puntatore alla localizzazione fisica della riga della tabella. Se si utilizza la WHERE condition di uguaglianza sulla colonna di pk, Oracle ricerca dalla radice alla foglia contenente il valore chiave desiderato e poi usa il puntatore per cercare la riga. Se invece si utilizza una WHERE condition contenente LIKE, BETWENN, > o < allora Oracle naviga dalla radice alle foglie per cecare il primo valore e poi naviga tra le foglie per cercare i restanti valori. Il puntatore alle righe è il ROWID che è una pseudocolonna di Oracle che contiene l'indirizzo fisico della riga ed è univoca per ogni riga di ogni tabella di un intero database.
Il tipo di dato ROWID è memorizzato come una stringa esadecimale all'interno di un datafile

select rowid, a.* from  employees a;
Ad esempio
rowid = AAARAgAAFAAAABUAAA

AAARAg         Object number

AAF                 Relative file number

AAAABU        The data block number 

AAA                The row number

L'indice di tipo BITMAP utilizza una stringa di bit per localizzare velocemente le righe.
E' utilizzato in colonne che hanno una bassa cardinalità, come una colonna contenente il sesso (M o F) o se il numero delle righe è alto o se la colonna è usata in operazioni booleane (and, not, or). Ogni bit corrisponde ad una singola riga nella tabella. Rispetto a B*Tree include il valore NULL.

Si usa per  i campi di tipo "flag", che hanno un dominio di 2 o 3 valori, tipo i classici campi come SESSO, che vale M o F oppure se hai il flag SEGNALATO "S" o "N"..

Per un esempio andare al link seguente:http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm#i36238

CREATE [UNIQUE | BITMAP] INDEX  [ schema.]<nome_indice>  ON [schema.]<nome_tabella> (colonna1 [, colonna2…] );

L'indice di default è nonunique di tipo B*Tree.

Se si vuole creare un indice ma non assegnargli un segment usare la clausola UNUSABLE.

SQL > create index DEPT_NAME_IDX on dept(dept_name) UNUSABLE;

L'indice è presente nella query

SQL> select * from user_indexes;

ma non nei segmenti

SQL> select * from user_segments where segment_name  like 'DEPT%';

Usando la clausola rebuild si assegna un segment all'indice.

SQL> alter index DEPT_NAME_IDX REBUILD;

SQL> select * from user_segments where segment_name like 'DEPT%';

Cancellare un indice

SQL> DROP INDEX [ schema.]<nome_indice>;

Visualizzare gli Indici 

select index_name,index_type,uniqueness
from user_indexes
where table_name='EMPS';

Individuare gli indici associate alle colonne di una tabella

select index_name, column_name

 from DBA_IND_COLUMNS

where table_owner = 'HR'

and table_name = 'EMPS'

order by index_name , column_name

L' utilizzo dell'indice conviene se:
- la query estare meno del 2% - 4% delle righe della tabella altrimenti un full table scan è più veloce. Infatti gli indici B*Tree sono spesso inutili in un ambiente Data Warehouse.
- la colonna contiene un ampio range di valori
- la colonna contiene molti valori a NULL
- se la colonna o le colonne in generale sono usate nella clausola WHERE o nella condizione JOIN
- se la tabella non è frequentemente soggetta ad UPDATE perchè ad ogni DML della tabella corrisponde anche un aggiornamento degli indici

Se nella where condition c'è la clausola NULL, non è utilizzato l'indice ma và in full table scan.
E s e m p i o
select * from  employees where LAST_NAME is null;

Estarre DDL dell'index:

SQL> select dbms_metadata.get_ddl('INDEX','<nome_index>','<schema>' ) from dual;

Per aggiungere una constraint ad una tabella già esistente eseguire i seguenti comandi.

alter table <schema>.<nome_tabella> add constraint <colonna_pk> primary key (<colonna>);

alter table <schema>.<nome_tabella> add constraint <colonna_uk> unique (<colonna>);

alter table <schema>.<nome_tabella> add constraint <colonna_fk> foreign key (<colonna/e>) references <nome_tabella>(<colonna/e>);

Quando viene eseguito il comando DROP TABLE vengono "droppate" anche gli indici e le costraints definite in quella tabella. 

Se un indice è creato implicitamente da Oracle dalla creazione di una pk o uk costraint, allora se si "droppano" le constraint viene droppato anche l'indice.

Se invece l'indice è stato creato esplicitamente e successivamente alle constraint, allora se le constraints sono droppate l'indice sopravvive.