Quelques Select sur les vues du dictionnaire de données relatives aux index :

Index par type

select INDEX_TYPE, count(*)
from user_indexes
group by INDEX_TYPE;

select TABLE_TYPE, count(*)
 from user_indexes
 group by TABLE_TYPE;

Index : Infos générales

select LAST_ANALYZED, 
       Index_name, 
       NUM_ROWS , 
       SAMPLE_SIZE, 
       BLEVEL, 
       LEAF_BLOCKS, 
       DISTINCT_KEYS, 
       AVG_LEAF_BLOCKS_PER_KEY, 
       AVA_BLOCKS_PER_KEY
from   user_indexes
order  by 1,3;

Taille des Objets du user

SELECT sum(bytes/1024/1024) M,
       segment_type
FROM   user_extents
GROUP BY segment_type

Taille des Objets du user par type

SELECT sum(bytes)/1024/1024 Megs,
 segment_type,
 segment_name
FROM user_extents
GROUP BY segment_name, segment_type
order by 1;

Ratio de colonne indexé Par table et par index.

select ind.Table_NAME,
 ind.INDEX_NAME,
 max(ind.COLUMN_POSITION) max_ind_col,
 (select max(COLUMN_ID)
 from user_tab_columns
 where table_NAME = ind.table_NAME) max_tab_col,
 CEIL( max(ind.COLUMN_POSITION) /(select max(COLUMN_ID) from user_tab_columns
 where table_NAME = ind.table_NAME)
 *100) "Ratio%"
from user_ind_COLUMNS ind
group by ind.INDEX_NAME, ind.TABLE_NAME
order by 5;

ALL_IND_EXPRESSIONS

Ratio du nombre de colonne indexé sur une table

select ind.Table_NAME,
 (select max(COLUMN_ID) from user_tab_columns where table_NAME = ind.table_NAME) max_tab_col,
 count(*) max_ind_col,
 CEIL( count(*) /(select max(COLUMN_ID) from user_tab_columns
 where table_NAME = ind.table_NAME)
 *100) "Ratio%"
from user_ind_COLUMNS ind
group by ind.TABLE_NAME
order by 4;

Nombre d’index par table

select count(*), TABLE_NAME
from user_indexes
group by TABLE_NAME
order by 1;

SELECT table_name,
(select sum(bytes)/1024/1024 Megs FROM user_extents where segment_name=table_name)
from user_tables
/

SELECT ind.table_name,
ind.index_name,
(select sum(user_extents.bytes)/1024/1024 Megs FROM user_extents where segment_name=ind.table_name),
(select sum(user_extents.bytes)/1024/1024 Megs FROM user_extents where segment_name=ind.index_name)
from user_indexes ind
group by ind.table_name, ind.index_name

set linesize 132
set pagesize 132
column INDEX_OWNER for a12

Pour tout les schémas :

select INDEX_OWNER,
ind.Table_NAME,
(select max(COLUMN_ID) from dba_tab_columns where table_NAME = ind.table_NAME and OWNER =ind.INDEX_OWNER ) max_tab_col,
count(*) max_ind_col ,
CEIL( count(*) /(select max(COLUMN_ID) from dba_tab_columns
where table_NAME = ind.table_NAME and owner=ind.INDEX_OWNER)
*100) « Ratio% »
from dba_ind_COLUMNS ind
where ind.INDEX_OWNER not in (‘SYS’, ‘SYSTEM’, ‘XDB’ ,’WMSYS’, ‘PERFSTAT’, ‘OUTLN’ , ‘DBSNMP’ ,’ORAPA13′, ‘PTX00XXXX’)
group by INDEX_OWNER, ind.TABLE_NAME
order by 5

gavini.fr

Index Oracle