Oracle 11G: XMLQuery = eval startup 594127 1280

Oracle 11G: XMLQuery = eval

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

SQL>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 < 6
SQL>/
PRODUCT                   SUM
------------------------- -------------------------
1 = 1                     1 = 1
1*2 = 2                   1+2 = 3
1*2*3 = 6                 1+2+3 = 6
1*2*3*4 = 24              1+2+3+4 = 10
1*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.

Tags:,

6 Comments

  1. Laurent Schneider July 31, 2007
  2. Andre July 31, 2007
  3. Marco Gralike July 16, 2007
  4. Marco Gralike July 16, 2007
  5. Laurent Schneider July 15, 2007
  6. Lucas Jellema July 14, 2007