Detailed description of Oracle Query Optimization Principles

1: When performing multi-table join, use the Where statement to minimize the result set of a single table. Use the aggregate function to summarize the result set and associate it with other tables to minimize the data volume of the result set.
2: When two tables are joined, you should consider whether to use right join. To increase the query speed
3: Use where instead of havingWhere is used to filter rows, while having is used to filter groups, because having can filter groups after rows are grouped.
4: Use exists instead of IN because Exists only checks the existence of rows, while in checks the actual value.
5: IN Operator
SQL statements written IN are easy to write and understand, which is suitable for modern software development.
However, the SQL Performance with IN is always relatively low because:
For SQL statements that use IN, ORACLE always tries to convert them into connections of multiple tables. If the conversion fails, execute IN
And then query the outer table records.
If the conversion is successful, it is converted to the join of multiple tables. Therefore, if you do not manage it, the SQL statement using IN always has one more conversion.
Process. Normal SQL statements can be converted successfully.
However, SQL statements that contain grouping statistics cannot be converted. Therefore, do not use the IN operator IN business-intensive SQL statements.
6: not in Operator
This operation is strongly recommended because it cannot apply table indexes.
In this case, use EXISTS, not exists, or (Outer Join + is null.
7: <> Operator
The non-equals operator will never use the index, so the processing of it will only generate a full table scan.
In this case, you can use other methods, such:
A <> 0-> A> 0 or a <0
A <> ''-> A>''
8: like Operator
You can use instr instead of LIKE to filter SQL statements. The processing speed will be significantly improved.
9: union operator
Union filters out duplicate records after table links. Therefore, after table links, it sorts the generated result sets,
Delete duplicate records and return results. In most applications, duplicate records are not generated. The most common is Process Tables and history.
Table union. For example:
Copy codeThe Code is as follows:
Select * from gc_dfys
Union
Select * from ls_jg_dfys

This SQL statement extracts the results of two tables at run time, sorts and deletes duplicate records in the sorting space, and returns the result set,
If the table contains a large amount of data, it may be sorted by disk.
Recommended Solution: Use the union ALL operator to replace union, because the union ALL operation simply merges the two results and returns them.
Copy codeThe Code is as follows:
Select * from gc_dfys
Union all
Select * from ls_jg_dfys

10 SQL writing impact
Impact of SQL statement writing with the same function and performance
For example, if an SQL statement is written by A programmer
Select * from zl_yhjbqk
Programmer B writes
Select * from dlyx. zl_yhjbqk (with the table owner prefix)
C programmers write
Select * from DLYX. ZLYHJBQK (Capital table name)
D programmers write
Select * from DLYX. ZLYHJBQK (spaces are added in the middle)
The results and execution time of the preceding four sqls are the same after ORACLE analysis, but the shared memory SGA
Principle,
It can be concluded that ORACLE will analyze each SQL statement and occupy the shared memory. If you write the SQL string and format
If they are identical, ORACLE only analyzes the data once,
Shared Memory also leaves only one analysis result, which not only reduces the time for SQL analysis, but also reduces the number of duplicate shared memory
Information, ORACLE can also accurately calculate the frequency of SQL Execution.
11: conditional order after where
The conditional order after the where clause directly affects the query of the big data table, as shown in figure
Copy codeThe Code is as follows:
Select * from zl_yhjbqk where dy_dj = '1k' and xh_bz = 1
Select * from zl_yhjbqk where xh_bz = 1 and dy_dj = '1kv below'

In the preceding two SQL statements, the dy_dj and xh_bz fields are not indexed.
Is full table scan,
In the first SQL statement, the dy_dj = '1kv below 'condition is 99% in the record set, while the xh_bz = 1 condition is only 0.5%,
Comparison of dy_dj and xh_bz is performed for the first SQL statement, and comparison of 99% records during the second SQL statement
Comparison between dy_dj and xh_bz,
The CPU usage of the second SQL statement is obviously lower than that of the first SQL statement.
12: Impact of query order
The list order in the table following FROM will affect SQL Execution performance.
In this case, ORACLE links the tables in the order they appear,
As a result, data that consumes a lot of server resources is generated when the table order is incorrect. (Note: If statistical analysis is performed on a table,
ORACLE automatically links small tables before large tables)
13: indexes cannot be used for fields processed by functions, for example:
Copy codeThe Code is as follows:
Substr (hbs_bh, 5400) = '20160301', optimization: hbs_bh like '20160301'
Trunc (sk_rq) = trunc (sysdate), optimization:
Sk_rq> = trunc (sysdate) and sk_rq <trunc (sysdate + 1)

Fields with explicit or implicit operations cannot be indexed, for example:
Copy codeThe Code is as follows:
Ss_df + 20> 50, optimization: ss_df> 30
'X' | hbs_bh> 'x5400021452 '. Optimization: hbs_bh> '123'
Sk_rq + 5 = sysdate, optimized: sk_rq = sysdate-5
Hbs_bh = 5401002554, optimized: hbs_bh = '000000'. Note: This condition is implicitly used for hbs_bh.

To_number conversion, because the hbs_bh field is character type.
Fields in multiple tables cannot be indexed, for example:
Ys_df> cx_df, cannot be optimized
Qc_bh | kh_bh = '000000', optimization: qc_bh = '000000' and kh_bh = '000000'
14: Apply oracle hint (prompt) for processing
The prompt processing is used when the SQL analysis execution path generated by ORACLE is not satisfied. It can perform the following operations on SQL:
Tips
Target tips:
COST (COST-based optimization)
RULE (RULE-Based Optimization)
CHOOSE (default) (ORACLE automatically selects cost or rules for optimization)

SELECT EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';
ALL_ROWS (all rows are returned as soon as possible)
SELECT EMP_NO, EMP_NAM, DAT_IN from bsempms where EMP_NO = 'Scott ';
FIRST_ROWS (the first row of data is returned as soon as possible)
Select *
From xxx
Where xxx;
Select *
From xxx
Where xxx
Optimizer prompt:It is used to increase the response time of SQL statements and quickly return n rows first.
Access path prompt
FULL: perform FULL table Scan
ROID: scan by ROWID
INDEX: Scan Based on an INDEX
Select * from emp where deptno = 200 and sal> 300;
If you write more than one, ORACLE automatically selects the optimal one.
Select * from emp where deptno = 200 and sal> 300;
INDEX_JOIN: If the selected fields are all index fields (several indexes), you can access the data through the index connection without accessing
Table data.

Select deptno, sal from emp
Where deptno = 20;
INDEX_FFS: performs a quick full index scan.

Select count (*) from emp;
NO_INDEX: Specifies which indexes are not used

Select * from emp where deptno = 200
And sal> 300;
AND_EQUAL: Specify the result (intersection) for merging two or more index searches. A maximum of five indexes can be merged.

The execution method prompt is as follows:
USE_NL (use nested loops to join)
USE_MERGE (Union using MERGE join)
USE_HASH (Union using HASH join)

According to the order in which the table appears in FROM, ORDERED connects ORACLE to the table in this order.
For example:
Select a. COL1, B. COL2, C. COL3 FROM TABLE1 A, TABLE2 B, TABLE3 C
Where a. COL1 = B. COL1 and B. COL1 = C. COL1;

Concatenates the specified table with the nested connected row source and uses the specified table as the internal table.
For example:
Select bsdptms. DPT_NO, BSEMPMS. EMP_NO, BSEMPMS. EMP_NAM
From bsempms, bsdptms where bsempms. DPT_NO = BSDPTMS. DPT_NO;

The specified table is connected to other row sources by means of merged sort connections.
For example:
SELECT * from bsempms, BSDPTMS
Where bsempms. DPT_NO = BSDPTMS. DPT_NO;

Connects the specified table to other row sources by means of hash connections.
For example:
SELECT * from bsempms, BSDPTMS
Where bsempms. DPT_NO = BSDPTMS. DPT_NO;
Other advanced prompts (such as parallel processing)
The prompt function of ORACLE is a strong function and a complicated application, and the prompt is just a suggestion for ORACLE execution,
Sometimes ORACLE may not follow the prompts for cost considerations. Based on practical applications, it is generally not recommended that developers Apply ORACLE prompts,
Because the performance of each database and server is different, the performance may be improved in one place, but the performance may be decreased in another place,
ORACLE has been mature in SQL Execution and analysis. If the analysis execution path is incorrect, it should first be in the database structure (mainly index ),
The current server performance (shared memory, disk file fragments), database objects (tables, indexes) Statistics are correct.