SQL (select) Statement Optimization

I. About indexes:

Rationality of using indexes:

The variable sequence in the condition clause should be the same as that in the index key sequence. (Try to create an index on the join and order by fields as much as possible) place the most restrictive conditions in front, forward, and small values in the back.

Eg :... Where o. A <1000 and O. A> 200 efficiency is higher than where O. A> 200 and O. A <1000

... Where o. A between 200 and 1000 are more efficient than where O. A> 200 and O. A <1000

2. wildcards:

Avoid using matches and like wildcard match queries:

The SQL optimizer uses the like or other wildcard to query data. In other words, the index created on the queried fields does not work.

Eg :... Where o. B <'20180101' and O. B> '20180101' is more efficient than where o. B Match' 12399 *'

Iii. subqueries:

The more nested query layers, the lower the efficiency. Therefore, avoid subqueries as much as possible. If the subquery is unavoidable, filter as many rows as possible in the subquery.

Eg :... From O1, O2 where o1.c = o2.c and o2.a = 2009 efficiency higher than from O1 where o1.c in (select o2.c from O2 where o2.a = 2009)

4. nested queries for sequential access:

When you classify indexes with a large amount of data, some forms of where clauses force the optimizer to use sequential access to invalidate the original index fields.

Eg :... Where (O. D = 123 and O. a> 200) Union O. B = 1010 efficiency higher than where (O. D = 123 and O. (B> 200) or O. B = 1010

V. Temporary tables:

Use temporary tables to accelerate queries

Sort a subset of a table and create a temporary table, which sometimes accelerates query. Note: after a temporary table is created, the modification to the primary table is not reflected. When the data in the master table is frequently modified, do not lose data.

Eg :... Select unique to1 into temp to1 "then (then)" select count (*) from temp to1 is more efficient than select count (distinct)

6. Clustering functions:

1. For the sum of large data volumes, you should avoid using a single sum command. You can use the group by method to combine them, which sometimes greatly improves the efficiency.

2. Avoid disk read/write operations such as rowid. In the WHERE clause or select statement, using rowid to generate disk read/write is a physical process that affects SQL Performance.