Written by Rene Woody
This blog is a
continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer
for Oracle. It 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.
If a 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 scan (assuming a binary index tree with only two nodes with each parent) 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 be 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.