xmltable-connectby

gistfile1.pls

SQL> alter system flush shared_pool;

System altered.

SQL> @tests/sy1.sql
SQL> variable from_n number
SQL> variable to_n number
SQL> exec :from_n := 100;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL> exec :to_n := 2000;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
SQL>
SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

Elapsed: 00:00:00.12
SQL>
SQL> select count(*) from (
  2  select  /*+ no_merge */ ceil((-1 + sqrt(1 + 4 * ((:from_n - 1) * (:from_n - 1) - (:from_n - 1) + 2 * (:from_n + level - 1)))) / 2) v
  3    from  dual
  4    connect by level < = (:from_n + :to_n) * (:to_n - :from_n + 1) / 2
  5  );

  COUNT(*)
----------
   1996050

Elapsed: 00:00:01.21
SQL> select count(*)
  2    from (
  3         select/*+ no_merge no_xml_query_rewrite */ i
  4         from xmltable(:from_n || ' to ' || :to_n columns i int path '.') t1
  5             ,xmltable('1 to xs:integer(.)' passing t1.i columns j for ordinality) t2
  6   );

  COUNT(*)
----------
   1996050

Elapsed: 00:00:00.94
SQL> set echo off timing off;
SQL> @sta 38xa64udbsx5a  06f3dbgfr3z8p

      INST SQL_ID         CH# PARENT_HANDLE    OBJECT_HANDLE       PLAN_HV         HV Profile/Baseline/Patch               ELAEXE        CNT    FETCHES   EO_FTCHS FIRST_LOAD_TIME      PARSE_CALLS DISK_READS BUFFER_GETS DIRECT_WRITES   CPU_TIME    IO_WAIT   APP_WAIT CONCURRENCY    PLSQL_T JAVA_EXEC_T ROWS_PER_EXEC OPT_MODE           COST   ENV_HASH P_SCHEMA             PROGRAM_ID PROC_NAME                       PROC_LINE
---------- ------------- ---- ---------------- ---------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ---------- -------------------- ----------- ---------- ----------- ------------- ---------- ---------- ---------- ----------- ---------- ----------- ------------- ------------ ---------- ---------- -------------------- ---------- ------------------------------ ----------
         1 06f3dbgfr3z8p    0 000000008F57A838 000000007E235F20 3039852489 3715235093                                      .79388          1          1          1 2015-03-29/23:20:03            1         49        1977             0        .76    .001247          0           0          0           0             1 ALL_ROWS         237293 3179701485 SYS                           0                                         0
         1 38xa64udbsx5a    0 00000000872F5478 000000008D4F6760 2919102380 2596041898                                     1.11019          1          1          1 2015-03-29/23:20:02            1          1          25             0      1.092    .000056          0           0          0           0             1 ALL_ROWS              2 3179701485 SYS                           0                                         0

SQL> @testu 06f3dbgfr3z8p
SP2-0310: unable to open file "testu.sql"
SQL> @textu 06f3dbgfr3z8p
################################  Original query text:  ################################################

QTEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*)
  from (
       select/*+ no_merge no_xml_query_rewrite */ i
       from xmltable(:from_n || ' to ' || :to_n columns i int path '.') t1
           ,xmltable('1 to xs:integer(.)' passing t1.i columns j for ordinality) t2
 )

################################  Original query text End #############################################
SQL> @textu 38xa64udbsx5a
################################  Original query text:  ################################################

QTEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select count(*) from (
select  /*+ no_merge */ ceil((-1 + sqrt(1 + 4 * ((:from_n - 1) * (:from_n - 1) - (:from_n - 1) + 2 * (:from_n + level - 1)))) / 2) v
  from  dual
  connect by level < = (:from_n + :to_n) * (:to_n - :from_n + 1) / 2
)

################################  Original query text End #############################################