--sql connect by
bill@ORCL> select id, text
2 from t1
3 connect by prior id=id and level<=length(text)-length(replace(text, chr(10))) and prior dbms_random.value>0;
已选择 12800 行。
已用时间: 00: 00: 06.81
执行计划
----------------------------------------------------------
Plan hash value: 3874795171
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 186 | 366K| 68 (0)| 00:00:01 |
|* 1 | CONNECT BY WITHOUT FILTERING| | | | | |
| 2 | TABLE ACCESS FULL | T1 | 186 | 366K| 68 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=PRIOR "ID")
filter(LEVEL<=LENGTH("TEXT")-LENGTH(REPLACE("TEXT",' ')) AND PRIOR
"DBMS_RANDOM"."VALUE"()>0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
313 consistent gets
0 physical reads
0 redo size
236099 bytes sent via SQL*Net to client
9927 bytes received via SQL*Net from client
855 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
12800 rows processed
--sql笛卡尔
bill@ORCL> select id, text
2 from t1,
3 (select rownum n from dual connect by rownum<=64) b;
已选择 12800 行。
已用时间: 00: 00: 05.00
执行计划
----------------------------------------------------------
Plan hash value: 894562235
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 186 | 366K| 70 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN | | 186 | 366K| 70 (0)| 00:00:01 |
| 2 | VIEW | | 1 | | 2 (0)| 00:00:01 |
| 3 | COUNT | | | | | |
|* 4 | CONNECT BY WITHOUT FILTERING| | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | BUFFER SORT | | 186 | 366K| 70 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | T1 | 186 | 366K| 68 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(ROWNUM<=64)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
378 consistent gets
0 physical reads
0 redo size
280133 bytes sent via SQL*Net to client
9927 bytes received via SQL*Net from client
855 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
12800 rows processed
--管道表函数
bill@ORCL> select id, item_txt
2 from table(refcur_pkg.f_cartesian2(cursor(
3 select id, length(text)-length(replace(text, chr(10))), text
4 from t1
5 )));
已选择 12800 行。
已用时间: 00: 00: 05.16
执行计划
----------------------------------------------------------
Plan hash value: 2991304848
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 271K| 29 (0)| 00:00:01 |
| 1 | VIEW | | 8168 | 271K| 29 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN2 | 8168 | | 29 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 186 | 366K| 68 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
统计信息
----------------------------------------------------------
266 recursive calls
0 db block gets
529 consistent gets
0 physical reads
0 redo size
236103 bytes sent via SQL*Net to client
9927 bytes received via SQL*Net from client
855 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12800 rows processed
bill@ORCL> select id, item_txt
2 from table(refcur_pkg.f_cartesian3(cursor(
3 select id, length(text)-length(replace(text, chr(10))), text
4 from t1
5 )));
已选择 12800 行。
已用时间: 00: 00: 00.29
执行计划
----------------------------------------------------------
Plan hash value: 2831580648
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 271K| 29 (0)| 00:00:01 |
| 1 | VIEW | | 8168 | 271K| 29 (0)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| F_CARTESIAN3 | 8168 | | 29 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 186 | 366K| 68 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
统计信息
----------------------------------------------------------
278 recursive calls
0 db block gets
555 consistent gets
0 physical reads
124 redo size
228407 bytes sent via SQL*Net to client
9927 bytes received via SQL*Net from client
855 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12800 rows processed