引查询常用相关语句-1

1.当前用户下,哪些表的索引个数字超过5个的

   select table_name, count(*) cnt from user_indexes

      group by table_name having count(*) >= 5  order by cnt desc ;

2.外键未建索引的

   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);

3.当前用户下，哪些组合索引组合列超过4个的

   select table_name, index_name, count(*) from user_ind_columns

     group by table_name, index_name having count(*) >= 4

     order by count(*) desc;

4.针对普通表（大于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;

5.针对分区表（大于2GB的分区表未建任何索引）

   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;