SQL Performance Optimization scenarios:

ORACLE SQL Performance Optimization Series 

1. The types of ORACLE optimizer
2. The way to visit Table
3. Shared SQL statement

ORACLE SQL Performance Optimization Series 

4. Select the table name of the most efficient order (only in the effective rule-based optimizer)
5. WHERE clause in the order of the connections
6. SELECT clause to avoid using ‘*’
7. Access to the database to reduce the number of

ORACLE SQL Performance Optimization Series 

8. Using the DECODE function to reduce the processing time
9. Integration of simple, non-associated database access
10. Remove duplicate records
11. Alternative DELETE with TRUNCATE
12. As much as possible the use of COMMIT

ORACLE SQL Performance Optimization Series (4)

13. Calculate the number of records
14. Where clause with the HAVING clause to replace
15. To reduce the query table
16. Through an internal function to improve SQL efficiency

ORACLE SQL Performance Optimization Series 

17. Use the table alias (Alias)
18. Replace IN with EXISTS
19. Replace NOT IN with NOT EXISTS

ORACLE SQL performance optimization Series 

20. Connect with the table to replace EXISTS
21. Replace DISTINCT with EXISTS
22. Recognition ‘inefficient implementation of the’ in SQL statements
23. Use TKPROF tool to query SQL Performance Status

ORACLE SQL Performance Optimization Series 

24. Analysis of SQL statements with EXPLAIN PLAN

ORACLE SQL Performance Optimization Series 

25. With the index to improve efficiency
26. Operation index

ORACLE SQL Performance Optimization Series (9)

27. The choice of the basis of the table
28. Number of equal index
29. Comparing and scope of the comparison equation
30. The index level is not clear

ORACLE SQL Performance Optimization Series 

31. Force index failure
32. Avoid the use of columns in the index calculation.
33. Auto Select Index
34. Avoid the use of NOT in the index column
35. With “= substitute”

ORACLE SQL Performance Optimization Series 

36. UNION replaced with the OR (for the index column)
37. To replace the OR with the IN
38. Avoid the use of columns in the index IS NULL and IS NOT NULL

ORACLE SQL Performance Optimization Series 

39. Always use the first column index
40. ORACLE internal operations
41. With the UNION-ALL replaced UNION (if possible)
42. Usage Tips (Hints)

ORACLE SQL Performance Optimization Series 

43. WHERE replaced with ORDER BY
44. Avoid changing the index of the column type
45. Need to be careful of the WHERE clause

ORACLE SQL Performance Optimization Series 

46. Connect multiple scan
47. CBO to use a more selective index of
48. Avoid the use of resource-intensive operations
49. GROUP BY Optimization
50. Use Date
51. Use explicit cursor (CURSORs)
52. Optimization EXPORT and IMPORT
53. Separate tables and indexes

ORACLE SQL Performance Optimization Series 

EXISTS / NOT EXISTS must be better than IN / NOT IN the efficiency of high?

ORACLE SQL Performance Optimization Series 

I used the view of how query results are wrong?

ORACLE SQL Performance Optimization Series 

Page Which writing efficient SQL?

ORACLE SQL Performance Optimization Series 

COUNT (rowid) / COUNT (pk) the efficiency of high?

ORACLE SQL Performance Optimization Series 

ORACLE data type implicit conversions

ORACLE SQL Performance Optimization Series