To improve the efficiency of SQL query how to write the precedence of where statement conditions

Source: Internet
Author: User
Tags one table

We have to do not only write SQL, but also to write a good performance SQLStatement.


(1) Select the most efficient table name Order(Valid only in rule-based optimizer):

Oracle's parser follows right-to-left OrderThe table name in the FROM clause is processed, and the last table written in the FROM clause (the underlying table driving tables) will be processed first, and in the case where the FROM clause contains more than one table, you must select the table with the lowest number of record bars as the underlying table. If more than 3 tables are connected Enquiry, you need to select the crosstab (intersection table) as the underlying table, which is the table referenced by the other table.

(2) connection in the WHERE clause Order

Oracle uses a bottom-up OrderParse WHERE clause, according to this principle, the connection between the tables must be written in the other where conditionsBefore, those that can filter out the maximum number of records conditionsMust be written at the end of the WHERE clause. China. Webmaster Station

(3) Avoid using ' * ' in the SELECT clause:

Oracle will convert ' * ' to all column names in the process of parsing, which is done by EnquiryThe data dictionary is done, which means more time is spent.

(4) Reduce the number of access to the database:

Oracle does a lot of work internally: parsing SQLStatement, estimating IndexUtilization, binding variables, read data blocks, and so on.

(5) in SQL*plus, SQLThe ArraySize parameter is reset in *forms and pro*c to increase the amount of data retrieved per database access, with a recommended value of 200.


(6) Use the Decode function to reduce processing time:

Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table.

To improve the efficiency of SQL query how to write the precedence of where statement conditions

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.