Periodically I find code executed on databases I support
that is used to generate x number of extra rows, normally to produce a set of
literal values as a table. For example,
recently I found the following embedded as an inline view in a rather large query:
(SELECT LAST_DAY(ADD_MONTHS(:in_date, -1))
work_date, -1 rws_back
FROM dual UNION
SELECT
LAST_DAY(ADD_MONTHS(:in_date, -2)), -2
FROM dual UNION
SELECT
LAST_DAY(ADD_MONTHS(:in_date, -3)), -3
FROM
dual UNION
...
SELECT
LAST_DAY(ADD_MONTHS(:in_date, -13)), -13
FROM dual) in_dt_view
The idea was to generate a 13-row table, with each row
holding a date counting backwards 1 month from the previous row. Those 26 lines of SQL could have easily been
written as:
(SELECT /*+ CARDINALITY(d 13) */ LAST_DAY(ADD_MONTHS(sysdate,
(level*-1))) work_date
, (level*-1) rws_back
FROM
dual d
CONNECT BY level <= 13) in_dt_view
Using "CONNECT BY level <=" is a handy way of
generating x number of rows for any number of purposes, not only in situations
like the above one but also for generating data when conducting tests. This change isn't meant to tune the query (it
does take a smidge less Shared Pool space) but make it easier to write (imagine
if they needed 48 months back in time).
Using "CONNECT BY level <=" isn't something new as there are many examples of this technique on the internet. But for whatever reason it doesn't seem to be used that often in production code, instead mostly in code for generating data and/or presentations.
But why the CARDINALITY hint? Without it the optimizer thinks only 1 row
will be returned as DUAL has only 1 row and the optimizer doesn't try to
determine the row count generated by "CONNECT BY …". Tom Kyte has a great discussion about this hint at http://tinyurl.com/4x2s7zk.
