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
