March 30, 2010
(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)
A continuation of the concept of yesterday’s true or false quiz – this one is a little easier, with new reading material. Today’s quiz is on the topic of improving performance of SQL statements. The reading material:
Article 1
Article 2
Article 3
Article 4
Article 5
Article 6
Article 7
Article 8 (actually a book)
Article 9 (actually a book)
Article 10 (actually a book)
Article 11
Please read the above articles and see if you are able to answer the following true or false questions, assuming that the questions apply to a release of Oracle that has been available for up to five years. State why you believe that the answer to the question is true, or why you believe that the answer to the question is false. Any answers that attempt to utilize logical fallacies will be scored as incorrect. (Most of these questions can be answered with the help of the first couple of links.)
1. Queries containing subqueries should be rewritten as a logical step in improving query performance.
2. Complex queries should be decomposed into multiple queries using global temp tables and/or WITH clauses as a step toward improving query performance.
3. Significant performance improvements, possibly 20 fold, are possible by replacing some or all of a SQL statement with PL/SQL. If true, provide an example showing a significant performance improvement with such a replacement. If not true, provide an example that shows that performance did not improve significantly.
4. There are cases were performance improvements are possible by modifying a SQL statement containing a three table equijoin, which accesses primary and foreign keys columns of the tables, into a SQL statement which accesses a single table with two PL/SQL functions (each containing a SELECT) in column positions.
Charles Hooper's Oracle Notes 
As its more fun than waiting for yet another test cycle to complete:-
Number 2:
As an old friend form Glasgow used to say Maybe is Aye Maybe is Naw.
Usually though I use the with clause or GTT to simplify a complex problem, either because the syntax of the query is horrible or I can’t figure out a single query to get the result I want (this is a pretty good indication of either a bad design or a bad specification). I have used the With clause when performance tuning but usually in combination with something else (analytics in this example http://forums.oracle.com/forums/thread.jspa?messageID=2187453�) here the design is so bad and the subquery used so often that the cost of writting the factored query to temp then reading it back into the buffer cache is worthwhile.
The other case I have used this for performance is when the optimiser couldn’t find a good plan not because of the subquery but because the data was being pulled from a complex underlying view factoring out the subquery resulted in a better plan, currently I don’t have a test case for it though building one and figuring out exactly what happened is in my todo list so its currently filed under Voodoo tuning methods.
Chris
Well stated.
Many of these True/False questions are stated as implied absolutes, so part of the challenge in answering the questions is trying to find conditions that lead to the statement evaluating as false. Take, for instance, question #1. If you follow the documentation link for subqueries, you will find that some of the approaches suggested in the articles for removing subqueries actually generate another form of a subquery. I personally do not consider an inline view to be a subquery, but the Oracle documentation states that it is a subquery. Another possible reason for the question to evaluate to false involves the automatic query rewriting capability in Oracle 9i and above – is it a logical step to always perform the transformation – Oracle 10g and above suggests not.
[…] Hooper posted a 3-part series with seemingly innocent True/False questions. He covers sorting, SQL tuning and wait […]
#3: Probably false 99% of the time. Article 5 implies that it is true, article 6 implies that it is false.
#4: Probably false 99% of the time. This suggestion was found in article #9, a point that I mentioned when I wrote a review for that book.
Blue moons and double-headed coins happen on occasion too.