SQL series, ultimate Series

SQL series, ultimate Series
SQL statement Optimization Methods

Some are general (for example, avoid Select *);

Some different database management systems are different (such as Where clause order );

Then you must optimize the SQL statement based on the actual environment, because even the same database and table may have different SQL efficiency after the data volume or other environment changes. Therefore, optimization is not achieved overnight.

Summary

Below are some common SQL statement optimization methods in the Oracle environment in my work, which are for reference only. Of course, you can further study it later.SQL Execution Plan and Index.

Avoid Select *

If each field is extracted from Selcet, the data extraction speed increases accordingly. The speed of improvement depends on the size of the discarded field. Select * should be avoided *.

Table Association Sequence

The Oracle parser processes the table names in the from clause in the order from right to left. The table written in the from clause (basic table driving table) will be processed first, when the from clause contains multiple tables, You must select the table with the least number of records as the base table. If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

Order in the WHERE clause

Oracle uses the bottom-up sequence to parse the Where clause. According to this principle, the join between tables must be written before other Where conditions. The conditions that can filter out the maximum number of records must be written at the end of the Where clause.

Avoid full table Scan

Where does NOT ,! =, <> ,! <,!> , Not exists, not in, not like, they will cause a full table scan.

Replace having clause with Where clause

Avoid using the having clause. having filters the result set only after all records are retrieved.

Exists replaces in

The number of results returned by the In subquery In Oracle cannot exceed 1000. Use exists as an alternative.

Performance Testing

Purpose

On the client side, I run the following statement to view the time changes by changing the table Association Sequence and where condition sequence.You may change the environment and the results will be very different. Please refer to the actual environment.

Database environment

TableA large table (DB2 data volume: 77763 ORACLE data volume: 77775)

Table B small table (DB2 data volume: 297 ORACLE data volume: 18294)

Table join sequence self-join

Small table in front, DB2 time: 0.015 s ORACLE time: 0.329 s

select count(*) from TableB b,TableA aWHERE b.ID=a.ID

Large tables in front, DB2 time: 0.016 s ORACLE time: 0.678 s

 select count(*)  from TableA a,TableB b WHERE a.ID=b.ID

We can see that the time in DB2 does not change much, and the difference in Oracle is twice.

Left join

Small table in front, DB2 time: 0.453 s ORACLE time: 0.047 s

select count(*)from TableB bLEFT JOIN TableA a ON b.ID=a.ID

Large tables in front, DB2 time: 0.031 s ORACLE time: 0.031 s

select count(*)from TableA aLEFT JOIN TableB b ON a.ID=b.ID

It can be seen that the difference in DB2 is more than 10 times, but it does not change much in Oracle.

Internal Connection

Small table in front, DB2 time: 0.078 s ORACLE time: 0.015 s

select count(*)from TableB bINNER JOIN TableA a ON b.ID=a.ID

Large tables in front, DB2 time: 0.016 s ORACLE time: 0.016 s

select count(*)from TableA aINNER JOIN TableB b ON a.ID=b.ID

We can see that the difference between DB2 and Oracle is 4 times different.

WHERE condition order

The filtering condition is on the right, and the DB2 time is 0.109 s. The ORACLE time is 0.015 s.

select count(*)from TableB b,TableA aWHERE b.ID=a.ID AND b.TYPE = '0001'

The filter conditions are left and DB2 time: 0.156 s. ORACLE time: 0.016 s.

select count(*)from TableB b,TableA aWHERE b.TYPE = '0001' AND b.ID=a.ID

DB2 changes by 1/3. when the conditions are large and the data volume is large, it becomes more obvious.