Suggestions on Oracle to Improve sql Execution Efficiency
- 2021-10-11 19:56:36
- OfStack
-->FROM Clause contains multiple tables , Select the table with the least number of records as the basic table
--> Analyse WHERE Clause is bottom-up Filter conditions should be in order
-->ORACLE Will '*' Convert to column name
-->DELETE Will be in rollback segment Store recoverable information in, you can try TRUNCATE
-->COMMIT Will release :1.rollback segment
2. Locks acquired by program statements
3.redo log buffer
--> Put Alias Prefixed with each Column You can reduce the parsing time on
-->ORACLE Analyse sql Statement will convert lowercase letters to uppercase letters before executing
--> Use on index columns NOT Will stop using the index and perform a full table scan instead
--> Operating on an indexed column stops using the index and performs a full table scan instead
--> Use '>=', Instead of '>'
-->where Clause '!=' , '||' , '+' Symbols such as these will destroy the index
-->IN The efficiency of is very low ,IN Clause executes the 1 Internal sorting and merging
-->EXIST Query is faster
--> For two index columns ,UNION Ratio OR More efficient
-->IS NULL Will destroy the index
-->DISTINCT,UNION,MINUS,INTERSECT,ORDER BY With, etc SORT Function , Will consume resources
--> When you have more than one index column, try to use the 1 Index columns
--> Use as much as possible UNION ALL Substitute UNION
--> Don't use TO_NUMBER() Change the type of the indexed column.
--> For indexed columns of character type, try to write: col = '123' Instead of col = 123, The latter will be parsed as TO_NUMBER(EMP_TYPE)=123
--> Use the appropriate keywords where appropriate: HAVING The result set is filtered after all records are retrieved Be able to use WHERE Don't use it when solving it HAVING
--> Indexes also need to be maintained ,INSERT,DELETE,UPDATE Will be made more times due to the index I/O
--> It is necessary to reconstruct the index: ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME> [ONLINE]