50+ SQL Performance Optimization scenarios 4

Before the year ends I’d like to share some good stuff…

I have never seen a huge compilation of SQL tuning tips or rewrite scenarios (with test cases) and got them only on OracleFans forum… ooops… so you can’t read Chinese? try this translated version, whew.. good thing Google has this translate service that I am able to read in Chinese.. :)

So how did I end up on this forum? mm… I can’t really remember… I’m a curious wayward Googler, either I was having problems on a particular SQL on a client or I’m getting really curious about SQL Tuning and trying to get the whole idea (building up a mind map) by doing multiple reads..

Even the language barrier (Chinese) does not even stop me from digging good knowledge and I even subscribed & created accounts on some other similar forums.. Then luckily… I’ve stumbled upon this series! A guy named David wrote it.. I’ve already mailed him & thanked him for compiling this very useful good stuff but I did not get any response yet.. maybe I should translate my English to Chinese :p .. I’m just kidding..

So here it goes… just click on the links and you’ll be redirected to the translated version!
well regarding the accuracy of his findings… as a reader, I don’t believe instantly on all what he wrote… before you take in something there has to be some form of validation (you have to be some kinda scientist yourself)…

For those who have objections or if you feel that some claims are inaccurate or if you have done your own validation but the results are different from what is written, please do post your comment on that particular item or topic.. Your feedback is very valuable and it would be useful for other viewers too :)

If you are having problems with the links, you can check all the URL here


ORACLE SQL Performance Optimization Series (1)

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

ORACLE SQL Performance Optimization Series (2)

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 (3)

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 (5)

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

ORACLE SQL performance optimization Series (6)

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 (7)

24. Analysis of SQL statements with EXPLAIN PLAN

ORACLE SQL Performance Optimization Series (8)

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 (10)

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 (11)

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 (12)

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 (13)

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 (14)

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 (15)

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

ORACLE SQL Performance Optimization Series (16)

I used the view of how query results are wrong?

ORACLE SQL Performance Optimization Series (17)

Page Which writing efficient SQL?

ORACLE SQL Performance Optimization Series (18)

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

ORACLE SQL Performance Optimization Series (19)

ORACLE data type implicit conversions

ORACLE SQL Performance Optimization Series (20)

The use of INDEX should pay attention to the three questions
Includes date range example

ORACLE Tips (HINT) use (Part 1) (21)

ORACLE Tips (HINT) use (Part 2) (22)

Analysis of function-based index (Part 1) (23)

Includes date range example

Analysis of function-based index (Part 2) (24)

How to achieve efficient paging query (25)

ORACLE achieved in the SELECT TOP N method (26)





About these ads

4 comments

    • Thanks Marko! :) I’m also thinking of compiling them on one pdf file.. I’ll update this post once I’m finish with it..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s