1: When multiple table associations are used, the multiple where statement minimizes the result set of a single table, uses aggregate functions to summarize the result set, and then associates with other tables to minimize the amount of result set data
2: You should consider the possibility of using a right connection when you are associating two tables. To improve query speed
3: Use where instead of having , where is used to filter rows, and having is used to filter groups, because the rows are grouped, having to filter the group, so try to filter the user where
4: Use exists instead of in because exists only checks the existence of rows, while in examines the actual values.
5:in operator
The advantage of using in-write SQL is that it is easier to write and understand, which is more suited to the style of modern software development.
However, in SQL performance is always low, because:
For SQL statements in, ORACLE always attempts to convert the connection to multiple tables, and if the conversion is unsuccessful, the first execution in
Inside of the subquery, and then query the outer table record
A connection that is converted to multiple tables if the conversion succeeds. So don't manage how, in SQL statements always have one more conversion
Process. Normal SQL can be converted successfully.
But for SQL that contains packet statistics, it cannot be converted. Therefore, in the business-intensive SQL, try not to use the in operator.
6:not in operator
This operation is strongly recommended for use because it cannot apply the index of the table.
In this case, it should be replaced with EXISTS, not EXISTS or (outer join + NULL) scheme.
7:<> operator
Not equal to the operator is never used in the index, so processing it will only produce a full table scan.
In this case, other ways can be substituted, such as:
A<>0-> a>0 OR a<0
A<> '-> a> '
8:like operator
If you encounter SQL statements that require like filtering, you can use InStr instead. Processing speed will be significantly improved.
9:union operator
The Union filters out duplicate records after a table link, so the resulting set of results is sorted after the table is connected.
Delete duplicate records and return the results. Most applications do not produce duplicate records, the most common is the process table and history
Table Union. Such as:
Copy Code code as follows:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables first, then sorts the duplicate records with the sort space, and finally returns the result set.
If the amount of table data is large, it may result in sorting by disk.
Recommendation: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.
Copy Code code as follows:
SELECT * FROM Gc_dfys
UNION ALL
SELECT * FROM Ls_jg_dfys
The impact of SQL writing
The same function, same performance, different syntax, SQL effect
such as a SQL in a programmer written for
SELECT * FROM Zl_yhjbqk
b The programmer writes for
SELECT * from dlyx.zl_yhjbqk (prefix with table owner)
C the programmer writes for
SELECT * from Dlyx. ZLYHJBQK (uppercase table name)
D Programmer writes for
SELECT * from Dlyx. ZLYHJBQK (more space in the middle)
The above four SQL produces the same results and execution time after Oracle parsing, but the shared memory of the SGA from the Oracle
Principle
It can be concluded that Oracle will parse it once for each SQL and consume shared memory, if the SQL string and format are written
Exactly the same, Oracle will only analyze it once,
Shared memory also leaves only one analysis, which reduces the time it will take to parse SQL, and reduces the amount of shared memory duplicated
Information, Oracle can also accurately count the execution frequency of SQL.
11:where The conditional order after the
The conditional order after the WHERE clause has a direct effect on queries to large data scales, such as
Copy Code code as follows:
SELECT * from zl_yhjbqk where dy_dj = ' 1KV below ' and xh_bz=1
SELECT * from Zl_yhjbqk where xh_bz=1 and dy_dj = ' below ' 1KV
The above two SQL DY_DJ (voltage level) and XH_BZ (PIN user flag) Two fields are not indexed, so the implementation of the time are
is a full table scan,
The first SQL DY_DJ = ' 1KV below ' condition has a ratio of 99% in the Recordset, while the xh_bz=1 ratio is only 0.5%,
In the first SQL, 99% records were compared Dy_dj and xh_bz, while in the second SQL, 0.5% records
are compared with DY_DJ and XH_BZ,
As a result, the second SQL CPU occupancy rate is significantly lower than the first one.
12: Impact of order of inquiry
The order of the lists in the table following from will have an impact on SQL performance, where there are no indexes and the Oracle does not have statistical analysis of the table
In the case Oracle will link in the order in which the table appears,
This is because the order of the tables does not result in data crossings that are very consuming server resources. (Note: If you make a statistical analysis of the table,
Oracle automatically links to advanced small tables, then links to large tables
13: Fields that are processed by functions cannot take advantage of indexes, such as:
Copy Code code as follows:
substr (hbs_bh,1,4) = ' 5400 ', optimizing processing: Hbs_bh like ' 5,400% '
Trunc (SK_RQ) =trunc (sysdate), optimized processing:
Sk_rq>=trunc (Sysdate) and Sk_rq<trunc (sysdate+1)
Fields that have an explicit or implicit operation cannot be indexed, such as:
Copy Code code as follows:
SS_DF+20>50, optimizing Processing: ss_df>30
' X ' | | Hbs_bh> ' X5400021452 ', optimized for processing:hbs_bh> ' 5400021542 '
Sk_rq+5=sysdate, optimizing Processing: sk_rq=sysdate-5
hbs_bh=5401002554, Optimizing Processing: hbs_bh= ' 5401002554 ', note: This condition is implicit to the HBS_BH
To_number conversion because the Hbs_bh field is a character type.
A condition that includes multiple field operations for this table cannot be indexed, such as:
YS_DF>CX_DF, unable to optimize
qc_bh| | Kh_bh= ' 5400250000 ', Optimizing Processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '
14: Apply Oracle's HINT (hint) processing
Prompt processing is used when the SQL profiling execution path generated by Oracle is not satisfactory. It can be done to the SQL below
The hint of the face
goal-related tips:
Cost (optimized by costs)
Rule (optimized by rules)
CHOOSE (default) (Oracle automatically selects costs or rules for optimization)
SELECT emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
All_rows (all lines are returned as soon as possible)
SELECT emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
First_rows (first line of data return as soon as possible)
SELECT *
from xxx
where xxx;
SELECT *
from xxx
where xxx
Optimizer tip:The goal is to improve the response time of SQL statements, and quickly return n rows first.
Tips for accessing paths
Full: Perform whole-table scans
ROID: Scan According to ROWID
Index: Scanning based on an index
SELECT * from emp where deptno=200 and sal>300;
If more than one is written, Oracle automatically chooses which of the best
SELECT * from emp where deptno=200 and sal>300;
Index_join: If the selected field is an indexed field (several indexes), you can access the data through an indexed connection without having to access
Table's data.
Select Deptno,sal from emp
where deptno=20;
Index_ffs: Perform fast full index scans
Select COUNT (*) from EMP;
No_index: Specify which indexes are not used
SELECT * FROM EMP where deptno=200
and sal>300;
And_equal: Specifies the result (intersection) of merging two or more index searches, up to 5
Tips for executing methods:
USE_NL (combined with nested loops method)
Use_merge (union with MERGE join)
Use_hash (Federated with HASH join)
Depending on the order in which the table appears in from, ordered enables Oracle to connect to it 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;
Connects the specified table to the row source of the nested connection and takes the specified table as an 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
joins the specified table with other row sources by merging the sort connections.
For example:
SELECT * from Bsempms,bsdptms
WHERE Bsempms. Dpt_no=bsdptms. Dpt_no; The
connects the specified table to other row sources through a hash connection.
For example:
SELECT * from Bsempms,bsdptms
WHERE Bsempms. Dpt_no=bsdptms. dpt_no;
Other advanced hints (such as parallel processing, and so on)
Oracle's hints are more powerful, more complex applications, and hints are just a recommendation for Oracle,
Sometimes Oracle may not follow the prompts for cost considerations. Based on practical applications, developers are generally not advised to apply Oracle hints,
because of the different database and server performance situation, it is likely that one local performance increased, but the other place is down,
Oracle in the SQL execution analysis has been more mature, If the parse execution path does not first analyze the database structure (primarily the index), the
server's current performance (shared memory, disk file fragmentation), database object (table, index) statistics are correct.
