I'm quite a fan of row generators. If I know I need a row for every day of the year for example, I generally avoid holding (and maintaining) a table of these dates, and instead will generate them. This is by no means a universal rule, so take each case on its merits.select trunc(sysdate,'YYYY') + level -1 dt
from dual
connect by level <= add_months(trunc(sysdate,'YYYY'),12) - trunc(sysdate,'YYYY');You can see a few methods (of many) of generating rows on one of my previous posts. The issue with generating rows in this manner is that the optimizer estimates cardinalities based on the number of rows in the table. Which for dual is... 1.So when we look at the execution plan for such a query, we see this.SQL_ID 16r2my83pj187, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ trunc(sysdate,'YYYY') + level -1 dt
from dual connect by level <= add_months(trunc(sysdate,'YYYY'),12) -
trunc(sysdate,'YYYY')
Plan hash value: 1236776825
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 365 |00:00:00.01 | | | |
| 1 | CONNECT BY WITHOUT FILTERING| | 1 | | 365 |00:00:00.01 | 2048 | 2048 | 2048 (0)|
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | | | |
----------------------------------------------------------------------------------------------------------------We thought we would get just 1 row back - but we in fact got 365. Once you start joining out to other tables, the magnitude of that soon becomes more prominent. Let's say I want to generate a row for each employee who is a manager, for each day of the year.Read More
