Oracle索引日常脚本

• 查看用户下所有索引

select t.index_owner,
t.TABLE_NAME,
t.INDEX_NAME,
listagg(t.COLUMN_NAME, ',') within group(order by t.COLUMN_POSITION) index_col
from dba_ind_columns t
where t.table_owner = upper('&owner') and t.TABLE_NAME not like 'BIN$%'
group by t.TABLE_NAME, t.index_owner, t.INDEX_NAME
order by t.TABLE_NAME, t.index_owner, t.INDEX_NAME;

• 查看列的区分度

select a.table_name, 
 a.column_name, 
 a.num_distinct, 
 round(a.num_distinct * 100 / b.num_rows) "distinct percent%" 
 from dba_tab_columns a, dba_tables b 
 where a.table_name = b.table_name 
 and a.table_name = '&table_name'; 

• 查看当前索引使用情况

 SELECT p.object_name, p.operation, p.options, COUNT(1) 
 FROM v$sql_plan p, v$sql s 
 WHERE p.object_owner <> 'SYS' 
 AND p.OBJECT_NAME in 
 (select index_name 
 from dba_indexes 
 where table_name = 'S_SHIP_UNIT_LINE') 
 AND p.sql_id = s.sql_id 
 GROUP BY p.object_name, p.operation, p.options 
 ORDER BY 1, 2, 3; 

• 查看失效索引并重建索引

select owner index_owner,
       table_name,
       index_name,
       '普通表无分区' partition_name,
       status,
       'alter index ' || owner || '.' || index_name ||
       ' rebuild parallel 8;' rebuild_index
  from dba_indexes
 where status in ('INVALID', 'UNUSABLE')
union
select a.index_owner,
       b.table_name,
       a.index_name,
       a.partition_name,
       a.status,
       'alter index ' || a.index_owner || '.' || index_name ||
       ' rebuild partition ' || a.partition_name || ' parallel 8;' rebuild_index
  from dba_ind_partitions a, DBA_TAB_PARTITIONS b
 where status in ('INVALID', 'UNUSABLE')
   and a.partition_name = b.partition_name;

• 关闭索引并行

--关闭索引并行
select owner,
       index_name,
       index_type,
       table_owner,
       table_name,
       partitioned,
       degree,
       'alter index ' || table_owner || '.' || index_name || ' noparallel;'
  from dba_indexes
 where degree > '1';
 
 --关闭表并行
 select a.OWNER,
        a.TABLE_NAME,
        a.degree,
        'alter index ' || a.OWNER || '.' || a.TABLE_NAME || ' noparallel;'
   from dba_tables a
  where a.degree > '1';