I. Overview
The core of the report is data. Whether the data set is reasonable determines the quality of the report.
1) Each report should have a main data set. In order to reduce the workload during maintenance, try to put all fields in the main data set, unless in some cases, not using multi-source data sets will cause the main data set to be extremely complicated .
2) Before making a report, try to consider all the data fields that need to be displayed. In the database software, reasonably write SQL statements, and try to optimize SQL and add indexes for big data.
3) With high-performance SQL query statements, the query speed can be accelerated, and the report display speed has been significantly improved!
II.Program introduction
1. Avoid using "*" in the SELECT clause
When you want to list all COLUMNs in the SELECT clause, it is a convenient way to use dynamic SQL column reference ‘*’.
But this is a very inefficient method. In fact, ORACLE will convert "*" into all column names in turn during the parsing process. This work is done by querying the data dictionary, which means that it will consume more time.
2. Delete duplicate records
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)
3. Use TRUNCATE instead of DELETE
When deleting records in the table, rollback segments are used to store information that can be recovered. If you do not have a COMMIT transaction, ORACLE will restore the data to the state before the deletion (to be precise to the state before executing the delete command), and when using TRUNCATE, the rollback segment no longer stores any information that can be restored. When the command is run, the data cannot be restored. Therefore, few resources are called and the execution time is short.
4. Calculate the number of records
Contrary to the general opinion, count(*) is slightly faster than count(1). Of course, if it can be retrieved by index, the count of indexed columns is still the fastest. For example: COUNT(EMPNO)
5. Use EXISTS instead of IN
In many queries based on the underlying table, in order to meet a condition, it is often necessary to join another table. In this case, using EXISTS or NOT EXISTS can improve the efficiency of the query. E.g:
Inefficient:
SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
6. Replace DISTINCT with EXISTS
When submitting a query that contains one-to-many table information (such as department table and employee table), avoid using DISTINCT in the SELECT clause, and generally consider replacing it with EXIST.E.g::
Inefficient:
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)
7. Replace> with >=
For example, there is an index on DEPTNO:
SELECT * FROM EMP WHERE DEPTNO >3
The difference between the two is that the former DBMS will directly jump to the first record with DEPT equal to 4. The latter will first locate the record with DEPTNO=3 and scan forward to the first record with DEPT greater than 3.
8. Try to avoid judging fields in the where clause
select id from t where num=0
9. Avoid using the != or <> operator in the where clause
The engine abandons the use of indexes and performs a full table scan. The optimizer will not be able to determine the number of rows to be hit through the index, so it needs to search all rows of the table.
10. Avoid using or connection in the where clause
select id from t where num=10 union all select id from t where num=20
11. in and not in should also be used with caution
select id from t where num between 1 and 3
12. Avoid expression operations in the where clause
Note: Any operation on the column will result in a table scan, which includes database functions, calculation expressions, etc., when querying, move the operation to the right of the equal sign as much as possible.
13. Avoid performing functional operations in the where clause
This will cause the engine to give up using the index and perform a full table scan. E.g:
1) id whose name starts with abc
select id from t where substring(name,1,3)='abc'
Should be changed to:
select id from t where name like 'abc%'
2）The id generated by‘2005-11-30’
select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-1'
14. Do not operate on the left side of the "=" in the where clause
Performing functions, arithmetic operations, or other expression operations, the system may not be able to use indexes correctly.
15. Try to avoid returning large amounts of data to the client
If the amount of data is too large, you should consider whether the corresponding demand is reasonable.
16. Avoid using incompatible data types
For example, float and int, char and varchar, binary and varbinary are incompatible. Incompatibility of data types may prevent the optimizer from performing some optimization operations that could have been performed. E.g:
SELECT name FROM employee WHERE salary > 60000
In this statement, if the salary field is of money type, it is difficult for the optimizer to optimize it, because 60000 is an integer number. We should convert integer types to coin types during programming, instead of waiting for runtime conversion.
17. Make full use of connection conditions
In some cases, there may be more than one join condition between the two tables. At this time, write the join condition in the WHERE clause completely, which may greatly improve the query speed. E.g:
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO
SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO
The second sentence will execute much faster than the first sentence.
18. If you can use GROUP BY, you don't need DISTINCT
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10
Can be changed to:
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID
19. Don't use UNION if you can use UNION ALL
UNION ALL does not execute the SELECT DISTINCT function, which will reduce a lot of unnecessary resources.
20. Try not to use the SELECT INTO statement
The SELECT INTO statement will cause the table to be locked, preventing other users from accessing the table.
Previous：Optimize Report Access
