If you work anywhere near a PeopleSoft product you know it’s common practice to write effective date queries like so:
That’s fine. PeopleSoft actually generates queries like this. Oracle’s query analyzer will show that this has a cost of 516.
If we re-work that query to join a relation that contains the effdt we want (instead of the correlated subquery to find it for each row),
it brings the cost down to 12—an order of magnitude lower.
Big deal, right? This is working on a dinky table, who cares?
It gets worse
It starts to matter when you’re dealing with millions of records. In those cases, the effects are magnified. Consider
this query against ps_acad_prog (6.9 million rows for this sample institution):
Pretty standard stuff. This indicates a execution cost of 19,254,013. It’s a big table after all.
Using our same method from above, the same results can be returned much faster:
This query costs only 38,171. That’s not just much lower. Thats three orders of magnitude lower.