1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
|
drop table t1
/
drop sequence s1
/
drop type jpl_table_type
/
drop type jpl_scalar_type
/
create sequence s1
/
create table t1 as
select
level id,
mod(level-1,10) group_1,
lpad(mod(level-1,10),10,'0') group_2,
trunc((level-1)/10) data1,
rpad('x',100) padding
from
dual
connect by level <= 100000
/
create type jpl_scalar_type as object(
group_1 number,
group_2 varchar2(10),
seq_id number
);
/
create type jpl_table_type as table of jpl_scalar_type;
/
create or replace function pipe_fun
return jpl_table_type
pipelined
as
begin
for r in (
select group_1, group_2, s1.nextval seq_id
from (
select distinct group_1, group_2
from t1
order by group_1, group_2
)
)
loop
pipe row (jpl_scalar_type( r.group_1, r.group_2, r.seq_id));
end loop;
return;
end;
/
break on seq_id skip 1
column padding noprint
set timi on
Select * from
(select
/*+ ordered use_hash (t) */
v.seq_id,
t.group_1,
t.group_2,
t.id,
t.data1,
t.padding
from
table(pipe_fun) v,
t1 t
where
t.group_1 = v.group_1
and t.group_2 = v.group_2
and rownum < 10
order by
v.seq_id, t.group_1, t.group_2, t.id
)
where rownum <= 10
/
SEQ_ID GROUP_1 GROUP_2 ID DATA1
---------- ---------- ---------- ---------- ----------
1 0 0000000000 1 0
2 1 0000000001 2 0
3 2 0000000002 3 0
4 3 0000000003 4 0
5 4 0000000004 5 0
6 5 0000000005 6 0
SEQ_ID GROUP_1 GROUP_2 ID DATA1
---------- ---------- ---------- ---------- ----------
7 6 0000000006 7 0
8 7 0000000007 8 0
9 8 0000000008 9 0
9 ligne(s) sÚlectionnÚe(s).
EcoulÚ : 00 :00 :00.92 |
