Use what you learn

Way back, way way back I remembered reading about the "WITH query_name (select ...) subq select * from subq", which oracle calls Subquery Factoring I remember thinking at the time "that's neat" and then of course promptly forgot about it. Well, today I spent about 3 hours trying to optmize an audit report on changes to employees in our payroll system. A huge nasty bit of SQL that made numerous FTS into the audit table and the employee table to gather the audit information the way the users demanded it. I tried materialized views, rewriting the queries, even considered pipelined functions, then for some weird reason the old noodle remembered the subqueries - 1 minute to find it in the SQL reference guide, another 5 to rebuilt the SQL. Cost on the query cut from over 30,000 to 543. The time of the query, cut from over 8 minutes to under 5 seconds. This is a direct copy/paste straight out of the 10G SQL reference manual:

 Subquery Factoring: 
Example The following statement creates the query names dept_costs 
and avg_cost for the initial query block containing a join, and then 
uses the query names in the body of the main query. 

WITH dept_costs AS ( SELECT department_name, SUM(salary) dept_total 
 FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name)
 , avg_cost AS ( SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs) 

SELECT * FROM dept_costs 
 WHERE dept_total > (SELECT avg FROM avg_cost) 
 ORDER BY department_name; 

DEPARTMENT_NAME                DEPT_TOTAL 
------------------------------ ---------- 
Sales                          313800 
Shipping                       156400 

Nothing new or earth shattering here... just a comment on how the brain works.