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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.