Suggestions on improving SQL Execution efficiency in Oracle

SQL Execution efficiency has always been a concern. How can we improve it? If you have any better methods, I would like to share with you some good suggestions. If you are interested, please refer to them. I hope to help you copy the Code as follows: -- When the FROM clause contains multiple tables, select the table with the least number of records as the base table -- resolve WHER

SQL Execution efficiency has always been a concern. How can we improve it? If you have any better methods, I would like to share with you some good suggestions. If you are interested, please refer to the following code for help: -- When the FROM clause contains multiple tables, select the table with the least number of records as the base table -- resolve WHER

SQL Execution efficiency has always been a concern. How can we improve it? If you have any better methods, I would like to share some good suggestions with you. If you are interested, please refer to them and hope to help you.

The Code is as follows:
--> When the FROM clause contains multiple tables, select the table with the least number of records as the base table.

--> Parsing the WHERE clause requires a bottom-up filter condition in order.

--> ORACLE converts '*' To a column name.

--> DELETE stores recoverable information in rollback segment. You can try TRUNCATE.

--> COMMIT will be released: 1. rollback segment
2. Locks obtained by Program Statements
3. redo log buffer

--> Prefix Alias on each Column to reduce resolution time

--> When ORACLE parses SQL statements, it converts lowercase letters into uppercase letters and then executes them.

--> Using NOT on the index column will stop using the index and then perform full table scan.

--> Performing operations on the index column will stop using the index and then perform full table scan.

--> Use '> =' instead of '>'

--> '! = ',' | ',' + ', And other symbols will damage the index.

--> The efficiency of IN is very low. The IN clause will execute an internal sorting and merging.

--> Faster EXIST Query

--> For two index columns, UNION is more efficient than OR.

--> Is null will damage the index

--> DISTINCT, UNION, MINUS, INTERSECT, and order by all have the SORT function, which consumes resources.

--> Use the first index column whenever possible for multiple index Columns

--> Try to replace UNION with UNION ALL

--> Do not use functions such as TO_NUMBER () to change the index column type.

--> For character-type index columns, try to write as follows: col = '000000' instead of col = 123. The latter will be parsed as TO_NUMBER (EMP_TYPE) = 123.

--> When appropriate, use the appropriate Keyword: HAVING will filter the result set after all records are retrieved. If the WHERE clause is used, do not use HAVING.

--> Indexes also need to be maintained. INSERT, DELETE, and UPDATE will make more I/O operations for indexes.

--> Rebuilding an INDEX is necessary: ALTER INDEX REBUILD [ONLINE]

For details, refer: