哪些表的索引個數過多：
prompt
目前使用者下,哪些表的索引個數字超過5個的
select table_name, count(*) cnt
from user_indexes
group by table_name
having count(*) >= 5
order by cnt desc ;
哪些表的外鍵未建索引：
prompt
将外鍵未建索引的情況列出
select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name);
哪些表組合索引列過多
prompt
目前使用者下，哪些組合索引組合列超過4個的
select table_name, index_name, count(*)
from user_ind_columns
group table_name, index_name
having count(*) >= 4
order by count(*) desc;
哪些大表未建任何索引
--針對普通表(大于2GB的表未建任何索引)
select segment_name, bytes/1024/1024/1024 "GB", blocks, tablespace_name
from user_segments
where segment_type = 'TABLE'
and segment_name not in (select table_name from user_indexes)
and bytes / 1024 / 1024 / 1024 >= 2
order by GB desc;
--針對分區表(大于2GB的分區表未建任何索引)
--無論是建了局部索引還是全局索引，在user_indexes都可以查到，隻是status不一樣。
select segment_name, sum(bytes)/1024/1024/1024 "GB", sum(blocks)
from user_segments
where segment_type = 'TABLE PARTITION'
and segment_name not in (select table_name from user_indexes)
group by segment_name
having sum(bytes)/1024/1024/1024>=2
order by GB desc;
--注：無論是建了局部索引還是全局索引，在user_indexes都可以查到，隻是status不一樣。
哪些聚索引列合因子差
prompt
目前使用者下，哪些索引的聚合因子特别大。
select a.table_name,
a.index_name,
a.blevel,
a.leaf_blocks,
b.num_rows,
b.blocks,
a.clustering_factor,
trunc(a.clustering_factor / b.num_rows,2) cluster_rate
from user_indexes a, user_tables b
where a.table_name = b.table_name
and a.clustering_factor is not null
and a.clustering_factor / b.num_rows>0.9
order by cluster_rate desc ;
哪些類型的索引已失效
prompt
失效-普通索引
select t.index_name,
t.table_name,
blevel,
t.num_rows,
t.leaf_blocks,
t.distinct_keys
from user_indexes t
where status = 'UNUSABLE' ;
prompt
失效-分區索引
select t1.blevel,
t1.leaf_blocks,
t1.INDEX_NAME,
t2.table_name,
t1.PARTITION_NAME,
t1.STATUS
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and t1.STATUS = 'UNUSABLE';
哪些索引單列組合有叉
prompt
目前使用者下，哪些表的組合索引與單列索引存在交叉的情況。
select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
from user_ind_columns
group by table_name
having count(distinct(column_name)) / count(*) < 1
order by cross_idx_rate desc;
---------例子
drop table t purge;
drop table t1 purge;
create table t1 as select * from dba_objects where object_id is not null;
create index idx_t1_objid_owner on t1(object_id ,owner);
create index idx_t1_object_id on t1(object_id );
drop table t2 purge;
create table t2 as select * from dba_objects where object_id is not null;
create index idx_t2_objid_owner on t2(object_id,owner);
create index idx_t2_object_id on t2(object_id);
create index idx_t2_owner on t2(owner);
drop table t3 purge;
create table t3 as select * from dba_objects where object_id is not null;
create index idx_t3_objid_owner on t3(object_id,owner);
create index idx_t3_owner_objid on t3(owner,object_id);
create index idx_t3_object_id on t3(object_id);
create index idx_t3_owner on t3(owner);
---執行如下語句，發現目前使用者下，T3,T2,T1表存在索引單列組合有交叉，最嚴重的是T3
select table_name, trunc(count(distinct(column_name)) / count(*),2) cross_idx_rate
from user_ind_columns
group by table_name
having count(distinct(column_name)) / count(*) < 1
order by cross_idx_rate ;
TABLE_NAME CROSS_IDX_RATE
------------------------------ --------------
T3 .33
T2 .5
T1 .66
哪些索引的高度比較高
prompt
目前使用者下，哪些索引的高度比較高，大于5層(LEVEL=4)
select table_name,
index_name,
blevel,
leaf_blocks,
num_rows,
last_analyzed,
degree,
status
from user_indexes
where blevel>=4;
哪些索引建後從未使用
prompt
目前使用者下，哪些索引最近30天内從未被使用過。
set linesize 166
col INDEX_NAME for a10
col TABLE_NAME for a10
col MONITORING for a10
col USED for a10
col START_MONITORING for a25
col END_MONITORING for a25
--以下判斷在最近30天内未被使用過的索引有哪些
select *
from v$object_usage
where USED = 'NO'
and START_MONITORING <= sysdate - 30
and END_MONITORING is not null;
--注，之前需有對索引進行監控，如
alter index idx_t_id monitoring usage;
---如果取消監控就是
alter index idx_t_id nomonitoring usage;
哪些索引設定并行屬性
prompt
目前使用者下，哪些索引被設定了并行。
select table_name,
index_name,
blevel,
leaf_blocks,
num_rows,
last_analyzed,
degree,
status
from user_indexes
where degree>1;
哪些索引統計資訊太舊
--普通索引(從未收集過統計資訊或者是最近10天内未收集過統計資訊的表)
select index_name, table_name, last_analyzed, num_rows, temporary, status
from user_indexes
where status <> 'N/A'
and (last_analyzed is null or last_analyzed < sysdate - 10);
--分區索引(從未收集過統計資訊或者是最近10天内未收集過統計資訊的分區)
select t2.table_name,
t1.INDEX_NAME,
t1.PARTITION_NAME,
t1.last_analyzed,
t1.blevel,
t1.leaf_blocks,
t1.STATUS
from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
and (t1.last_analyzed is null or t1.last_analyzed < sysdate - 10);
哪些主外鍵限制失效了
prompt
目前使用者下，哪些外鍵的限制失效了。
SELECT TABLE_NAME,
CONSTRAINT_NAME,
STATUS,
CONSTRAINT_TYPE,
R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE STATUS='DISABLED';
--試驗
drop table t_p cascade constraints purge;
drop table t_c cascade constraints purge;
CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_P ADD CONSTRAINT T_P_ID_PK PRIMARY KEY (ID);
CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(30));
ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);
set autotrace off
INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;
INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME FROM ALL_OBJECTS;
COMMIT;
ALTER TABLE T_C DISABLE CONSTRAINT FK_T_C;
CREATE INDEX IND_T_C_FID ON T_C (FID);
标簽：語句,name,t1,索引,user,Oracle,table,select
來源： https://www.cnblogs.com/sunliyuan/p/12366336.html