Oracle自己写的找出索引体积比表大的索引

Oracle自己写的找出索引体积比表大的索引

排除掉默认的用户：

set line 500
col index_name for a35
col table_name for a35
with d as
 (select di.owner, di.index_name, ds.bytes idx_mb, di.table_name
    from dba_segments ds, dba_indexes di
   where ds.segment_name = di.index_name
     and ds.owner = di.owner
     and ds.owner not in ('ANONYMOUS',
                          'ANYNAME',
                          'APEX_030200',
                          'AURORA$JIS$UTILITY$',
                          'AURORA$ORB$UNAUTHENTICATED',
                          'APPQOSSYS',
                          'CTXSYS',
                          'DBSNMP',
                          'DIP',
                          'DMSYS',
                          'DVF',
                          'DVSYS',
                          'EXFSYS',
                          'FLOWS_FILES',
                          'LBACSYS',
                          'MDDATA',
                          'MDSYS',
                          'MGMT_VIEW',
                          'OAS_PUBLIC',
                          'ODM',
                          'ODM_MTR',
                          'ORACLE_OCM',
                          'ORDPLUGINS',
                          'ORDSYS',
                          'OSE$HTTP$ADMIN',
                          'OUTLN',
                          'OLAPSYS',
                          'PERFSTAT',
                          'REPADMIN',
                          'RMAN',
                          'SCOTT',
                          'SI_INFORMTN_SCHEMA',
                          'SYS',
                          'SYSMAN',
                          'SYSTEM',
                          'SPATIAL_CSW_ADMIN_USR',
                          'SPATIAL_WFS_ADMIN_USR',
                          'TRACESVR',
                          'TSMSYS',
                          'WEBSYS',
                          'WK_TEST',
                          'WKPROXY',
                          'WKSYS',
                          'WKSYS',
                          'WKUSER',
                          'WMSYS',
                          'XDB',
                          'XS$NULL',
                          'ORDDATA',
                          'OWBSYS',
                          'OGG',
                          'OWBSYS_AUDIT',
                          'IX',
                          'SH',
                          'BI',
                          'OE',
                          'APEX_PUBLIC_USER',
                          'HR',
                          'PM',
                          'BJHL',
                          'GSMCATUSER',
                          'REMOTE_SCHEDULER_AGENT',
                          'SYSRAC',
                          'SYSBACKUP',
                          'GSMADMIN_INTERNAL',
                          'SYS$UMF',
                          'GSMUSER',
                          'SYSDG',
                          'AUDSYS',
                          'OJVMSYS',
                          'APEX_050000',
                          'GGSYS',
                          'SYSKM',
                          'DBSFWUSER')),
t as
 (select owner, segment_name, sum(bytes) bytes
    from dba_segments
   where owner not in ('ANONYMOUS',
                       'ANYNAME',
                       'APEX_030200',
                       'AURORA$JIS$UTILITY$',
                       'AURORA$ORB$UNAUTHENTICATED',
                       'APPQOSSYS',
                       'CTXSYS',
                       'DBSNMP',
                       'DIP',
                       'DMSYS',
                       'DVF',
                       'DVSYS',
                       'EXFSYS',
                       'FLOWS_FILES',
                       'LBACSYS',
                       'MDDATA',
                       'MDSYS',
                       'MGMT_VIEW',
                       'OAS_PUBLIC',
                       'ODM',
                       'ODM_MTR',
                       'ORACLE_OCM',
                       'ORDPLUGINS',
                       'ORDSYS',
                       'OSE$HTTP$ADMIN',
                       'OUTLN',
                       'OLAPSYS',
                       'PERFSTAT',
                       'REPADMIN',
                       'RMAN',
                       'SCOTT',
                       'SI_INFORMTN_SCHEMA',
                       'SYS',
                       'SYSMAN',
                       'SYSTEM',
                       'SPATIAL_CSW_ADMIN_USR',
                       'SPATIAL_WFS_ADMIN_USR',
                       'TRACESVR',
                       'TSMSYS',
                       'WEBSYS',
                       'WK_TEST',
                       'WKPROXY',
                       'WKSYS',
                       'WKSYS',
                       'WKUSER',
                       'WMSYS',
                       'XDB',
                       'XS$NULL',
                       'ORDDATA',
                       'OWBSYS',
                       'OGG',
                       'OWBSYS_AUDIT',
                       'IX',
                       'SH',
                       'BI',
                       'OE',
                       'APEX_PUBLIC_USER',
                       'HR',
                       'PM',
                       'BJHL',
                       'GSMCATUSER',
                       'REMOTE_SCHEDULER_AGENT',
                       'SYSRAC',
                       'SYSBACKUP',
                       'GSMADMIN_INTERNAL',
                       'SYS$UMF',
                       'GSMUSER',
                       'SYSDG',
                       'AUDSYS',
                       'OJVMSYS',
                       'APEX_050000',
                       'GGSYS',
                       'SYSKM',
                       'DBSFWUSER')
   group by owner, segment_name)
select d.owner,
       d.index_name,
       d.idx_mb / 1024 / 1024 idx_mb,
       d.table_name,
       t.bytes / 1024 / 1024 tab_mb
  from d, t
 where d.table_name = t.segment_name
   and d.idx_mb > t.bytes
   and d.owner = t.owner;

我在我管理的数据中发现，挺多的索引比表大的情况。

后续按照计划重建后，发现还是部分存在。

这些索引都是将表的全部字段建成的复合索引，真奇葩。

不然至少也是90%的字段建成的符合索引，哎。