SQL series-SQL statement optimization Personal Summary

about SQL statement optimization methods

Some are generic (such as avoiding select *);

Some different database management systems differ (such as WHERE clause order);

You then have to tune according to the actual environment, because even the same databases and tables, SQL efficiency may be different after the amount of data or other environment changes. Therefore, optimization is not a overnight.

some summary

Here's what I do at work, primarily in the Oracle environment Some of the commonly used SQL Statement optimization method, for reference only. of course, follow-up can further study the SQL Execution Plan, index and so on .

Specify the columns you want

Each time a field is extracted in the SELCET, the data extraction speed will be improved accordingly. The speed of ascension depends on the size of the field you discard. You should avoid using SELECT *.

Table Association Order

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, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

order in the WHERE clause

Oracle uses a bottom-up sequential parsing where clause, according to which the connection 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.

Avoid full table scan

The where is less than the not,! =, <>,!<,!>, not EXISTS, not and not, and they cause a full table scan.

Replace a HAVING clause with a WHERE clause

avoidhaving a HAVING clause that filters the result set only after all records have been retrieved.

exists instead of in

The in subquery in Oracle returns no more than 1000 results, using exists as an alternative.

Tuning process

Database environment

cc_contractinfo large table (db2|77763  oracle|77775)  

si_guarwarrants Small Table (db2|297 oracle|18294)

--Self-connected small table in the rear

--Db2|0.015s ORACLE |0.329s

Select COUNT (*) from Cc_contractinfo b,si_guarwarrants A

WHERE B.contractno=a.contractno

--0.016s 0.678s

Select COUNT (*) from si_guarwarrants A,cc_contractinfo b

WHERE A.contractno=b.contractno

--Left connection small table in front

--0.453s 0.047s

Select COUNT (*) from Cc_contractinfo b

Left JOIN si_guarwarrants A on B.contractno=a.contractno

--0.031s 0.031s

Select COUNT (*) from si_guarwarrants a

Left JOIN Cc_contractinfo B on A.contractno=b.contractno

--Inner connection small table in front

--0.078s 0.015s

Select COUNT (*) from Cc_contractinfo b

INNER JOIN si_guarwarrants A on B.contractno=a.contractno

--0.016s 0.016s

Select COUNT (*) from si_guarwarrants a

INNER JOIN Cc_contractinfo B on A.contractno=b.contractno

--where conditions exclude more data in the rear

--0.109s 0.015s

Select COUNT (*) from Cc_contractinfo b,si_guarwarrants A

WHERE B.contractno=a.contractno and B.credittype = ' 0001 '

--0.156s 0.016s

Select COUNT (*) from Cc_contractinfo b,si_guarwarrants A

WHERE b.credittype = ' 0001 ' and B.contractno=a.contractno

From for notes (Wiz)

SQL series-SQL statement optimization Personal Summary