Refactoring SQL

I have never seen an exhaustive list like the sample you provided.

The most effective way to refactor sql that I have seen is to use the with statement.It allows you to break the sql up into manageable parts, which frequently can be tested independently. In addition it can enable the reuse of query results, sometimes by the use of a system temporary table. It is well worth the effort to examine.

Here is a silly example

WITH mnssnInfo AS(    SELECT SSN,            UPPER(LAST_NAME),            UPPER(FIRST_NAME),            TAXABLE_INCOME,                     CHARITABLE_DONATIONS    FROM IRS_MASTER_FILE    WHERE STATE = 'MN'                 AND -- limit to Minne-so-tah          TAXABLE_INCOME > 250000      AND -- is rich           CHARITABLE_DONATIONS > 5000      -- might donate too),doltishApplicants AS(    SELECT SSN, SAT_SCORE, SUBMISSION_DATE    FROM COLLEGE_ADMISSIONS    WHERE SAT_SCORE < 100          -- Not as smart as the average moose.),todaysAdmissions AS(    SELECT doltishApplicants.SSN,            TRUNC(SUBMISSION_DATE)  SUBMIT_DATE,            LAST_NAME, FIRST_NAME,            TAXABLE_INCOME    FROM mnssnInfo,         doltishApplicants    WHERE mnssnInfo.SSN = doltishApplicants.SSN)SELECT 'Dear ' || FIRST_NAME ||        ' your admission to WhatsaMattaU has been accepted.'FROM todaysAdmissionsWHERE SUBMIT_DATE = TRUNC(SYSDATE)    -- For stuff received today only

One of the other things I like about it, is that this form allows you to separate the filtering from the joining. As a result, you can frequently copy out the subqueries, and execute them stand alone to view the result set associated with them.

There is a book on the subject: "Refactoring Databases". I haven't read it, but it got 4.5/5 stars on Amazon and is co-authored by Scott Ambler, which are both good signs.

Not that I've ever found. I've mostly done SQL Server work and the standard techniques are:

• Parameterise hard-coded values that might change (so the query can be cached)
• Review the execution plan, check where the big monsters are and try changing them
• Index tuning wizard (but beware you don't cause chaos elsewhere from any changes you make for this)

If you're still stuck, many reports don't depend on 100% live data - try precalculating portions of the data (or the whole lot) on a schedule such as overnight.