Oracle SQL Optimization

1. Use Oracle built-in functions or the effects that can be achieved by using their functions. Use Oracle functions as much as possible because their functionsAlgorithmAnd the language used to implement functions are very efficient, which is generally more efficient than what we write.

2. SQL keywords are in uppercase, because Oracle converts all SQL statements to uppercase for SQL Execution.

3. It is recommended that the SELECT statement not contain *. You need to write a specific column name. Otherwise, Oracle will parse each field at a time, which takes a long time.

4. for a from table, if it is one or two tables, put the least number behind it. If it is more than three tables, put the table with other crosstab values behind it, it means to be associated with other tables, because Oracle scans from the Internet to the left, so that the table can be scanned as little as possible,

5. Place the where condition that can filter out multiple data records in the future, and so on.

6. The best way to delete duplicate records is to use rowid.

7. Use the decode function to reduce processing time

8. Use truncate table to replace the delete from table name, because the first table will delete the table's tablespace.

9. Use Where to filter, and use having less.

10. Using table aliases can also improve efficiency.

11. Replace in with exists and not exists instead of not in:

12. index is used to improve the query efficiency, but the index will be created very well. Not every field is created, which will reduce the efficiency.

13. Replace distinct with exists

14. Avoid using the computed functions on the index.

15. Replace with> =>. Because there is an exact edge, Oracle can be followed to determine the edge.

16. Avoid using not in the index column, because this will invalidate the index and avoid using is null or is not null.

17. Use union to replace or operations. If yes

18. replace or with in, if possible