Wednesday, May 31, 2006
Tuesday, May 30, 2006
1 IN (SELECT 1 FROM DETAIL WHERE
AND DETAIL.HEADERID = '3432223'
AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR'))
AND HEADERID IN (SELECT
AND DETAIL.HEADERID = '200504505'
AND DETAIL.status in ('DKAPPR','DFFAPPR','UIUAPPR')
Wednesday, May 24, 2006
Sunday, May 21, 2006
Tuesday, May 09, 2006
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.