Oracle query optimization, stored procedure Select Table loops Insert another table, and index rebuild

Query statements with F5 optimization statements in PL/SQL

Oracle's explain plan tool has only one function, getting the execution plan of the statement
1. The statement itself does not execute, and Oracle generates a theoretical execution plan based on the optimizer
2. The analysis results of the statements are stored in the Table plan table

SELECT * FROM TABLE
where Nowtime >=to_date (' 20160101 ', ' yyyy-mm-dd ') and Nowtime < To_date (' 20160102 ', ' yyyy-mm-dd ')

By showing the SELECT statement is the index of the "Indexrange SCAN" followed by the index name, cost display costs, go index cost is very low.

If there is no "Indexrange SCAN", the table is indexed, indicating that the index is invalid, we do not have to delete and then build the index, only need to rebuild the index with the following statement, fast, even if the table has tens of millions of data.

Alter index INDEX_NAME rebuild tablespace tablespace_name joins the table space name, which moves the specified index into the specified table space.

When the index is rebuilt, the query can still use the old index.　　In fact, when Oracle is rebuild, the old index is not deleted during the creation of the new index until the new index rebuild successfully. From this you can know that rebuild than the removal of a reconstruction of the benefits of the original SQL query will not affect, but also because of this, the rebuild way to build an index requires that the corresponding table space free space is twice times the deletion of the reconstruction method. There are several ways to rebuild an index

Stored procedures:

//////////////////////////////////////////

Create or replace procedure Loop_while
(
Start_date in Date,
End_date in Date
)
Is
Current_date Date: = start_date;

Current_date_end date;
Begin
While Current_date <=end_date
Loop
current_date_end:=current_date+1;
INSERT INTO TABLE1
SELECT * FROM TABLE
where Nowtime >=current_date and Nowtime < current_date+1;
Commit
current_date:=current_date+1;
End Loop;
End Loop_while;

/////////////////////////////////////////////////////////////////

Inserts a table in a day loop.

Oracle query optimization, stored procedure Select Table loops Insert another table, and index rebuild