ITPub博客

首页 > 数据库 > Oracle > 【TUNE_ORACLE】列出SQL谓词中需要创建索引的列SQL参考

【TUNE_ORACLE】列出SQL谓词中需要创建索引的列SQL参考

原创 Oracle 作者:Attack_on_Jager 时间:2021-08-06 10:45:52 0 删除 编辑

实验环境

搭建平台:VMware Workstation

OS:RHEL 6.10

Grid&DB:Oracle 11.2.0.4


SQL参考

--******该脚本依赖统计信息!!必须先收集统计信息!!******

注:

统计收集方法详见: http://blog.itpub.net/69992972/viewspace-2784605/


--1. 只判断出现在SQL谓词条件中的列是否需要创建索引

select owner,

       column_name,

       num_rows,

       cardinality,

       selectivity,

       'Need index' as notice

  from (select b.owner,

               a.column_name,

               b.num_rows,

               a.num_distinct cardinality,

               round(a.num_distinct / b.num_rows * 100, 2) selectivity

          from dba_tab_col_statistics a, dba_tables b

         where a.owner = b.owner

           and a.table_name = b.table_name

           and a.owner = 'TEST'

           and a.table_name = 'TAB')

 where selectivity >= 20

   and column_name not in

       (select column_name

          from dba_ind_columns

         where table_owner = 'TEST'

           and table_name = 'TAB')

---------------- 出现在where条件中(需要提前刷新监控信息dbms_stats.flush_database_monitoring_info):

   and column_name in

       (select c.name

          from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

         where o.obj# = u.obj#

           and c.obj# = u.obj#

           and c.col# = u.intcol#

           and r.name = 'TEST'

           and o.name = 'TAB');

--2. 出现在where条件中,选择性大于20%,总行数大于5W的没有创建索引的列

select owner,

       table_name,

       column_name,

       num_rows,

       cardinality,

       selectivity,

       'Need index' as notice

  from (select a.owner,

               a.table_name,

               a.column_name,

               b.num_rows,

               a.num_distinct cardinality,

               round(a.num_distinct / b.num_rows * 100, 2) selectivity

          from dba_tab_col_statistics a, dba_tables b

         where a.owner = b.owner

           and a.table_name = b.table_name

           and a.owner = 'TEST')

 where selectivity >= 20

   and num_rows > 50000

   and (table_name, column_name) not in

       (select table_name, column_name

          from dba_ind_columns

         where table_owner = 'TEST'

           and column_position = 1)

---------------- 出现在where条件中(需要提前刷新监控信息dbms_stats.flush_database_monitoring_info):

   and (table_name, column_name) in

       (select o.name, c.name

          from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r

         where o.obj# = u.obj#

           and c.obj# = u.obj#

           and c.col# = u.intcol#

           and r.name = 'TEST');


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69992972/viewspace-2785577/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
在某银行任职DBA,拥有多年数据库运维经验,擅长Oracle,MySQL。尤其擅长Oracle的SQL优化,数据库性能调优,数据库备份、恢复与迁移。拥有的认证:OCM 12c,OCM 11g,MySQL OCP,RHCE,阿里云ACP,巨杉SCDP,软考系统集成工程师认证,Oracle Iaas OCA,OBCA,TDSQL认证,TBase认证

注册时间:2021-01-11

  • 博文量
    107
  • 访问量
    274690