如何为表中多条记录分别实现构造结果集（一）
上一篇 / 下一篇 2008-04-14 21:22:47 / 个人分类：ORACLE
前一段用SQL实现了一个复杂的功能，但是留下了一个问题一直没有真正的解决，那就是如何同时为表中的多条记录分别实现构造结果集。
问题源自：一个复杂问题的求解过程（一）：http://yangtingkun.itpub.net/post/468/456641
感觉直接用文字描述比较困难，下面通过一个例子说明打算实现什么样的功能。
在写一个复杂的SQL的时候，经常会涉及到构造结果集的功能，而构造的功能一般是通过两种方法来实现，第一种是借助一张已经存在的大表，比如DBA_OBJECTS，通过访问这张表来获取需要的记录，第二种方式是利用CONNECT BY方式直接从DUAL表获取。
第一种方法比较直观，但是需要依赖被访问的表有足够的记录。而第二种方式除了DUAL表外不需要借助任何的对象，而且没有记录条数的限制。
比如希望构造一个1到10的结果集，采用两种方法分别为：
SQL> SELECT ROWNUM RN FROM ALL_OBJECTS WHERE ROWNUM <= 10;
RNITPUB个人空间LzyU1A9|s9D
----------
T^3M Y wic0 1
8x9^2pz @0 2
(y9o
A5{ I4}m p/ofk0 3
%b{-}
U:o d-\0 4
Q%y}B*M*G5i z%k7g(L0 5
&YlI2{ w d9Yp0 6ITPUB个人空间[ y*vII
w&L
7
k"qvu0J:c)P|0 8ITPUB个人空间.c7p)d#A S
9ITPUB个人空间6p _l*a"t
10
已选择10行。
前面已经提到了，这种方法虽然方便但是需要访问一张大表，还需要确保构造的记录数小于大表中的记录总数。
第二种方法是利用CONNECT BY来访问DUAL表：
SQL> SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL <= 10;
RNITPUB个人空间mC^ O6I,r u-}4T1X
----------ITPUB个人空间q Z K ~,|B
1
o$C/wy;O\/I0 2ITPUB个人空间+Q~)f5@{(t y
3
K,Cy| y4x%E F0 4ITPUB个人空间{j CEL2a T
5
{7qYe'@m O*P A h0 6
8c&j.[ EI!D0 7ITPUB个人空间 _(AK N C
8ITPUB个人空间3{%MTo n#\
9ITPUB个人空间Rl^%a DZ
10
已选择10行。
DUAL表是Oracle数据库必然存在的表，Oracle的很多数据字典都依赖这张表。而且这种方式不需要担心记录数超过表中记录的情况，有更好的普遍性。因此一般实现构造结果集的时候都倾向于选择这种方式。
不过现在碰到的问题不是构造一个结果集，而是为表中配置记录分别构造结果集：
SQL> CREATE TABLE T (ID NUMBER, VALUE NUMBER, POWER NUMBER);
表已创建。
SQL> INSERT INTO T VALUES (1, 2, 3);
已创建1行。
SQL> INSERT INTO T VALUES (2, 1, 2);
已创建1行。
SQL> INSERT INTO T VALUES (3, 4, 4);
已创建1行。
SQL> INSERT INTO T VALUES (4, 5, 2);
已创建1行。
SQL> INSERT INTO T VALUES (5, 4, 3);
已创建1行。
SQL> COMMIT;
提交完成。
SQL> SELECT * FROM T;
ID VALUE POWERITPUB个人空间+o O+At1v
---------- ---------- ----------
iv B(M%g.@0 1 2 3
f+ha
M,rZ0 2 1 2
dX8L'xW uS#N0 3 4 4ITPUB个人空间l{8~#E j E N
4 5 2ITPUB个人空间"nP/r!p ]
5 4 3
现在的问题是，为5条记录分别生成结果集，每个ID对应的结果集POWER从0开始递增到POWER中现有的值（不包括当前值），最后在乘以VALUE的值。简单的说，最终需要得到的结果如下：
ID VALUE POWER RESULTITPUB个人空间8Sp4?H3e J K)U*M
---------- ---------- ---------- ----------ITPUB个人空间F+z{
ng*Jr
1 2 3 0ITPUB个人空间m}&?%VD;V(X(R
1 2 3 2
K
KTsC0 1 2 3 4
&C#zHX){:p@rP0 2 1 2 0ITPUB个人空间x RA al Y2~/o9e
2 1 2 1
F7U2V$e FE7G0 3 4 4 0ITPUB个人空间 iW(M G8N:d#?&Q
3 4 4 4
vgj5o4]MY }0 3 4 4 8
9uN ?8x
qx0x#?j0 3 4 4 12
LHk&nF8Y1LJ0 4 5 2 0ITPUB个人空间+L2Kx8^z+^@[z
4 5 2 5
4im n5O r J0 5 4 3 0ITPUB个人空间,D"h Z6r I,C
P
5 4 3 4
a U6~ v MF$}5_0 5 4 3 8
也就是说，现在需要做的是为一组记录生成结果集，且每条记录结果集大小是不固定的，这个大小有这条记录上的数据来限制。
这个时候显然不能直接使用CONNECT BY来实现，因为表中包含了多条记录，直接使用CONNECT BY LEVEL < POWER的方式会导致表中数据之间产生树形查询，从而导致结果集出现大量的重复记录：
SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULTITPUB个人空间CG$WP%f9UI
2 FROM T
T^1`C[ n0 3 CONNECT BY LEVEL <= T.POWER
6f6Y MXlq ey0 4 ORDER BY 1, 4;
ID VALUE POWER RESULTITPUB个人空间ufh5g|V/}l
---------- ---------- ---------- ----------ITPUB个人空间-LY c vc {q
O n
1 2 3 0
/yV1z CM Q/I8}
a#O0 1 2 3 2
Q dN:X f|&K q0 1 2 3 2ITPUB个人空间
m2n1nQC Ej3A
1 2 3 2
V wb@"}7]2]9D}Wq0 1 2 3 2
'f5S*hM3V f3F-tIA-p0 1 2 3 2ITPUB个人空间.W+_x8TJ3B:h)E Hc-iy
1 2 3 4ITPUB个人空间'\-yOj'Vw XT%L)Z5G
1 2 3 4ITPUB个人空间
i,wD(w,Xlk)|
1 2 3 4
-k#za0r J/k0 1 2 3 4ITPUB个人空间Vg(F'w l
1 2 3 4ITPUB个人空间I1Y&k-@&G"_&pj~-`&L
1 2 3 4
qf3a PmN,fu0 1 2 3 4
tC:{)L9bd~0.
Y:p"V1z a0.
W H,Dq YP8mG6oO[0.
:j}/Z8r;`0 1 2 3 4ITPUB个人空间N X1E ^ | @9Q_O
2 1 2 0ITPUB个人空间D F-Rr*K \3J-\$?0]
2 1 2 1
.F7uJ g,g(w ^0 2 1 2 1
5r/hvA#r0 2 1 2 1
E1r2V C-B6@u'~v.X0 2 1 2 1
.](\~\N
Ek AV0 2 1 2 1ITPUB个人空间 G*`{}._
3 4 4 0ITPUB个人空间)k8b"h e&Gh!i k+Q
3 4 4 4ITPUB个人空间k&s r!uY&C:Tf:Sm
3 4 4 4ITPUB个人空间 \LCv|_
3 4 4 4ITPUB个人空间n*j{d u I
3 4 4 4ITPUB个人空间Hp!F(I:G c
3 4 4 4ITPUB个人空间*\jB&S9a'b
3 4 4 8
0c Y!SA7aP0 3 4 4 8ITPUB个人空间/Ea#d2q Mwqt T
3 4 4 8
3] f X?1Vf+Of9m,~0 3 4 4 8ITPUB个人空间
]U {.Hv,uW
3 4 4 8
9us,H2t_:q G/G0.
;`JC
qyv){q0.ITPUB个人空间;LK ^C#rC,|:W
.ITPUB个人空间'x,WnW#h Q6W GL F ~
ITPUB个人空间 {s4hO;VM
3 4 4 12
I?$i @6b(?3W4MN0 4 5 2 0ITPUB个人空间e+O9D4Le*{E&s
4 5 2 5
d_h/Kzr0b EaI0 4 5 2 5
9V4D9laF5kj7V0 4 5 2 5ITPUB个人空间/E)|*Q`!R'~6\
4 5 2 5ITPUB个人空间'L4A*qF+t5g(P,H ^
4 5 2 5
8d5A NH3e4~!Uy'R(t0 5 4 3 0ITPUB个人空间(C,lxJB(D
5 4 3 4
?w)W4x:N&w x0 5 4 3 4
#|1]!L Qk[1z.z7a0 5 4 3 4ITPUB个人空间r-J$|'@Y
5 4 3 4
"D7_/a:L i-v ^#b0 5 4 3 4ITPUB个人空间H ZQG^P Y{
5 4 3 8
E)Qg:cEy&_)f6EgK0 5 4 3 8ITPUB个人空间'g
q w Rv C J7}m @
5 4 3 8
BKw/sL m6s"]
IO0 5 4 3 8
8z"RO6b9y
A2g2e0.ITPUB个人空间8bAW/CB
.ITPUB个人空间;b4nU[*B9T8O[
.ITPUB个人空间P:O bn4?$uC
5 4 3 8ITPUB个人空间 ]?W5],E1eL
5 4 3 8
已选择180行。
当然可以简单的采用DISTINCT和GROUP BY的方法来消除这个问题，比如：
SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
a7l{5eq0 2 FROM T
.k@
m E)]s*k0 3 CONNECT BY LEVEL <= T.POWER
-j3Sb@:}z0 4 GROUP BY ID, VALUE, LEVEL, POWER
`*Q#@dq0 5 ORDER BY 1, 4
&y(N n Q_7R0 6 ;
ID VALUE POWER RESULT
I sa6m_K4q7E0---------- ---------- ---------- ----------
;Y3L8hBEW0 1 2 3 0ITPUB个人空间^ G@,sv,o
r+v
1 2 3 2ITPUB个人空间`
RY#F+P
m5DG
1 2 3 4ITPUB个人空间 } sW
GNPcG
2 1 2 0
8](c_$U S+J(Gk0 2 1 2 1ITPUB个人空间 \Yu!P,Qo6p
3 4 4 0
1zC(kF;@0 3 4 4 4
0r[*E q{{0 3 4 4 8ITPUB个人空间*{,mt7N[ljrW
3 4 4 12
A)v;l O0z0 4 5 2 0ITPUB个人空间7fj9@6{Ax
{ zcK`%b
4 5 2 5ITPUB个人空间&c!Gy6Sj;OK,Gu
5 4 3 0ITPUB个人空间)ubA$[3l(I7M
5 4 3 4
)hy N(a,y a6R h[h0 5 4 3 8
已选择14行。
SQL> SELECT DISTINCT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
vl9?4ah c
yI0 2 FROM T
0O"E*O @1j0 3 CONNECT BY LEVEL <= T.POWERITPUB个人空间/pqL6f#Cr:J;c
4 ORDER BY 1, 4;
ID VALUE POWER RESULTITPUB个人空间/a;x bw2oe:|W
---------- ---------- ---------- ----------ITPUB个人空间Ux1Z8t'P6BA x
1 2 3 0ITPUB个人空间e:S W-A {o'kD
1 2 3 2ITPUB个人空间/p9Eu"w K,oW0{ m
1 2 3 4
6['UF2Y x d(x'?0 2 1 2 0
4D1m%c J1c,R(Q0 2 1 2 1
!p9T7q9^di-b v v0 3 4 4 0
R#P2G8N`1p/JL0 3 4 4 4ITPUB个人空间 b"GVz8\"swS
3 4 4 8
AFw'Z$tDs H0 3 4 4 12
9U S \ y1TXi.`0 4 5 2 0
bu,PH Z$L0 4 5 2 5ITPUB个人空间9k5A+P }$j'a0J1y
5 4 3 0
+^5t)U e0r.w'^L0 5 4 3 4
-d:BK)n!X
O0 5 4 3 8
已选择14行。
这两种方式似乎都可以圆满的解决这个问题，而且由于当时没有找到更好的方法在我前面的文章中给出的SQL解决方法中也是采用的这种方法：http://yangtingkun.itpub.net/post/468/456778
但是这种方法埋藏着一个严重的隐患，那就是性能问题。
由于这种方法存在着记录之间的树形查询，导致大量的重复记录出现，而采用DISTINCT或GROUP BY的方式，实际上只是为了最终结果的正确性，而并没有从根本上消除重复记录的问题，因此一旦数据库增大一点，就会导致需要DISTINCT的结果成数量级的增大。
比如将T中的记录增大到40条，在执行上面的查询：
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 AUTOT TRACE
;T&l.Y"Wx5bl0SQL> SET TIMING ON
HQ5K+{%at}9}0SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULTITPUB个人空间)j U4w!s9d\ }P
2 FROM T
.HaJ/]2S)b9s!v o0 3 CONNECT BY LEVEL <= T.POWER
Bf gn5Y|8B"X-RQ[0 4 GROUP BY ID, VALUE, LEVEL, POWER
D l br j){
wf0 5 ORDER BY 1, 4
} hj&q6h.E"bj0 6 ;
已选择112行。
已用时间: 00: 00: 04.04
执行计划ITPUB个人空间2V/[ XN&w
----------------------------------------------------------ITPUB个人空间r9oD q_*fO/o"m
Plan hash value: 2909186630
---------------------------------------------------------------------------------------ITPUB个人空间7D'_'xF~5lE
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |ITPUB个人空间 {T)e5M~$Q g Z
---------------------------------------------------------------------------------------ITPUB个人空间^z,N'I'U8b6@
| 0 | SELECT STATEMENT | | 40 | 1560 | 5 (40)| 00:00:01 |
.Vq L'QL.M0CD0| 1 | SORT ORDER BY | | 40 | 1560 | 5 (40)| 00:00:01 |
xP$vu)x5c;q0| 2 | HASH GROUP BY | | 40 | 1560 | 5 (40)| 00:00:01 |
8HG
[KO4R'f0| 3 | CONNECT BY WITHOUT FILTERING| | | | | |
#uj Lm `x0| 4 | TABLE ACCESS FULL | T | 40 | 1560 | 3 (0)| 00:00:01 |
YU f&K9j#Lr.{G0---------------------------------------------------------------------------------------
NoteITPUB个人空间\.xF-_2x3`(Z D'z
-----ITPUB个人空间b2oe Y J
- dynamic sampling used for this statement
)t0vF Bu bm | p0统计信息ITPUB个人空间@MLW q Vaj,k
----------------------------------------------------------
Ti!ct*bMX0 0 recursive calls
6M+BB4bZ]@0n0 0 db block gets
:{p{+h-_,mT,H0_0 7 consistent gets
R3uP5y$g F0 0 physical reads
o8k.@b:u"]Z$Lf Y0 0 redo sizeITPUB个人空间 [S?9kI$cb a w
2675 bytes sent via SQL*Net to clientITPUB个人空间4R/?[nP7{$T ?kE V
462 bytes received via SQL*Net from client
yd riZh,M0 9 SQL*Net roundtrips to/from client
1rJ |
e;b?&Fn\0 2 sorts (memory)
Yp
q D8n Q8k0 0 sorts (disk)
7f"`+n:@&dCPso~%u0 112 rows processed
仅仅40条记录查询就需要40秒，如果将表中记录再增加一倍：
SQL> SET AUTOT OFFITPUB个人空间-a9xZ:BXL\D
SQL> INSERT INTO T SELECT 40 + ID, VALUE, POWER FROM T;
已创建40行。
已用时间: 00: 00: 00.04
SQL> SET AUTOT TRACEITPUB个人空间SY)V1vb"V s
SQL> SELECT T.ID, T.VALUE, POWER, T.VALUE * (LEVEL - 1) RESULT
1^ P S#T5u|0 2 FROM TITPUB个人空间,y \3v `;gO
3 CONNECT BY LEVEL <= T.POWERITPUB个人空间5VZ mcrG+r
4 GROUP BY ID, VALUE, LEVEL, POWER
mJIZL7a!{ @;o-sB0 5 ORDER BY 1, 4
;y"^5n8ZJ-L0 6 ;
已选择224行。
已用时间: 00: 01: 56.92
执行计划ITPUB个人空间a y(Gt OYL c
----------------------------------------------------------
\#nz F4J#g0K0Plan hash value: 2909186630
---------------------------------------------------------------------------------------ITPUB个人空间 w rxIE4V \O
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
7Ke@)[5tz|4H"O0---------------------------------------------------------------------------------------ITPUB个人空间$Ji\m hc
| 0 | SELECT STATEMENT | | 80 | 3120 | 5 (40)| 00:00:01 |ITPUB个人空间7YI"zGsc],C
| 1 | SORT ORDER BY | | 80 | 3120 | 5 (40)| 00:00:01 |ITPUB个人空间E9y a\)} U
| 2 | HASH GROUP BY | | 80 | 3120 | 5 (40)| 00:00:01 |ITPUB个人空间%V@:a7J'r`W
| 3 | CONNECT BY WITHOUT FILTERING| | | | | |
6z:{
B#F/H5N9k Qc)q0| 4 | TABLE ACCESS FULL | T | 80 | 3120 | 3 (0)| 00:00:01 |ITPUB个人空间 \6Y8@(}3f!D`'J(i
---------------------------------------------------------------------------------------
Note
1Cz2R T hNvQ0-----ITPUB个人空间2A:CT!zH4x#y
- dynamic sampling used for this statement
ITPUB个人空间dAA
F}pC h5O
统计信息ITPUB个人空间7JJ8MW r k8H"{
----------------------------------------------------------
1IJ7p6s3}/cN0 0 recursive calls
X3fsv+R3l9i8h(p0 0 db block gets
j%X(q6j{7^0 7 consistent getsITPUB个人空间!H S9] r'|
0 physical readsITPUB个人空间7} |3r4Gj
0 redo size
/i-t%_Iq1e$w7n0 4756 bytes sent via SQL*Net to client
P|o0F9p(I h0 539 bytes received via SQL*Net from clientITPUB个人空间m*wC1C m j
a
16 SQL*Net roundtrips to/from client
7R4b)X/Yx#F,` Y0 2 sorts (memory)
#t{)c,P K
V C