A nice little trick on Oracle 11G is using XMLQuery as an eval function:

SYS@LAB> l  1  select substr( sys_connect_by_path( level, '*' ), 2 ) || ' = ' ||  2         XMLQuery( substr( sys_connect_by_path( level, '*' ), 2 ) RETURNING CONTENT).getnumberval() product  3       , substr( sys_connect_by_path( level, '+' ), 2 ) || ' = ' ||  4         XMLQuery( substr( sys_connect_by_path( level, '+' ), 2 ) RETURNING CONTENT).getnumberval() sum  5  from dual  6* connect by level < 6SYS@LAB> /
PRODUCT                   SUM------------------------- -------------------------1 = 1                     1 = 11*2 = 2                   1+2 = 31*2*3 = 6                 1+2+3 = 61*2*3*4 = 24              1+2+3+4 = 101*2*3*4*5 = 120           1+2+3+4+5 = 15

Anton

 

Marco’s first performance  investigation:

== First Time ==

Execution Plan----------------------------------------------------------Plan hash value: 1236776825

-----------------------------------------------------------------------------| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 ||*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          ||   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

   1 - filter(LEVEL<6)

Statistics----------------------------------------------------------       2526  recursive calls        910  db block gets       1971  consistent gets        171  physical reads      16816  redo size        834  bytes sent via SQL*Net to client        407  bytes received via SQL*Net from client          4  SQL*Net roundtrips to/from client        191  sorts (memory)          0  sorts (disk)          5  rows processed

== Second Time ==

Execution Plan----------------------------------------------------------Plan hash value: 1236776825

-----------------------------------------------------------------------------| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |-----------------------------------------------------------------------------|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 ||*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          ||   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------

   1 - filter(LEVEL<6)

Statistics----------------------------------------------------------         71  recursive calls        900  db block gets        169  consistent gets          0  physical reads          0  redo size        834  bytes sent via SQL*Net to client        407  bytes received via SQL*Net from client          4  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)          5  rows processed

 

Disclaimer

The information demonstrated and shared here is based on Oracle beta software. The following is intended to outline Oracle’s general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.