Several Methods for SQL optimization and SQL Optimization

Several Methods for SQL optimization and SQL Optimization

1. Create views of commonly used data to minimize query methods such as group by, order by, and update;

2. Establish a reasonable index;

3. Select and project first, and then connect (projection is to select the required columns from the given table; selection is to select the required rows );

4. replace in with exists. in connects the External table to the internal table as hash, while exists performs loop on the External table. Each loop then queries the internal table, that is, if the subquery table is large, the exists clause is used. If the subquery table is small, the in clause is used. The External table is located before the where clause, and the subquery is followed by the where clause. in addition, no matter which table is large, not exists is faster than not in;

5. use hints to specify the oracle optimizer. The optimizer selects an execution scheme based on these hints (Oracle Hints is a mechanism, it is used to tell the optimizer to generate an execution plan as we tell it ).