How Oracle writes efficient SQL

Reprint: http://www.blogjava.net/ashutc/archive/2009/07/19/277215.html

1. Select the most efficient indication order (only valid in rule-based optimizer)

The Oracle parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the final table (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 fewest number of record bars as the underlying table. If you have more than 3 tables connected to the query, then you need to select the crosstab (intersection table) as the base table, which refers to the table that is referenced by other tables

2. Connection order in the WHERE clause

Oracle uses a bottom-up sequence to parse where clauses, and according to this principle, the connections between tables must be written before other where conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause (Ensure that the first processing conditions can filter out the most records, allowing subsequent conditional processing to handle as few records as possible)

3. Avoid using "*" in the SELECT clause

During the parsing process, Oracle converts * once to all column names, which is done by querying the data dictionary, which means more time is spent

4, reduce the number of access to the database

Oracle has done a lot of work internally: Parsing SQL statements, estimating utilization of indexes, binding variables, reading blocks, etc.

5, reset the arraysize parameter in Sql*plus, Sql*forms and proc*c, can increase the amount of index data per database access, the recommended value is 200

6, use 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

7, integrated simple, non-related database access

If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them)

8. Delete duplicate records

The most efficient way to delete duplicate records (because of the use of ROWID) example

DELETE from emp E where E.rowid > (    SELECT MIN (x.rowid) from    emp X    where x.emp_no = e.emp_no)

　　

9. Replace DELETE with truncate

When deleting records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered, and if you do not commint the transaction, Oracle restores the data to the state before the deletion (accurately reverting to the state before the delete command was executed). When using truncate, the rollback segment no longer holds any information that can be recovered. When the command is run, the data cannot be restored, so very few resources are invoked and the execution time is short

But TRUNCATE is only used to remove all tables, TRUNCATE is DDL is not DML

10. Use COMMIT as much as possible

Whenever possible, use commit as much as you can in your program, so that the performance of the program is improved and the requirements are reduced due to the fields that are freed by the commit

Resources Freed by Commit:

A. Information for recovering data on a rollback segment

B. Locks acquired by program statements

C. Redo space in the log buffer

D. Oracle to govern internal spending in the 3 resources above

11. Replace the HAVING clause with a WHERE clause

Avoid having a HAVING clause thatfilters the result set only after all records have been retrieved . This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce this overhead. (Non-Oracle) on, where, have this single clause that can be conditionally added, on is the first execution, where second, having the last.

Because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to deal with the data, supposedly it should be the fastest.

Where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared.

In the case of single-table query statistics, if the conditions to be filtered are not related to the calculated fields, the result is the same, just where the Rushmore technique can be used, and the having cannot, the latter is slower in speed. If the field of calculation is to be involved, it means that the value of the field is indeterminate until it is calculated, according to the workflow described above, where the action time is done before the calculation, and having is the function after the calculation, so in this case, the results will be different.

On a win-join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, and then the where is filtered, then computed, and then filtered by having after the calculation is complete.

Thus, in order to filter conditions to play the right role, first of all to clearly report this condition should be in effect, and then decide where to put

12, reduce the query of the table

In the SQL statement that contains the subquery, it is important to focus on reducing the query to the table, example

Select Tab_namefrom tableswhere (tab_name, db_ver) = (    select Tab_name, db_ver from    tab_columns    WHERE version=604    )

　　

13. Improve SQL efficiency through internal functions

Complex SQL often sacrifices execution efficiency. The ability to grasp the above arithmetic function to solve the problem is often very meaningful in practical work.

　　

14. Alias using the table

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column so that the parsing time is reduced and the syntax errors caused by the column ambiguity are reduced

15, replace with EXISTS in; replace not in with NO EXISTS

In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition, in which case, using EXISTS (or not EXISTS) will generally improve the efficiency of the query, in which the not-in clause performs an internal sort and merge.

In either case, not is inefficient (because it performs a full table traversal of the table in the subquery). To avoid using not, you can change it to an outer join (Outer Joins) or not EXISTS

An efficient SQL instance

select* from EMP (base table) where Empno>0 and EXISTS (    SELECT ' X ' from    DEPT    where DEPT. Deptno=emp. DEPTNO and loc= ' Melb '    )

Low-efficiency SQL instance

SELECT * from EMP (base table) where Empno>0 and DEPTNO in (    SELECT DEPTNO from    DEPT    where loc= ' Melb '    )

　　

16. Identify "inefficient execution" of SQL statements

Although there are many graphical tools for SQL optimization, it is always a good idea to write your own SQL tools to solve the problem.

　　

17. Improve efficiency with index

An index is a conceptual part of a table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure. Typically, the query data is faster than a full table scan.

When Oracle finds the best path to execute queries and UPDATE statements, the Oracle Optimizer uses the index.

Also, using indexes when joining multiple tables can improve efficiency.

Another advantage of using an index is that it provides uniqueness validation of the primary key (PRIMARY key). Those long or long RAW data types, you can index almost all the columns.

In general, the use of indexes in large tables can improve query efficiency, but we must also pay attention to its cost. So the need for space to store, also need regular maintenance, whenever there are records in the table or the index column is modified, the reduction itself is also modified. This means that no record of INSERT, DELETE, update will pay more than 4, 5 times the disk I/O.

Because indexes require additional storage space and processing, those unnecessary indexes can slow down the query time. It is necessary to periodically refactor the index

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

　　

18, replace DISTINCT with exists

Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. It is generally possible to consider using exists substitution, exists is a faster query because the RDBMS core module will return results immediately after the condition of the subquery is met

Low-efficiency SQL instance

SELECT DISTINCT dept_no, Dept_namefrom DEPT D, EMP ewhere d.dept_no=e.dept_no

An efficient SQL instance

Select Dept_no, Dept_namefrom DEPT dwhere EXISTS (    select ' X ' from     EMP E    WHERE e.dept_no=d.dept_no    )

　　

19, the SQL statement with uppercase

Because Oracle always parses SQL statements first, the lowercase letters are converted to uppercase in the execution

20. Use the connector "+" connection string sparingly in Java code

21. Avoid using not on indexed columns

In general, we use Not,not on an indexed column to produce the same printing effect as using functions on an indexed column. When Oracle "encounters" not, it stops using the index instead of choking the full table scan

22. Avoid using calculations on indexed columns

In the WHERE clause, if the index column is part of a function, the optimizer will no longer use the index for full table scanning

Low-efficiency SQL instance

SELECT ... From Deptwhere sql*12>25000

An efficient SQL instance

SELECT ... From Deptwhere SAL > 25000/12

　　

23, replace > with >=

An efficient SQL instance

SELECT *from Empwhere deptno>=4

Low-efficiency SQL instance

SELECT * from EMP WHERE DEPTNO >3

The difference between the two is that the pre-DBMS will jump directly to the first record that dept equals 4, and the latter will first navigate to the Deptno=3 record and scan forward to the first record with a dept greater than 3.

Similarly, replace < with <=

24. Replace or with UNION (for indexed columns)

In general, replacing or in a WHERE clause with Union will have a good effect. Using OR on an indexed column causes a full table scan.

Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not choose or . In the following example, indexes are established on both loc_id and region

An efficient SQL instance

SELECT loc_id, Loc_desc, Regionfrom locationwhere loc_id=10 unionselect loc_id, Loc_desc, Regionfrom LOCATIONWHERE region = ' MELBOURNE '

Low-efficiency SQL instance

SELECT loc_id, Loc_desc, Regionfrom locationwhere loc_id=10 OR region= ' MELBOURNE '

　　If you insist on OR, you need to return the least logged index column to the front

25.

How Oracle writes efficient SQL