空间管理您的位置: ITPUB个人空间 » yangtingkun的个人空间 » 日志

我申请这个blog是为了督促自己，把自己平时的一些想法和思考结果保留下来。本博客所有内容均为原创，如有转载请注明作者和出处

如何为表中多条记录分别实现构造结果集（二）

上一篇 / 下一篇 2008-04-15 23:10:33 / 个人分类：ORACLE

查看( 30 ) / 评论( 0 ) / 评分( 0 / 0 )

前一段用SQL实现了一个复杂的功能，但是留下了一个问题一直没有真正的解决，那就是如何同时为表中的多条记录分别实现构造结果集。

利用Oracle的TYPE实现结果集构造。

问题源自：一个复杂问题的求解过程（一）：http://yangtingkun.itpub.net/post/468/456641

如何为表中多条记录分别实现构造结果集（一）：http://yangtingkun.itpub.net/post/468/459848

上一篇已经提到通过DISTINCT的方式对于数据量大的情况是不可取的，而且从本质上讲，这个SQL的逻辑是有问题的。

当然这里可以通过对表中的配置记录依次生成结果集：

SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULTITPUB个人空间+B)@;mm5N$U@)n
2 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 1) TITPUB个人空间:mRh$QWZ
3 CONNECT BY LEVEL <= T.POWER
lNU*j$zG_.~L|0 4 UNION ALL
QWG%@|1k5Z B/W7R0 5 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
"v.wLuq\;H8Pg0 6 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 2) TITPUB个人空间a4tnz$W1Cz'Y Q
7 CONNECT BY LEVEL <= T.POWERITPUB个人空间 PC1^'Bhn6B7A
8 UNION ALL
X^~yP0 9 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
)D/r#D4BsxSTW0 10 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 3) TITPUB个人空间 _N6k(aI^L
11 CONNECT BY LEVEL <= T.POWER
12 UNION ALL
cOm!m d3|0 13 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
'V)i#S]Cb0 14 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 4) TITPUB个人空间}\7]FjyOz
15 CONNECT BY LEVEL <= T.POWERITPUB个人空间 N3yAc2Z
16 UNION ALLITPUB个人空间4DV;iS~Z"|
17 SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
oV5H.?4L$BG[g0 18 FROM (SELECT ROWNUM, T.* FROM T WHERE ID = 5) TITPUB个人空间"z,i.W/b,D`
19 CONNECT BY LEVEL <= T.POWERITPUB个人空间HRPnqr js
20 ;

        ID      VALUE      POWER     RESULT
g:_0W-yH+JJB:k3]0---------- ---------- ---------- ----------
         1          2          3          0
\!`v6~6L ]+K$NU0         1          2          3          2ITPUB个人空间5gLc%|O FVB.]
         1          2          3          4ITPUB个人空间mSA(L0st"p([nN
         2          1          2          0ITPUB个人空间 N!J[1a9^.Wx+n)M
         2          1          2          1
/s Z`Izn \LU0         3          4          4          0ITPUB个人空间 _#{:Ejc
         3          4          4          4
+~Cy|Nd+}"R5R9|}0         3          4          4          8ITPUB个人空间;W l1J%Y"WI"A J
         3          4          4         12ITPUB个人空间4c8EN:pJ;Fk
         4          5          2          0ITPUB个人空间 NW'YZ)wU
         4          5          2          5ITPUB个人空间i8{%RxO
         5          4          3          0ITPUB个人空间_;X&Z{K9D%o
         5          4          3          4
#dj4|H4Y0         5          4          3          8

已选择14行。

暂且不论这种方式的效率问题，单就实现而言就有很大的问题，这种方式采用硬编码的方式，一旦表中配置记录增加，就需要手工修改SQL，而且随着数据量的增大，SQL语句的长度会迅速增大，每增加一条记录就需要多UNION ALL一次基础表，这种效率也是无法接受的。

硬编码和DISTINCT方式都无法满足要求，只能选择其他的方法进行替代，如果这里借助一下自定义的TYPE类型，则SQL就会很容易实现：

SQL> CREATE OR REPLACE TYPE T_TYPE IS TABLE OF NUMBER;ITPUB个人空间j?s3TlW
2 /

类型已创建。

SQL> WITH T1 AS
sj^C ho0 2 (ITPUB个人空间x.St(w)k9Q
3 SELECT ID, VALUE, POWER,
t'`2^Y8V3cP0 4   CAST (MULTISET(ITPUB个人空间E+s`m]eJL]P
5    SELECT VALUE * (LEVEL - 1)
x4_2l LK!|:ZB0 6    FROM DUAL CONNECT BY ROWNUM <= POWERITPUB个人空间6]"YS+i)Uy1A
7   ) AS T_TYPE) RESULT
&_Y|+w#D\za0 8 FROM TITPUB个人空间9by+N)Cc#O(R
9 )ITPUB个人空间Rx$C(er,^s+I(S
10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULTITPUB个人空间4NvN5j`@
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) TITPUB个人空间.q/F/]nI QI
12 ;

        ID      VALUE      POWER     RESULT
Hf~.FE)@Cg0---------- ---------- ---------- ----------ITPUB个人空间;tL%s*X0G
         1          2          3          0
ns;L,Al8{0         1          2          3          2
-e+rzU|uI5r#_wR#j0         1          2          3          4ITPUB个人空间&Cr6cFh]6@%s
         2          1          2          0ITPUB个人空间\CH1E8~(L
         2          1          2          1
Z6biX.O\0         3          4          4          0
8Rr2V2?VG~0         3          4          4          4
glz]5xAod3Q0         3          4          4          8
!v7_:?)`?}'A4P@0         3          4          4         12
/w*ri-^+tEg'M0         4          5          2          0ITPUB个人空间 N7I1S]`lyH
         4          5          2          5
+m*cD@ E,g0         5          4          3          0ITPUB个人空间gB"n4I$L3n
         5          4          3          4
)X5h-V8@7{.Y${df0         5          4          3          8

已选择14行。

采用这种方式实现，SQL就相当简单了，而且性能也完全可以满足要求：

SQL> INSERT INTO T SELECT 5 + ID, VALUE, POWER FROM T;

已创建5行。

SQL> INSERT INTO T SELECT 10 + ID, VALUE, POWER FROM T;

已创建10行。

SQL> INSERT INTO T SELECT 20 + ID, VALUE, POWER FROM T;

已创建20行。

SQL> SET TIMING ONITPUB个人空间f.w6A(](y4T(R
SQL> SET AUTOT TRACE
7cbA YRz"\0SQL> WITH T1 ASITPUB个人空间_(xd!}D9U
2 (
}3V)ut2vgB0 3 SELECT ID, VALUE, POWER,ITPUB个人空间 {;z'jyV
4   CAST (MULTISET(
(Qn|}8o.M,R;l0 5    SELECT VALUE * (LEVEL - 1)
2N!s#Y7a)m [I/`h0 6    FROM DUAL CONNECT BY ROWNUM <= POWERITPUB个人空间 i|9L;L d(I{
7   ) AS T_TYPE) RESULTITPUB个人空间N0Z |QV4QCf*q#l
8 FROM TITPUB个人空间.]3aW)\&c$VCiE
9 )
v sD]@,xo%o0 10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
+v2Xy&c(V7h a F.`0 11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
By-W)imcQ;~b0 12 ;

已选择112行。

已用时间: 00: 00: 00.01

执行计划ITPUB个人空间AqN5M#K,|U
----------------------------------------------------------
l+lBE;]}0Plan hash value: 1145965015

--------------------------------------------------------------------------------------------ITPUB个人空间 S"M [;X6y'_-i
| Id | Operation                           | Name | Rows | Bytes | Cost (%CPU)| Time     |ITPUB个人空间1OH?_ot(tz
--------------------------------------------------------------------------------------------
6~S yg-Q?uw0|   0 | SELECT STATEMENT                    |      |   326K|   12M|   893   (2)| 00:00:11 |
xPE-u5D\1Ln$e0|   1 | NESTED LOOPS                       |      |   326K|   12M|   893   (2)| 00:00:11 |
:w Bvc,k}U0|   2 |   TABLE ACCESS FULL                 | T    |    40 | 1560 |     3   (0)| 00:00:01 |ITPUB个人空间i S(`8V R
|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |       |       |            |          |ITPUB个人空间]8P&{t!I#~N
|* 4 |    TABLE ACCESS FULL                | T    |     1 |    39 |     3   (0)| 00:00:01 |ITPUB个人空间fe0CB [o!AH
|   5 |     COUNT                           |      |       |       |            |          |ITPUB个人空间0C)uHf-A
|   6 |      CONNECT BY WITHOUT FILTERING   |      |       |       |            |          |ITPUB个人空间]k:u R/c)@w"\
|   7 |       FAST DUAL                     |      |     1 |       |     2   (0)| 00:00:01 |ITPUB个人空间$s"Y%WNC Mp
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
S2FPa yM:|i0---------------------------------------------------

4 - filter("ID"=:B1)

Note
&u&pS;n/M4HI7g0-----ITPUB个人空间@7CSYK9p!DAH
- dynamic sampling used for this statement

6r+E1c X3^;r)C\!D0统计信息ITPUB个人空间$esgg xr{$r
----------------------------------------------------------ITPUB个人空间~~Tkba
          0 recursive calls
p\)e.NWD5M b%F0          0 db block gets
s4RLaA&_L s3P F,g1s0        495 consistent gets
0o"O"~Y-_0          0 physical reads
,aq*f)Py1}h$es0          0 redo sizeITPUB个人空间GY kK1? \-u M3f
       2675 bytes sent via SQL*Net to client
f(CgPyo0        462 bytes received via SQL*Net from clientITPUB个人空间m(s@7H1O\(E,c#b
          9 SQL*Net roundtrips to/from client
{2W0`M9c-P Pe*a!^"l0         40 sorts (memory)ITPUB个人空间 G:\+o0F0WAe
          0 sorts (disk)
kUL7UI:J4C\AxP0        112 rows processed

SQL> SET AUTOT OFFITPUB个人空间D#dl M.]F/[[
SQL> INSERT INTO T SELECT 40 + ID, VALUE, POWER FROM T;

已创建40行。

已用时间: 00: 00: 00.00
0i0d E$h"a(zz%_UEL0SQL> SET AUTOT TRACE
&m [F!c q(s~v~@0SQL> WITH T1 AS
z/HRuyRcw"D;m0 2 (
2@Nj~)`"i0 3 SELECT ID, VALUE, POWER,
_uh],W5z0 4   CAST (MULTISET(
O"oW ]e0 5    SELECT VALUE * (LEVEL - 1)
3AS `!Gxn W0 6    FROM DUAL CONNECT BY ROWNUM <= POWERITPUB个人空间d6l*G$b(h3~[W
7   ) AS T_TYPE) RESULT
7f6u/o"tIV&c0 8 FROM TITPUB个人空间e0[2vJ3x*`
9 )
`&kmK$` Q%? h9^1J0 10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULTITPUB个人空间*rLdm-D C \3]
11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) TITPUB个人空间)T6o-M$K/q)}
12 ;

已选择224行。

已用时间: 00: 00: 00.01

执行计划
3Ce;j\5j-DP#R0----------------------------------------------------------
%I'u]M)wSsw0Plan hash value: 1145965015

--------------------------------------------------------------------------------------------ITPUB个人空间5e2a;q:P4SDL,B[
| Id | Operation                           | Name | Rows | Bytes | Cost (%CPU)| Time     |ITPUB个人空间-_.f6| m*Za`P&oj
--------------------------------------------------------------------------------------------ITPUB个人空间:YtVODe%mr
|   0 | SELECT STATEMENT                    |      |   653K|   25M| 1782   (2)| 00:00:22 |ITPUB个人空间6I(I-l)]~(Cz
|   1 | NESTED LOOPS                       |      |   653K|   25M| 1782   (2)| 00:00:22 |ITPUB个人空间P/N)Xe~\
|   2 |   TABLE ACCESS FULL                 | T    |    80 | 3120 |     3   (0)| 00:00:01 |
?Gk-U5ejWE0|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |       |       |            |          |ITPUB个人空间D{[ KcE
|* 4 |    TABLE ACCESS FULL                | T    |     1 |    39 |     3   (0)| 00:00:01 |ITPUB个人空间Y$nj L B|aOI!B#ij
|   5 |     COUNT                           |      |       |       |            |          |
)H@8p3z }3sZ8c0|   6 |      CONNECT BY WITHOUT FILTERING   |      |       |       |            |          |
2?ve%[5b9o)[0|   7 |       FAST DUAL                     |      |     1 |       |     2   (0)| 00:00:01 |ITPUB个人空间NXskN.k
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间9]{}K5ZlHE
---------------------------------------------------

4 - filter("ID"=:B1)

NoteITPUB个人空间\raF)[$j K7pw{
-----ITPUB个人空间[+E.c*M^V0Or s
- dynamic sampling used for this statement

ITPUB个人空间4\lR@N0Eq
统计信息
7v3P!U'Q+K0----------------------------------------------------------ITPUB个人空间2]W2k~/_,s.}A+e,}
          0 recursive callsITPUB个人空间w3R?!i RvBp
          0 db block getsITPUB个人空间Pr]fK0g5G
        982 consistent gets
@mF3xF$H;A0          0 physical reads
7}4y|3J/V2R7IE0          0 redo sizeITPUB个人空间$v&Hl4c9|+E
       4756 bytes sent via SQL*Net to clientITPUB个人空间!d$~r2\[n
        539 bytes received via SQL*Net from clientITPUB个人空间\4bfad8q!u oe1W8P
         16 SQL*Net roundtrips to/from clientITPUB个人空间Bf,_1ca.x
         80 sorts (memory)ITPUB个人空间+?'t)f(r]k#M
          0 sorts (disk)ITPUB个人空间9|'I#A)ZFi
        224 rows processed

可以看到这种SQL写法，在配置表中数据量增长后执行时间变化不大，即使将配置表扩大到1000条记录以上，这种写法仍然可以在1秒钟内完成，可以说这种写法满足性能方面的需求：

SQL> SET AUTOT OFFITPUB个人空间8Ay0r,D4q Fm
SQL> SET TIMING OFF
HQ6s1X9oA0SQL> INSERT INTO T SELECT 80 + ID, VALUE, POWER FROM T;

已创建80行。

SQL> INSERT INTO T SELECT 160 + ID, VALUE, POWER FROM T;

已创建160行。

SQL> INSERT INTO T SELECT 320 + ID, VALUE, POWER FROM T;

已创建320行。

SQL> INSERT INTO T SELECT 640 + ID, VALUE, POWER FROM T;

已创建640行。

SQL> SET TIMING ONITPUB个人空间0yHot!\*?~4w
SQL> SET AUTOT TRACE
0t2wv4H*s/?5d0SQL> WITH T1 AS
.U#ma't:ym!};h0 2 (
6d!KhAN0 3 SELECT ID, VALUE, POWER,
^|:x[*j7ni0 4   CAST (MULTISET(
ny;b6K;K*Hh S0 5    SELECT VALUE * (LEVEL - 1)ITPUB个人空间 u.u{;m_}
6    FROM DUAL CONNECT BY ROWNUM <= POWER
/m%~ aURz0j0 7   ) AS T_TYPE) RESULTITPUB个人空间voiP#~8y B%U
8 FROM T
Pq*a$fZ2I4{ E-|0 9 )
YK!xtA/k0 10 SELECT ID, VALUE, POWER, T.COLUMN_VALUE RESULT
{9E/U^ F?0 11 FROM T1 A, TABLE(SELECT RESULT FROM T1 WHERE T1.ID = A.ID) T
4W~|,_c5YCm*n0 12 ;

已选择3584行。

已用时间: 00: 00: 00.31

执行计划ITPUB个人空间tB%I6fb7[p
----------------------------------------------------------
6^e)gC [0Plan hash value: 1145965015

--------------------------------------------------------------------------------------------ITPUB个人空间&@Kh+u;] _
| Id | Operation                           | Name | Rows | Bytes | Cost (%CPU)| Time     |
Gj6P@X\~0--------------------------------------------------------------------------------------------
(Q,Y3A4yr0pl0|   0 | SELECT STATEMENT                    |      |   10M|  408M| 28453   (2)| 00:05:42 |ITPUB个人空间%h];d4p1Ah
|   1 | NESTED LOOPS                       |      |   10M|  408M| 28453   (2)| 00:05:42 |
IP s#@.vx0|   2 |   TABLE ACCESS FULL                 | T    | 1280 | 49920 |     3   (0)| 00:00:01 |
_3EN.qI;l0|   3 |   COLLECTION ITERATOR SUBQUERY FETCH|      |       |       |            |          |ITPUB个人空间#sw@K8`-t9h
|* 4 |    TABLE ACCESS FULL                | T    |    13 |   507 |     2   (0)| 00:00:01 |
V$t$Q+A`i pp O0|   5 |     COUNT                           |      |       |       |            |          |ITPUB个人空间os!J#p C N1l K
|   6 |      CONNECT BY WITHOUT FILTERING   |      |       |       |            |          |ITPUB个人空间(O({ o;yM1In
|   7 |       FAST DUAL                     |      |     1 |       |     2   (0)| 00:00:01 |ITPUB个人空间 Ou,S:^P#G!a
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
#Dm{SGX1T0---------------------------------------------------

4 - filter("ID"=:B1)

NoteITPUB个人空间Tp8Y!aNe/w)v6a-G
-----ITPUB个人空间(_'e0Y#\G \
- dynamic sampling used for this statement

_"l(?E:\v_0统计信息
uXI[j9q0----------------------------------------------------------
zH!j!?I:G`K0          0 recursive calls
Wo|V j;X7mR0          0 db block gets
z*k!Pipx0      16700 consistent gets
*CYW"m S0          0 physical readsITPUB个人空间|+JCN5D*R \
          0 redo sizeITPUB个人空间'hx ~rM|D
      70133 bytes sent via SQL*Net to client
z[`ec*B]0       3003 bytes received via SQL*Net from client
F~\N'qCvJ4U#z0        240 SQL*Net roundtrips to/from clientITPUB个人空间^A0`!Bmr&|8[z;|
       1280 sorts (memory)ITPUB个人空间+Cq*^l/B O
          0 sorts (disk)ITPUB个人空间Q%h5~|1Vi{k
       3584 rows processed