Toad World Blog

Rearranging the Driving Path

May 10, 2013 11:00:00 PM by Steve Hilker

This tip covers the importance of finding the best “driving path” for retrieving rows from the database.

We’ll use a simple illustration of a Nested Loop operation that retrieves data accessing 2 tables. Table A has 10,000,000 rows and Table B has 1,000.

TT_Optimization_RearrangingDrivingPath_Pic1.png-550x0

If SQL statement is rewritten so that the execution plan does a full table scan on Table A with its 10 million rows and an index (assuming a binary index tree with only two nodes with each parent) scan on Table B, this will result in 34,538,776 operations. If the SQL statement is rewritten so that the execution plan does the full table scan on Table B which has only one thousand rows and an index scan on Table A, this will result in 8,059 operations. If you write a SQL statement that accesses the larger table first, it is pretty easy to see that you would have a great performance improvement by just changing which table to used for the full table scan.

One of the SQL transformation rules used to rewrite the syntax of a SQL statement in Quest SQL Optimizer is to rearrange the “driving path” or order that the tables are accessed when retrieving data.

When a SQL statement accesses 2 tables with Nested Loop join, there are 2 ways you can join the tables. If it uses 3 tables, the number of ways you can join the tables is 3! (3*2*1) or 6, which means the optimization process could generate 6 SQL statements. If 6 tables are accessed in a SQL statement, the number of ways to join the tables is 6! which is 720 ways, so, the optimization process could generate 720 SQL statements using this one syntax transformation rule.  If the quota for SQL syntax (the Syntax transformation quota) is 100, then you can see that one rule which can generate 720 statements could use up the entire quota. The SQL optimizer engine has more than 60 other syntax transformation rules to apply to the original SQL statement. So this one rule for rearranging the driving path needs to be held in check so that not all the SQL alternatives are generating using only this one rule.

That is where the Table join permutation quota comes into play. It limits the number of attempts that the SQL Optimizer engine will try to find a different driving path.

TT_Optimization_RearrangingDrivingPath_Pic2.png-550x0

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.