· 8 years ago · May 17, 2018, 05:30 AM
1ID Assigned_To
2100 raju
3101 raju
4102 raju
5103 anil
6104 anil
7105 sam
8106 raju
9107 raju
10108 anil
11
12From_Id To_Id Assigned_To
13100 102 raju
14103 104 anil
15105 105 sam
16106 107 raju
17108 108 anil
18
19SQL> create table ticket (id,assigned_to)
20 2 as
21 3 select 100, 'raju' from dual union all
22 4 select 101, 'raju' from dual union all
23 5 select 102, 'raju' from dual union all
24 6 select 103, 'anil' from dual union all
25 7 select 104, 'anil' from dual union all
26 8 select 105, 'sam' from dual union all
27 9 select 106, 'raju' from dual union all
28 10 select 107, 'raju' from dual union all
29 11 select 108, 'anil' from dual
30 12 /
31
32Tabel is aangemaakt.
33
34SQL> select min(id) from_id
35 2 , max(id) to_id
36 3 , assigned_to
37 4 from ( select id
38 5 , assigned_to
39 6 , id - row_number() over (partition by assigned_to order by id) grp
40 7 from ticket
41 8 )
42 9 group by assigned_to
43 10 , grp
44 11 order by from_id
45 12 /
46
47 FROM_ID TO_ID ASSIGNED_TO
48---------- ---------- -----------
49 100 102 raju
50 103 104 anil
51 105 105 sam
52 106 107 raju
53 108 108 anil
54
555 rijen zijn geselecteerd.
56
57SQL> exec runstats_pkg.rs_start
58
59PL/SQL procedure successfully completed.
60
61SQL> set termout off
62SQL> select min(id) from_id
63 2 , max(id) to_id
64 3 , assigned_to
65 4 from ( select id
66 5 , assigned_to
67 6 , id - row_number() over (partition by assigned_to order by id) grp
68 7 from ticket
69 8 )
70 9 group by assigned_to
71 10 , grp
72 11 order by from_id
73 12 /
74
75 FROM_ID TO_ID ASSI
76---------- ---------- ----
77 100 102 raju
78 103 104 anil
79 105 105 sam
80 106 107 raju
81 108 108 anil
82 109 111 raju
83<snip>
84 589921 589922 raju
85 589923 589923 anil
86
87327680 rows selected.
88
89SQL> set termout on
90SQL> exec runstats_pkg.rs_middle
91
92PL/SQL procedure successfully completed.
93
94SQL> set termout off
95SQL> select * from table(testpl.pltest)
96 2 /
97
98 FROM_ID TO_ID ASSI
99---------- ---------- ----
100 100 102 raju
101 103 104 anil
102 105 105 sam
103 106 107 raju
104 108 108 anil
105 109 111 raju
106<snip>
107 589921 589922 raju
108 589923 589923 anil
109
110327680 rows selected.
111
112SQL> set termout on
113
114SQL> exec runstats_pkg.rs_stop(100)
115Run1 draaide in 547 hsecs
116Run2 draaide in 549 hsecs
117Run1 draaide in 99.64% van de tijd
118
119Naam Run1 Run2 Verschil
120STAT.recursive cpu usage 2 106 104
121LATCH.row cache objects 91 217 126
122STAT.bytes received via SQL*Net from client 37,496 37,256 -240
123STAT.recursive calls 7 5,914 5,907
124STAT.table scan rows gotten 615,235 589,824 -25,411
125STAT.sorts (rows) 917,504 589,824 -327,680
126
127Run1 latches totaal versus run2 -- verschil en percentage
128Run1 Run2 Verschil Pct
12910,255 10,471 216 97.94%
130
131PL/SQL procedure successfully completed.
132
133create or replace package testpl is
134
135 type outrec_type is record
136 ( from_id ticket.id%type
137 , to_id ticket.id%type
138 , assigned_to ticket.assigned_to%type);
139
140 type outrec_table is table of outrec_type;
141
142 function pltest return outrec_table pipelined;
143
144end;
145/
146
147create or replace package body testpl is
148
149 function pltest return outrec_table pipelined
150 is
151 l_outrec outrec_type;
152 l_first_time boolean := true;
153 begin
154
155 for r_tick in (select id, assigned_to from ticket order by id) loop
156
157 if (r_tick.assigned_to != l_outrec.assigned_to or l_first_time) then
158 if not l_first_time then
159 pipe row (l_outrec);
160 else
161 l_first_time := false;
162 end if;
163 l_outrec.assigned_to := r_tick.assigned_to;
164 l_outrec.from_id := r_tick.id;
165 end if;
166 l_outrec.to_id := r_tick.id;
167 end loop;
168
169 pipe row (l_outrec);
170
171 return;
172 end;
173
174end;
175/
176
177select * from table(testpl.pltest);
178
179for r_tick in (select ....) loop
180 ....
181end loop;
182
183select min(from_id), to_id, assigned_to from
184(
185select from_id, max(to_id) as to_id, assigned_to from
186(
187select t1.id as from_id, t2.id as to_id, t1.assigned_to
188from ticket t1
189inner join ticket t2 on t1.assigned_to = t2.assigned_to and t2.id >= t1.id
190where not exists
191 (
192 select * from ticket t3
193 where t3.ID > t1.ID
194 and t3.ID < t2.ID
195 and t3.assigned_to != t1.assigned_to
196 )
197) x
198group by from_id, assigned_to
199) y
200group by to_id, assigned_to
201;
202
203CREATE TABLE assignment
204(
205 a_id NUMBER,
206 assigned_to VARCHAR2(4000)
207);
208
209CREATE OR REPLACE package PCK_CONTIGUOUS_GROUPBY as
210
211 TYPE refcur_t IS REF CURSOR RETURN assignment%ROWTYPE;
212
213 TYPE outrec_typ IS RECORD (
214 from_id NUMBER,
215 to_id NUMBER,
216 assigned_to VARCHAR2(4000));
217
218 TYPE outrecset IS TABLE OF outrec_typ;
219
220 FUNCTION f_cont_groupby(p refcur_t)
221 RETURN outrecset PIPELINED;
222
223end;
224/
225
226CREATE OR REPLACE package body pck_contiguous_groupby as
227
228 FUNCTION f_cont_groupby(p refcur_t) RETURN outrecset PIPELINED IS
229
230 out_rec outrec_typ;
231 in_rec p%ROWTYPE;
232 first_id assignment.a_id%type;
233 last_id assignment.a_id%type;
234 last_assigned_to assignment.assigned_to%type;
235
236 BEGIN
237
238 LOOP
239 FETCH p INTO in_rec;
240 EXIT WHEN p%NOTFOUND;
241
242
243 IF last_id IS NULL THEN
244 -- First record: don't pipe
245 first_id := in_rec.a_id;
246
247 ELSIF last_id = in_rec.a_id - 1 AND last_assigned_to = in_rec.assigned_to THEN
248 -- Contiguous block: don't pipe
249 NULL;
250
251 ELSE
252 -- Block not contiguous: pipe
253 out_rec.from_id := first_id;
254 out_rec.to_id := last_id;
255 out_rec.assigned_to := last_assigned_to;
256
257 PIPE ROW(out_rec);
258
259 first_id := in_rec.a_id;
260 END IF;
261
262 last_id := in_rec.a_id;
263 last_assigned_to := in_rec.assigned_to;
264
265 END LOOP;
266 CLOSE p;
267
268 -- Pipe remaining row
269 out_rec.from_id := first_id;
270 out_rec.to_id := last_id;
271 out_rec.assigned_to := last_assigned_to;
272
273 PIPE ROW(out_rec);
274
275 RETURN;
276 END;
277
278END pck_contiguous_groupby;
279/
280
281SELECT * FROM TABLE(pck_contiguous_groupby.f_cont_groupby (CURSOR (SELECT a_id, assigned_to FROM assignment ORDER BY a_id)));
282
283SQL> create table ticket (id number, assigned_to varchar2(30));
284
285Table created.
286
287SQL> insert into ticket values (100, 'raju');
288
2891 row created.
290
291SQL> insert into ticket values (101, 'raju');
292
2931 row created.
294
295SQL> insert into ticket values (102, 'raju');
296
2971 row created.
298
299SQL> insert into ticket values (103, 'anil');
300
3011 row created.
302
303SQL> insert into ticket values (104, 'anil');
304
3051 row created.
306
307SQL> insert into ticket values (105, 'sam');
308
3091 row created.
310
311SQL> insert into ticket values (106, 'raju');
312
3131 row created.
314
315SQL> insert into ticket values (107, 'raju');
316
3171 row created.
318
319SQL> insert into ticket values (108, 'anil');
320
3211 row created.
322
323SQL> select a.id from_id
324 2 ,lead(a.id -1, 1, a.id) over (order by a.id) to_id
325 3 ,a.assigned_to
326 4 from (
327 5 select
328 6 id, assigned_to
329 7 ,lag(assigned_to, 1) over (order by id) prev_assigned_to
330 8 from ticket
331 9 ) a
332 10 where a.assigned_to != nvl(a.prev_assigned_to, a.assigned_to||'unique')
333 11 order by id
334 12 ;
335
336 FROM_ID TO_ID ASSIGNED_TO
337---------- ---------- ------------------------------
338 100 102 raju
339 103 104 anil
340 105 105 sam
341 106 107 raju
342 108 108 anil
343
344--get results by grouping by interval_begin
345SELECT MIN(id) from_id,
346 MAX(id) to_id,
347 MAX(assigned_to) assigned_to
348 FROM ( --copy ids of a first row of each interval of ids to the all following rows of that interval
349 SELECT id,
350 assigned_to,
351 MAX(change_at) over(ORDER BY id) interval_begin
352 FROM ( --find each id where a change of an assignee occurs and "mark" it. Dont forget the first row
353 SELECT id,
354 assigned_to,
355 CASE
356 WHEN (lag(assigned_to) over(ORDER BY id) <> assigned_to OR lag(assigned_to)
357 over(ORDER BY id) IS NULL) THEN
358 id
359 END change_at
360 FROM ticket))
361 GROUP BY interval_begin
362 ORDER BY from_id;
363 ;
364
365create table ticket (id,assigned_to)
366as
367select 100, 'raju' from dual union all
368select 101, 'raju' from dual union all
369select 102, 'raju' from dual union all
370select 103, 'anil' from dual union all
371select 104, 'anil' from dual union all
372select 105, 'sam' from dual union all
373select 106, 'raju' from dual union all
374select 107, 'raju' from dual union all
375select 108, 'anil' from dual
376/
377
378
379begin
380 for i in 1..17 loop
381 insert into ticket
382 select id + (select count(*) from ticket), assigned_to
383 from ticket;
384 end loop;
385end;
386/
387
388commit;
389
390SQL> select count(*) from ticket;
391
392 COUNT(*)
393----------
394 1179648
395
396SELECT MIN(ID) AS From_Id, MAX(ID) AS To_Id, Assigned_To
397FROM Ticket
398PARTITION BY Assigned_To
399ORDER BY From_Id