Oracle Indici

Indici

Elenco Indici

set pagesize 200
set linesize 140
col column_name for a30
col pos for 99
col table_name for a30
col index_name for a35
select table_owner || '.' || table_name table_name, index_owner || '.' || index_name index_name, column_name, column_position pos
 from dba_ind_columns
 where table_name=upper('&&1')
 order by 1,2,4;

set pagesize 200
set linesize 150
set autotrace off
col column_name for a30
col pos for 99
col table_name for a38
col index_name for a43
col tbs for a20
select c.table_owner || '.' || c.table_name table_name, c.index_owner || '.' || c.index_name || ' ' || i.generated index_name,
       substr(i.uniqueness,1,1) U, c.column_name, c.column_position pos, i.tablespace_name tbs
from dba_ind_columns c
 inner join dba_indexes i on 
 (c.table_owner=i.table_owner and c.table_name=i.table_name and
  c.index_owner=i.owner and c.index_name=i.index_name)
where c.table_name=upper('&&1')
order by 1,2,4;

col owner for a40
col tablespace_name for a40
select owner,tablespace_name,bytes/(1024*1024) mb from dba_segments where segment_name=upper('&&1');

Indici con proprietario diverso

select owner,index_name,table_owner,table_name from dba_indexes where table_owner!=owner;

indici nosegment (virtual indexes)

create index cust_idx1 on cust(first_name) nosegment;

Per fare in modo che l'optimizer valuti correttamente se usare o meno l'indice, si possono calcolare le statistiche:

EXEC DBMS_STATS.gather_index_stats(ownname=>'USER', indname =>'CUST_IDX1');

Per vedere se usa l'indice, anche se (di fatto) non esiste:

set autotrace traceonly explain;
alter session set "_use_nosegment_indexes"=true;
select first_name from cust where first_name = 'BUBBA';

Se viene usato, si può creare un "vero" indice. Per trasformarlo in un indice "vero":

create index cust_idx1 on cust(first_name);