Oracle性能分析10：重建索引续-常用SQL，oraclesql

上一节讲了重建索引的原因和重建方法，这节继续介绍几个常用的SQL语句和存储过程。

查询所有失效的全局索引

select index_name, status
  from user_indexes
 where table_name = upper(table_name)
   and status = 'UNUSABLE'

查询所有失效的分区索引

select index_name, status
  from user_ind_partitions
 where index_name in (select index_name
                        from user_indexes
                       where table_name = upper(table_name)
                         and partitioned = 'YES')
   and status = 'UNUSABLE'

在线重建所有的全局索引

下面的存储过程将在线重建指定表格的所有全局索引。

declare
  v_table_name varchar2(100) := upper(table_name);
begin
  for i_index_name in (select index_name
                         from user_indexes
                        where table_name = upper(v_table_name)
                          and partitioned = 'NO') loop
      dbms_output.put_line(i_index_name.index_name);
      execute immediate 'ALTER INDEX ' || i_index_name.index_name ||
                        ' REBUILD ONLINE';
  end loop;
end;

在线重建所有失效的全局索引

下面的存储过程查看指定表格的所有全局索引，并在线重建所有失效的全局索引。

declare
  v_table_name varchar2(100) := upper(table_name);
  v_status     varchar2(8);
begin
  for i_index_name in (select index_name
                         from user_indexes
                        where table_name = upper(v_table_name)
                          and partitioned = 'NO') loop
  
    select status
      into v_status
      from user_indexes
     where index_name = i_index_name.index_name;
    if v_status = 'UNUSABLE' then
      dbms_output.put_line(i_index_name.index_name);
      execute immediate 'ALTER INDEX ' || i_index_name.index_name ||
                        ' REBUILD ONLINE';
    end if;
  
  end loop;
end;

在线重建所有的分区索引

下面的存储过程在线重建所有的分区索引。

declare
  v_table_name varchar2(100) := upper(table_name);
begin
  FOR i_index_name IN (SELECT index_name
                         FROM user_indexes
                        WHERE table_name = upper(v_table_name)
                          and partitioned = 'YES') LOOP
    FOR i_partition_name IN (SELECT partition_name
                               FROM user_tab_partitions
                              WHERE table_name = upper(v_table_name)) LOOP
      dbms_output.put_line(i_partition_name.partition_name || ' : ' ||
                           i_index_name.Index_Name);
      execute immediate 'ALTER INDEX ' || i_index_name.Index_Name ||
                        ' REBUILD PARTITION ' ||
                        i_partition_name.partition_name || ' ONLINE';
    END LOOP;
  END LOOP;
end;

在线重建所有失效的分区索引

下面的存储过程查看指定表格的所有分区索引，依次遍历每个分区，并在线重建所有失效的分区索引。

declare
  v_table_name varchar2(100) := upper(table_name);
  v_status     varchar2(8);
begin
  FOR i_index_name IN (SELECT index_name
                         FROM user_indexes
                        WHERE table_name = upper(v_table_name)
                          and partitioned = 'YES') LOOP
    FOR i_partition_name IN (SELECT partition_name
                               FROM user_tab_partitions
                              WHERE table_name = upper(v_table_name)) LOOP
      SELECT status
        into v_status
        FROM user_ind_partitions
       WHERE index_name = i_index_name.index_name
         and partition_name = i_partition_name.partition_name;
      if v_status = 'UNUSABLE' THEN
        dbms_output.put_line(i_partition_name.partition_name || ' : ' ||
                             i_index_name.Index_Name);
        execute immediate 'ALTER INDEX ' || i_index_name.Index_Name ||
                          ' REBUILD PARTITION ' ||
                          i_partition_name.partition_name || ' ONLINE';
      END IF;
    END LOOP;
  END LOOP;
end;

也可以为上面重建索引使用nologging和compress（见重建索引）参数。

1. 应该是可行的， 具体 会不会节省时间 试一下就可以了。

2. 大概每个月存储四五十万的数据，里面只保存最新四个月的数据

每次create这7个索引用时都特别长，大概需要三四个小时；

200万的数据，重建索引花费的时间太长了；很奇怪。

3. 估计之前的 先drop掉索引，然后插入数据完毕后create索引 也是为了避免 插入数据时，索引对插入效率的影响。
 

如果5条都要插入的话就要用到游标了.
declare cursor table3_cursor is
select tet from table3;
maxid table2.ID%type;
begin
select ID into maxid from table2;
insert into table1 (id,name) values (22,maxid);

for idx in table3_cursor loop --idx可以看作是从游标中取出的值,这里就可--以看作是txt,循环取出table3中的txt
insert into table2(id,content,Date)
values(maxid,idx.txt,sysdate);
end loop;
end;