> works w/o a procedure/functions

And what exactly is the problem with creating procedures or functions? I often see this wheeled out as an excuse for bizarre or arcane SQL. In any case we are not talking about creating 'functions' we are proposing creating *one* PL/SQL function to support this functionality everywhere.

I don't know why you think CUBE is simple. A pipelined function returning n rows is fast, results in rather less code and can be used flexibly and generically in SQL and PL/SQL passing only a parameter (i.e. bind variable) of the number of rows required. To generate different numbers of rows with CUBE requires that you physically change the query which would typically require that you re-code it every time and use dynamic SQL in PL/SQL and all that that entails. On top of that the pipelined function returns exactly the right amount of rows whereas your solution with CUBE would return significantly more rows (the next power of two greater?) than required and then filter the result set.

Even if it was simple, CUBE *sucks* at synthesizing rows. It might make for an interesting little 'I read AskTom' interview trick for SELECTing lots of rows from dual but that does not translate into it being efficient at synthesizing lots of rows. Surprisingly the cost appears to be mainly in the parsing overhead - to parse a query with a twelve dimensional CUBE on dual took me a staggering 37 seconds (this cube would return only 4096 rows). Your example is not fast because it uses CUBE, rather it is fast because it generates moderately small result sets with CUBE and then generates a cartesian product (the fast bit) of the two. Hence if you use CUBE you need to keep the number of dimensions low and create a product from multiple CUBEs for efficiency (i.e. join CUBE, CUBE, CUBE in a cartesian product). In which case CUBE is of little or no value since it is just as easy to use a small integer table, UNION ALL of dual or TABLE function and join to it repeatedly to create a product.

Since pipelined table function returns 4 million rows in approx. 3 seconds quite frankly I see little reason to get more complicated.