Oracle Performance Analysis 10: Re-indexing-common SQL and oraclesql

Oracle Performance Analysis 10: Re-indexing-common SQL and oraclesql

The previous section describes the causes and methods for re-indexing. This section describes several common SQL statements and stored procedures.

Query all invalid global indexes

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

Query all invalid partition Indexes

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'

Rebuilding all global indexes online

The following stored procedure recreates all global indexes of a specified table online.

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;

Rebuilding all invalid global indexes online

The following stored procedures view all global indexes of a specified table and recreate all invalid global indexes online.

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;

Rebuilding all partition indexes online

The following stored procedure re-creates all the partition indexes online.

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;

Rebuilding all invalid partition indexes online

In the following stored procedure, you can view all the partition indexes of a specified table, traverse each partition in sequence, and recreate all invalid partition indexes online.

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;

You can also use the nologging and compress parameters for the preceding re-indexing (see re-indexing.

Oracle index problems, deleting and re-indexing and Index Analysis

1. It should be feasible. Will it save time and try it.

2. About 400 thousands or 500 thousands of data is stored every month, and only the data of the last four months is saved.

It takes about three or four hours to create these seven indexes each time;

It takes too long to rebuild the index for 2 million of the data. It is strange.

3. It is estimated that the index will be dropped first, and then the create index will avoid the impact of the index on the insertion efficiency when data is inserted.

How can I re-index oracle databases in a PL-SQL?

If you want to insert 5 entries, you need to use the cursor.
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 can be seen as the value retrieved from the cursor. Here we can -- take it as a txt, and retrieve the txt in table3 cyclically.
Insert into table2 (id, content, Date)
Values(maxid,idx.txt, sysdate );
End loop;
End;