In my last blog, I use two tables join to illustrate the simple path control, now let’s use a three table join SQL statement to demonstrate a more complicated scenario. Let’s assume that A.key, B.key and C.key are all indexed.
|
select * from A,B,C
where A.key = B.key
and B.key = C.key
|
|
For a three table join SQL statement, the database SQL optimizer will consider the total permutations which are 3!=6 for nested loop join. Assume that B→A→C is the lowest cost path and therefore is the path selected by database SQL optimizer. How can we guide the database SQL optimizer to select a preferred path by rewriting the SQL syntax? If we want to guide the database SQL optimizer to consider a path from A→B→C, we can try the following syntax:
|
select * from A,B,C
where A.key + 0 = B.key
and B.key + 0 = C.key
|
|
By changing the syntax to A.key + 0 and B.key + 0, three of the six table join permutations have an increase in cost: C→B→A, A→C→B and B→A→C. This leaves three remaining paths available for the SQL database optimizer to consider A→B→C, B→C→A, and C→A→B. It will only select the path of our choice, A→B→C, if the estimated cost is the lowest cost, otherwise, the database SQL optimizer will opt for some other path. Normally, I will recommend our clients to disable the original bad path which was selected by database optimizer and allow more room to database optimizer to correct the mistake by itself from the remain paths, unless your are really sure the path you think is the best among all other paths, please don’t try to fix a path by adding more dummy operation to the SQL statement.
With the lowering of the cost of today’s CPU and memory, the database SQL optimizer designers are able to lower the cost of Hash and Sort Merge joins which use more processing power and memory than the Nested Loop join. This means that database SQL optimizer will more often select the Hash or Sort Merge join instead of taking the risk to do Nested Loop join especially when the table size is small.
Let’s review the following rewritten SQL which also contains an external variable “A.f1=:VAR”:
|
select * from A,B,C
where A.key + 0 = B.key
and B.key + 0= C.key
and A.f1 = :VAR
|
|
Since the database SQL optimizer always assumes that an external variable will narrow down the first result set return from this table, the path VAR→A→B→C should normally estimated with the lowest cost.
Again, In a real life situation, the database SQL optimizer may not actually select the expected Nested Loop path when you change the syntax, new syntax would result in increasing the cost of the original Nested Loop path, but the database SQL optimizer may select second lowest cost execution plan which could be a Hash join or a Sort Merge instead of the Nested Loop execution plan.
Basically, SQL tuning is a try and error game, different database vendors have their own SQL optimization characteristic, it is not necessary in a systematic design, furthermore; some behaviors are only the result of the designers/programmers personally interest. Around 12 year ago, I wondered why I have to spend so much time to understand their personally interest? So, I started the research of using AI algorithm to do the try and error game for me, LECCO SQL Expert was the first product which combines a sophisticated SQL parser technology (actually, I have to design a special SQL parser for it) and AI recursive searching engine. Today, it is available in Toad SQL Tuning model; this tool automates the whole process so that you can save your valuable time while letting the tool do the try and error game for you.