Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Martin Berg's "Query tuning by eliminating throwaway"

Martin Berg's "Query tuning by eliminating throwaway"

From: Mladen Gogala <>
Date: Tue, 10 Feb 2004 11:49:20 -0500
Message-ID: <>

I've downloaded and read the paper, thanks to Mogens and Cary and my impressions are that the paper has some good ideas but is neither revolutionary nor overly applicable. Basically, the basic idea of the paper is that people usually process many rows more then necessary to produce the desired output. The excess rows are called "throwaway", thus the name of the article. The author then analyzes the throwaway caused by each access method (as of oracle 8.0.4, with notable exceptions of bitmap methods, star schema and hash) and comes to the conclusion that the only cure is to properly index tables, so that predicates are resolved by using index scans. The problem is, in my opinion, directly the opposite: how to design database schema in order to be able to write queries that execute quickly. To that end, I found more useful material in Ralph Kimball's book "The Data Warehouse Toolkit" and in Jonathan's and Tom Kyte's books then in Martin's article. I must confess that the whole debate made me very curious about Dan's book and that I ordered it from Barnes & Noble, but as I am busy with the 10g, it will have to wait at least 4 to 6 weeks.
I am not writing this to denigrate Martin's effort, but to basically point out that Anjo's, Cary's and Jonathan's method based on the wait inerface, together with the business knowledge (one must understand what is it that he or she wants to accomplish, in the first place) is the ultimate in SQL tuning. There is no easy method that will take a horrendous query, which would justify the capital punishment for the author, insert it into a "method", and then following few easy steps, end up with a missile which will execute in milliseconds. If someone wants to find number of AT&T subscribers per state, he or she will have to do something like:

        SELECT COUNT(*) FROM SUBSCRIBERS GROUP BY STATE; Given the number of subscribers, it will be a big query and there is no room for improvement. What I see as my role is to prevent design which would result in splitting the subsciber entity into several tables, therefore making the query above into a join. If that happens, no amount of methodical tuning the SQL will help.

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Tue Feb 10 2004 - 10:49:20 CST

Original text of this message