SQL optimization series (2) _ SQL optimization sorting

SQL optimization series (2) _ SQL optimization sorting 1. SQL query optimization (1 ). table name order (only valid in rule-based Optimizer): The Parser for www.2cto.com ORACLE processes the table names in the FROM clause in the order FROM right to left, the base table driving table written in the FROM clause will be processed first. When the FROM clause contains multiple tables, you must select a 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. (2 ). connection sequence in WHERE clause: ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connection 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 .. (3 ). to avoid using '*': www.2cto.com in the select clause, ORACLE converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, this means that it will take more time. (4 ). replace HAVING clause with the Where clause: avoid HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced. (in non-oracle) where on, where, and having can be added, on is the first statement to execute, where is the second clause, and having is the last clause, because on filters out records that do not meet the conditions before making statistics, it can reduce the data to be processed by intermediate operations. It is reasonable to say that the speed is the fastest, where should also be faster than having, because it performs sum only after filtering data, and on is used only when two tables are joined, so in a table, then we can compare where with having. In the case of single-Table query statistics, if the filter condition does not involve fields to be calculated, the results will be the same, but the where technology can be used, having cannot. The latter must be slow in terms of speed. If it involves a calculated field, it means that the value of this field is uncertain before calculation, according to the workflow written in the previous article, the where function is completed before computing, and having is used only after computing. In this case, the results are different. In multi-table join queries, on takes effect earlier than where. The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by having after calculation. It can be seen that to filter a condition to play a correct role, you must first understand when the condition should take effect and then decide to put it there. 5. when querying multiple tables, use the table Alias (Alias): when connecting multiple tables in an SQL statement, use the table Alias and prefix the Alias on each Column. in this way, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced. 6. replace IN with EXISTS and not exists instead of not in: IN many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency. IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). to avoid using not in, we can rewrite it into an Outer join (Outer Joins) or not exists. Example: (efficient) SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb') (inefficient) SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ') (7 ). using indexes to improve efficiency: indexes are a conceptual part of a table to improve data retrieval efficiency. ORACLE uses a complex self-balancing B-tree structure. data Query by index is usually faster than full table scan. when ORACLE finds the optimal path for executing the query and Update statements, the ORACLE optimizer uses the index. using indexes when joining multiple tables can also improve efficiency. another benefit of using an index is that it provides a primary key (Primary key .. For those LONG or long raw data types, You Can index almost all columns. generally, using indexes in large tables is particularly effective. of course, you will also find that using indexes to scan small tables can also improve efficiency. although the index can improve the query efficiency, we must pay attention to its cost. the index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. this means that the INSERT, DELETE, and UPDATE operations for each record will pay four or five more disk I/O. because indexes require additional storage space and processing, unnecessary indexes will slow the query response time .. Regular INDEX reconstruction is necessary: alter index <INDEXNAME> REBUILD <TABLESPACENAME> (8 ). replace DISTINCT with EXISTS: when you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will immediately return results once the subquery conditions are met. Example: (inefficient): select distinct DEPT_NO, DEPT_NAME from dept d, emp e where d. DEPT_NO = E. DEPT_NO; (efficient): SELECT DEPT_NO, DEPT_NAME from dept d where exists (SELECT 'x' from emp e where e. DEPT_NO = D. DEPT_NO); (9 ). replace OR with IN: (inefficient) SELECT .... From location where LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 (efficient) SELECT... From location where LOC_IN IN (10, 20, 30); (10 ). avoid using NOT in the index column: we should avoid using NOT in the index column, NOT will have the same impact as using the function in the index column. when ORACLE Encounters "NOT", it stops using indexes and then performs full table scanning. (11 ). avoid using the WHERE clause in the index column if the index column is part of the function. the optimizer uses full table scan instead of indexing. example: (inefficient) SELECT... From dept where sal * 12> 25000; (efficient) SELECT... From dept where sal> 25000/12; (12) the WHERE clause in some SELECT statements does not use indexes. In the following example, (1 )'! = 'No index is used. remember, indexes only tell you what exists in the table, but not what does not exist in the table. (2) '|' is a character concatenation function. as with other functions, indexes are disabled. (3) '+' is a mathematical function. as with other mathematical functions, indexes are disabled. (4) The same index Columns cannot be compared with each other, which enables full table scan. (13 ). SQL statement in uppercase: Because oracle always parses SQL statements first, converts lowercase letters into uppercase letters, and then executes limit 2. other optimizations 1. the most efficient method to DELETE duplicate records: delete from emp e where e. ROWID> (select min (X. ROWID) from emp x where x. EMP_NO = E. EMP_NO); 2. replace DELETE with TRUNCATE. When deleting a table record, a rollback segment is usually used to store recoverable information. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than DML.) 3. use COMMIT to release space as long as possible and use COMMIT as much as possible in the program. In this way, the performance of the program is improved, and the demand for resources released by COMMIT will also be reduced: resources released by COMMIT:. information used to restore data on the rollback segment. b. the lock obtained by the Program Statement c. space d in redo log buffer. ORACLE manages the internal costs of the above three types of resources. Reference link: 1. SQL Optimization Principles http://www.bkjia.com/database/201303/192993.html