SQL Optimization Tips (1):
Connection order in the WHERE clause: Oracle parses the WHERE clause in a bottom-up order, and according to this principle, the connections between tables must be written before other where conditions, those that can filter out a large number of records
Must be written at the end of the WHERE clause.
For example
Inefficient: SELECT * from Report_sale_account E
where hsje>5000
and DZXL = ' 000001 '
and 25< (SELECT COUNT (*)
From Report_sale_account
where Code=e.code);
Efficient: SELECT * from Report_sale_account E
where 25< (select COUNT (*)
From Report_sale_account
where Code=e.code)
and hsje>5000
and DZXL = ' 000001 ';
Optimization techniques for SQL (2)
When deleting a full table, replace Delete with TRUNCATE, noting that truncate can only be applied when the full table is deleted, because truncate is DDL instead of DML.
For example, delete a 1 million row of data.
Truncate table Report_sale_account;
than delete from Report_sale_account; at least 1000 times times faster.
Optimization techniques for SQL (3)
Use commit as much as possible:
Whenever possible, use commit as much as possible for each delete, insert, update operation in the program, so that system performance is greatly improved by the resources freed by commit.
Optimization techniques for SQL (4)
Using exists instead of in can improve query efficiency.
Low efficiency
SELECT * from Report_sale_account
WHERE Com_code
Not IN (SELECT CODE
From Baseinfo_goods
WHERE DZXL = ' 000001 ')
Efficient
SELECT * from Report_sale_account
WHERE not EXISTS
(SELECT CODE from Baseinfo_goods
WHERE CODE = Report_sale_account.com_code
and DZXL = ' 000001 ')
Optimization techniques for SQL (5)
Optimize GROUP BY
By increasing the efficiency of the GROUP BY statement, you can filter out unwanted records before group by.
Optimization techniques for SQL (5)
For example:
Inefficient: Select DZXL,
AVG (Hsje)
From Report_sale_account
GROUP BY DZXL
Having DZXL = ' 000001 '
or DZXL = ' 000002 ';
Efficient: Select DZXL,
AVG (Hsje)
From Report_sale_account
where DZXL = ' 000001 '
or DZXL = ' 000002 '
Group BY DZXL;
To avoid having a HAVING clause, the having only after retrieving all the records to filter the result set, this processing needs sorting, statistics and other operations. If you can limit the number of records through the WHERE clause, you can reduce this overhead.
Optimization techniques for SQL (6)
Conditional use of Union-all instead of union: This will increase the efficiency by 3 to 5 times times.
Optimization techniques for SQL (7)
In the SQL statement that contains the subquery, pay special attention to reducing the query on the table
For example:
Low efficiency
SELECT SUM (Hsje) from Report_sale_account
WHERE DZXL = (SELECT DZXL
From Baseinfo_goods
WHERE CODE = ' 0001 ')
and PP = (SELECT pp
From Baseinfo_goods
WHERE CODE = ' 0001 ')
Efficient
SELECT SUM (Hsje) from Report_sale_account
WHERE (DZXL, PP) = (SELECT dzxl,pp
From Baseinfo_goods
WHERE CODE = ' 0001 ')
Update multiple column also applies to the above example.
Optimization techniques for SQL (8)
Avoid using ' * ' in the SELECT clause
When you want to list all columns in a SELECT clause, referencing ' * ' Using dynamic SQL columns is a convenient method, unfortunately, this is a very inefficient approach, and in fact, Oracle will convert ' * ' to all column in the process of parsing.
Name, this work is done by querying the data dictionary, which means that it will take more time.
Tips for using some functions
While blindly referencing a function in SQL can degrade performance, using the proper function correctly will not only enhance SQL readability, but also improve SQL performance, making complex queries easy to implement
Inefficient approach:
Select Name,score, ' excellent ' Grade from score Where score>=90
UNION All
Select name,score, ' good ' grade from score Where score>=80 and score<90
UNION All
Select Name,score, ' middle ' grade from score Where score>=60 and score<80
UNION All
Select name,score, ' poor ' grade from score Where score<60;
Efficient approach to 1_decode functions:
Select name,
Score,
Decode (sign (score-90),
-1,
Decode (sign (score-80),
-1,
Decode (sign (score-60),-1, ' poor ', ' Medium '),
' Liang '),
' excellent ') as Grade
From score;
Efficient Practices 2_case Wehn:
SELECT Stuname,
Score,
(Case sign (score-90)
WHEN-1 Then
Case sign (SCORE-80)
WHEN-1 Then
Case sign (SCORE-60)
WHEN-1 Then
Poor
ELSE
In
End ELSE ' good ' end
ELSE ' excellent ' END) grade
from student;
Indexed references (1)
When the inserted data is more than 10% of the number of records in the data table, you first need to delete the index of the table to improve the efficiency of inserting data, and then index it when the data is inserted.
Indexed references (2)
Avoid using functions or calculations on indexed columns, in the WHERE clause, if the index is part of a function, the optimizer will no longer use the index for full table scanning. Such as:
Low efficiency:
SELECT * FROM Report_sale_account
where Hsjj*10 >1000;
Efficient:
SELECT * FROM Report_sale_account
where HSJJ >1000/10;
Indexed references (3)
Try to avoid using not and "! =" and "<>" on the index columns, the index can only tell what exists in the table, not what does not exist in the table, and when the database encounters not and "! =" and "<>", it stops using the index to perform a full table scan.
Indexed references (4)
It is important to note that index columns are not processed in the retrieval, such as: Trim,to_date, type conversions, destroying indexes, using full table scans, affecting SQL execution efficiency
Indexed references (5)
Avoid using is null and is not NULL on indexed columns
Avoid using any nullable columns in the index, and Oracle will not be able to use the index
For single-column indexes, this record will not exist in the index if the column contains null values;
For composite indexes, if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index
Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index
Indexed references (6)
">=" in the index column instead of ">"
Inefficient: SELECT * from Report_sale_account where HSJJ > 10;
Efficient: SELECT * from Report_sale_account where Hajj >=10.000000000000001;
Compare size function Sign
function Syntax:
Sign (n)
Function Description:
Take the sign of the number n, greater than 0 returns 1, less than 0 returns-1, equals 0 returns 0
Example:
First, select sign (0), sign (-+), sign (dual);
Sign ( -100) sign (0)
———- ———- ———-
1-1 0
Second, a=10,b=20
Sign (A-B) returns-1
SQL Optimization method
The first method: Connect multiple fields with a connector.
Second method: Cancels the duplicate rows.
The third method: Use the where statement in a diligent manner.
Fourth method: Flexible use of the Count function
Fifth method: Only the fields that are required when querying.
The sixth method: the proper handling of the null field.
The seventh method: Use a lot of fuzzy query.
The eighth method: use like wildcard characters with caution.
The Nineth method: using annotations to improve the readability of query statements.
Tenth method: When necessary, restrict the rows that the user is using.
1. Avoid using "*" in the SELECT clause
When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, during the parsing process, Oracle translates "*" into all column names, which is done by querying the data dictionary, which means more time is spent.
2. Use the Decode function to reduce processing time
Use the Decode function to avoid duplicate scans of the same record or duplicate connections to the same table. For example:
SQL code
SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0020 and ename like ' smith% ';
SELECT COUNT (*), SUM (SAL) from the EMP WHERE dept_no = 0030 and ename like ' smith% ';
You can use the Decode function to get the same result efficiently:
SQL code
SELECT COUNT (DECODE (dept_no,0020, ' X ', NULL)) D0020_count,
COUNT (DECODE (dept_no,0030, ' X ', NULL)) D0030_count,
SUM (DECODE (dept_no,0020,sal,null)) D0020_sal,
SUM (DECODE (dept_no,0030,sal,null)) d0030_sal
From the EMP WHERE ename like ' smith% ';
Similarly, the Decode function can also be used in the group BY and ORDER BY clauses.
Decode meaning Explanation:
Decode (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)
3. Delete duplicate records
The most efficient way to delete duplicate records (because ROWID is used)
SQL code
DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from emp X where x.emp_no = E.emp_no);
4. Replace Delete with truncate
When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered, and if you do not have a COMMIT transaction, Oracle restores the data to the state before it was deleted (accurately reverting to the condition before the delete command was executed). When using truncate, the rollback segment no longer holds any information that can be recovered. When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short.
5. Calculate the number of record strips
In contrast to the general view, COUNT (*) is slightly faster than count (1), and of course, the count of indexed columns is still the fastest if you can retrieve them by index. For example, COUNT (EMPNO)
6. Replace the HAVING clause with a WHERE clause
Avoid having a HAVING clause that will only filter the result set after retrieving all the records, which requires sorting, totaling, and so on, and if you can limit the number of records through the WHERE clause, you can reduce this overhead, for example:
SQL code
-Low efficiency
SELECT Region,avg (log_size) from location GROUP by region have region! = ' SYDNEY ' and region! = ' PERTH '
-Efficient
SELECT Region,avg (log_size) from location WHERE region! = ' SYDNEY ' and region! = ' PERTH ' GROUP by region
7. Replace in with exists
In many base-table-based queries, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) will usually improve the efficiency of the query.
SQL code
-Low efficiency
SELECT * from EMP where EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT where LOC = ' Melb ')
--Efficient:
SELECT * from EMP where EMPNO > 0 and EXISTS (select ' X ' from DEPT where DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ')
8. Replace not in with not exists
In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS. For example:
SELECT ... From the EMP where Dept_no not in (SELECT dept_no from DEPT where dept_cat= ' A ');
SQL code
--to improve efficiency rewrite as: (Method one: efficient)
SELECT .... from EMP a,dept B WHERE a.dept_no = b.dept (+) and B.dept_no are NULL and b.dept_cat (+) = ' A '
--(Method two: most efficient)
Select .... from the EMP E where not EXISTS (SELECT ' X ' from DEPT D WHERE d.dept_no = e.dept_no and Dept_cat = ' A ');
9. Replace distinct with exists
Avoid using DISTINCT in the SELECT clause when submitting a query that contains one-to-many table information, such as a departmental table and an employee table. You can generally consider replacing with exist
For example:
SQL code
-Low efficiency:
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);
--exists makes queries faster because the RDBMS core module returns results immediately after the conditions of the subquery are met.
10. Improve efficiency with indexes
An index is a conceptual part of a table that is used to improve the efficiency of retrieving data, in fact Oracle uses a complex self-balancing b-tree structure that typically queries data faster than a full table scan, when Oracle finds the best path to execute queries and UPDATE statements. The Oracle optimizer uses indexes, as well as using indexes to join multiple tables, and the other advantage of using indexes is that it provides uniqueness validation of primary keys (primary key), and you can index almost any column except those long or long raw data types. In general, using indexes in large tables is particularly effective. Of course, you will also find that using indexes can improve efficiency when scanning small tables, although the use of indexes can improve query efficiency, but we must also pay attention to its cost. The index needs space to store, also need regular maintenance, whenever there are records in the table or the index column is modified, the index itself will be modified, which means that each record of INSERT, DELETE, update will pay more than 4, 5 times the disk I/O, because the index requires additional storage space and processing, Those unnecessary indexes can slow down the query response time.
Note: It is necessary to periodically refactor the index.
11. Avoid using calculations on indexed columns
In the WHERE clause, if the index column is part of a function, the optimizer uses a full table scan without using the index. Example:
SQL code
-Low efficiency:
SELECT ... From DEPT WHERE SAL * > 25000;
--Efficient:
SELECT ... From DEPT WHERE SAL > 25000/12;
12. Replacing > with >=
SQL code
--If there is an index on the Deptno
--Efficient:
SELECT * from EMP WHERE DEPTNO >=4
-Low efficiency:
SELECT * from EMP WHERE DEPTNO >3
The difference between the two is that the former DBMS will jump directly to the first record that dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first record with a dept greater than 3.
Description: I was founded in Iteye in 2013, is now transferred to CSDN
SQL Optimization Tips (Oracle)
