3 ms·
I've used CTEs in Oracle and used judiciously they save a great deal of faffing about.
by jacques_chester 14y ago
I've used CTEs in Oracle and used judiciously they save a great deal of faffing about.
- einhverfr 14y agoActually the trial balance example came about because of addressing dependencies in join conditions that would have made it very difficult to address the same thing without it. I haven't tested it for large query sets but the idea is that usually you won't need to have a CTE covering more than about 14 months of data (assuming books are closed 2 months after fiscal end of year), or will only cover a small part of the data over a longer period. Of course for some businesses 14 months of data is still a lot of data for a CTE, maybe a couple hundred thousand rows, but given enough memory, PostgreSQL can be tuned for this.
- jacques_chester 14y agoYeah -- it's definitely a "pro tip" that you should cut down the CTE as much as possible. But a smart query planner can actually fold the CTE efficiently into the larger query plan and migrate where conditions up into the CTE and perform a union. This is why CTEs are better than temporary tables, for example.