加载中…
个人资料
iceman
iceman
微博
  • 博客等级：
  • 博客积分：0
  • 博客访问：3,655
  • 关注人气：10
  • 获赠金笔：0支
  • 赠出金笔：0支
  • 荣誉徽章：
正文 字体大小：大 中 小

分页语句与视图中采用UNION ALL存在性能问题

(2008-10-13 17:01:46)
标签：

oracle

分页

union

all

it

 分类： oracle9i

分页语句与UNION ALL存在性能问题

今天在处理一个分页查询语句的时候碰到了一个BUG，使用下面的分页语句进行查询，耗费的时间竟然要19秒：
SQL> SELECT  *
  2            FROM (SELECT a.*, ROWNUM rn
  3              FROM (select ID,
  4                           DCID,
  5                           ZZID,
  6                           HGTAG,
  7                           CE,
  8                           HMSQLY,
  9                           to_char(SQSJ, 'yyyy-MM-dd hh24:mi') SQSJ,
 10                           HMTAG,
 11                           PFSJ,
 12                           HMSQPF,
 13                           USERNAME,
 14                           JZ
 15                      from v_hmsqpf
 16                     WHERE HMTAG = '2'
 17                     Order By dcid, jz) a
 18             WHERE ROWNUM <= 50)
 19           WHERE rn >= 1;

已选择50行。

已用时间:  00: 00: 19.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=193298 Card=50 Bytes=176650)

   1    0   VIEW (Cost=193298 Card=50 Bytes=176650)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=193298 Card=183318 Bytes=645279360)
   4    3         SORT (ORDER BY STOPKEY) (Cost=193298 Card=183318 Bytes=645096042)
   5    4           VIEW OF 'V_HMSQPF' (Cost=426 Card=183318 Bytes=645096042)
   6    5             UNION-ALL
   7    6               HASH JOIN (Cost=336 Card=161670 Bytes=12125250)
   8    7                 TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
   9    7                 HASH JOIN (Cost=332 Card=161670 Bytes=10185210)
  10    9                   TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
  11    9                   HASH JOIN (OUTER) (Cost=328 Card=173500 Bytes=9022000)
  12   11                     TABLE ACCESS (FULL) OF 'T_AGC' (Cost=195 Card=173500 Bytes=6419500)
  13   11                     TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)
  14    6               HASH JOIN (Cost=90 Card=21648 Bytes=1688544)
  15   14                 TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
  16   14                 HASH JOIN (Cost=87 Card=21648 Bytes=1428768)
  17   16                   TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
  18   16                   HASH JOIN (OUTER) (Cost=84 Card=24816 Bytes=1364880)
  19   18                     TABLE ACCESS (FULL) OF 'T_NAGC' (Cost=61 Card=24816 Bytes=992640)
  20   18                     TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)

Statistics
----------------------------------------------------------
     264987  recursive calls
          0  db block gets
    1390686  consistent gets
       1477  physical reads
          0  redo size
       3522  bytes sent via SQL*Net to client
        529  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         50  rows processed

而不使用分页，使用下面的语句进行查询，只须0.04秒：

SQL> select ID,
  2                           DCID,
  3                           ZZID,
  4                           HGTAG,
  5                           CE,
  6                           HMSQLY,
  7                           to_char(SQSJ, 'yyyy-MM-dd hh24:mi') SQSJ,
  8                           HMTAG,
  9                           PFSJ,
 10                           HMSQPF,
 11                           USERNAME,
 12                           JZ
 13                      from v_hmsqpf
 14                     WHERE HMTAG = '2'
 15                     Order By dcid, jz;

已选择63行。

已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19117 Card=24220 Bytes=85230180)
   1    0   SORT (ORDER BY) (Cost=19117 Card=24220 Bytes=85230180)
   2    1     VIEW OF 'V_HMSQPF' (Cost=208 Card=24220 Bytes=85230180)
   3    2       UNION-ALL
   4    3         HASH JOIN (Cost=136 Card=21190 Bytes=1589250)
   5    4           TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
   6    4           HASH JOIN (Cost=133 Card=21190 Bytes=1334970)
   7    6             TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
   8    6             HASH JOIN (OUTER) (Cost=130 Card=22740 Bytes=1182480)
   9    8               TABLE ACCESS (BY INDEX ROWID) OF 'T_AGC' (Cost=109 Card=22740 Bytes=841380)
  10    9                 INDEX (RANGE SCAN) OF 'T_AGC_INX_HMTAG' (NON-UNIQUE) (Cost=4 Card=36646)
  11    8               TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)
  12    3         HASH JOIN (OUTER) (Cost=72 Card=3030 Bytes=236340)
  13   12           HASH JOIN (Cost=67 Card=3030 Bytes=190890)
  14   13             TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
  15   13             HASH JOIN (Cost=64 Card=3030 Bytes=154530)
  16   15               TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
  17   15               TABLE ACCESS (FULL) OF 'T_NAGC' (Cost=61 Card=3474 Bytes=138960)
  18   12           TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49Bytes=735)

Statistics
----------------------------------------------------------
        133  recursive calls
          0  db block gets
       1132  consistent gets
          0  physical reads
          0  redo size
       3942  bytes sent via SQL*Net to client
        540  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         63  rows processed
上面分页语句的写法并没有任何问题，为何上面的两个SQL语句的执行效率相差这么大呢？
仔细对比了两个SQL语句的执行计划，第一个语句的执行计划，基本所有表都走了全表扫描，这是不对的，因为已经在相应的表上已经建立了相应的索引，并进行表分析，不可能都走全表扫描的。而第二个语句的执行计划基本都走上了索引，所以效率比较高。而为什么会这样呢？
查看了分页语句中的视图v_hmsqpf，视图中采用了UNION ALL的写法，写法如下：
create or replace view v_hmsqpf as
select
a.ID||';agc' ID,
a.DCID DCIDS,
dc.dcmc DCID,
zz.username ZZID,
f_get_sfhg('yesno',HGTAG) HGTAG,
CE,
JZ,
HMSQLY,
SQSJ,
hmtag,
to_char(pjsd,'yyyy-MM-dd hh24:mi') pfsj,
pjsd pjsd1,
pjsd pjsd2,
hmsqpf,
pfzz.username
from t_agc a, t_zhizhang zz, t_dianchang dc,s_u_user pfzz
where a.zzid = zz.id and a.dcid = dc.id and a.pfrid=pfzz.id(+)
union all
select
na.ID||';nagc' ID,
na.DCID DCIDS,
dc.dcmc DCID,
zz.username ZZID,
f_get_sfhg('yesno',HGTAG) HGTAG,
CE,
'' JZ,
HMSQLY,
SQSJ,
hmtag,
to_char(pjsd1,'yyyy-MM-dd hh24:mi')||'-'||to_char(pjsd2,'hh24:mi') pfsj,
pjsd1,
pjsd2,
hmsqpf,
pfzz.username
from t_nagc na, t_zhizhang zz, t_dianchang dc,s_u_user pfzz
where na.zzid = zz.id and na.dcid = dc.id and na.pfrid=pfzz.id(+)
视图的写法没有什么特别之处，只是采用了UNION ALL的写法，难道分页语句和视图中采用UNION ALL存在冲突？
那么换另外一种写法，直接使用视图中的SQL语句，即直接查询基表，而不是查询视图：
SQL> Select *
  2    From (Select a.*, Rownum Rn
  3            From (Select Id,
  4                         Dcid,
  5                         Zzid,
  6                         Hgtag,
  7                         Ce,
  8                         Hmsqly,
  9                         To_Char(Sqsj, 'yyyy-MM-dd hh24:mi') Sqsj,
 10                         Hmtag,
 11                         Pfsj,
 12                         Hmsqpf,
 13                         Username,
 14                         Jz
 15                    From (Select a.Id || ';agc' Id,
 16                                 a.Dcid Dcids,
 17                                 Dc.Dcmc Dcid,
 18                                 Zz.Username Zzid,
 19                                 f_Get_Sfhg('yesno', Hgtag) Hgtag,
 20                                 Ce,
 21                                 Jz,
 22                                 Hmsqly,
 23                                 Sqsj,
 24                                 Hmtag,
 25                                 To_Char(Pjsd, 'yyyy-MM-dd hh24:mi') Pfsj,
 26                                 Pjsd Pjsd1,
 27                                 Pjsd Pjsd2,
 28                                 Hmsqpf,
 29                                 Pfzz.Username
 30                            From t_Agc       a,
 31                                 t_Zhizhang  Zz,
 32                                 t_Dianchang Dc,
 33                                 s_u_User    Pfzz
 34                           Where a.Zzid = Zz.Id
 35                             And a.Dcid = Dc.Id
 36                             And a.Pfrid = Pfzz.Id(+)
 37                             And Hmtag = '2'
 38                          Union All
 39                          Select Na.Id || ';nagc' Id,
 40                                 Na.Dcid Dcids,
 41                                 Dc.Dcmc Dcid,
 42                                 Zz.Username Zzid,
 43                                 f_Get_Sfhg('yesno', Hgtag) Hgtag,
 44                                 Ce,
 45                                 '' Jz,
 46                                 Hmsqly,
 47                                 Sqsj,
 48                                 Hmtag,
 49                                 To_Char(Pjsd1, 'yyyy-MM-dd hh24:mi') || '-'
||
 50                                 To_Char(Pjsd2, 'hh24:mi') Pfsj,
 51                                 Pjsd1,
 52                                 Pjsd2,
 53                                 Hmsqpf,
 54                                 Pfzz.Username
 55                            From t_Nagc      Na,
 56                                 t_Zhizhang  Zz,
 57                                 t_Dianchang Dc,
 58                                 s_u_User    Pfzz
 59                           Where Na.Zzid = Zz.Id
 60                             And Na.Dcid = Dc.Id
 61                             And Na.Pfrid = Pfzz.Id(+)
 62                             And Hmtag = '2'
 63                           Order By Dcid, Jz)
 64                          ) a
 65           Where Rownum <= 50)
 66   Where Rn >= 1;

已选择50行。

已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=514 Card=50 Bytes=176650)
   1    0   VIEW (Cost=514 Card=50 Bytes=176650)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=514 Card=24220 Bytes=85230180)
   4    3         SORT (ORDER BY STOPKEY) (Cost=208 Card=24220 Bytes=1825590)
   5    4           UNION-ALL
   6    5             HASH JOIN (Cost=136 Card=21190 Bytes=1589250)
   7    6               TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
   8    6               HASH JOIN (Cost=133 Card=21190 Bytes=1334970)
   9    8                 TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
  10    8                 HASH JOIN (OUTER) (Cost=130 Card=22740 Bytes=1182480)
  11   10                   TABLE ACCESS (BY INDEX ROWID) OF 'T_AGC' (Cost=109 Card=22740 Bytes=841380)
  12   11                     INDEX (RANGE SCAN) OF 'T_AGC_INX_HMTAG'(NON-UNIQUE) (Cost=4 Card=36646)
  13   10                   TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)
  14    5             HASH JOIN (OUTER) (Cost=72 Card=3030 Bytes=236340)
  15   14               HASH JOIN (Cost=67 Card=3030 Bytes=190890)
  16   15                 TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
  17   15                 HASH JOIN (Cost=64 Card=3030 Bytes=154530)
  18   17                   TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
  19   17                   TABLE ACCESS (FULL) OF 'T_NAGC' (Cost=61 Card=3474 Bytes=138960)
  20   14               TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)

Statistics
----------------------------------------------------------
        126  recursive calls
          0  db block gets
       1130  consistent gets
          0  physical reads
          0  redo size
       3522  bytes sent via SQL*Net to client
       1490  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         50  rows processed
时间为0.04秒，执行计划也正确，初步可以确定，分页语句中的视图如果含有UNION ALL的话，会导致ORACLE选择错误的执行计划，导致性能问题。解决的方法可以参考上面。但上面的语句写法比较复杂，可读性也比较低。可以采用ORACLE的分析函数Row_Number()，可以简单很多，如下：
SQL> Select *
  2    From (Select Id,
  3           Dcid,
  4           Zzid,
  5           Hgtag,
  6           Ce,
  7           Hmsqly,
  8           To_Char(Sqsj, 'yyyy-MM-dd hh24:mi') Sqsj,
  9           Hmtag,
 10           Pfsj,
 11           Hmsqpf,
 12           Username,
 13           Jz,
 14           Row_Number() Over(Order By Dcid, Jz) Rn
 15      From v_Hmsqpf a
 16     Where Hmtag = '2')
 17   Where Rn <= 50
 18     And Rn >= 1;

已选择50行。

已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19117 Card=24220 Bytes=85569260)
   1    0   VIEW (Cost=19117 Card=24220 Bytes=85569260)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=19117 Card=24220 Bytes=85230180)
   3    2       VIEW OF 'V_HMSQPF' (Cost=208 Card=24220 Bytes=85230180)
   4    3         UNION-ALL
   5    4           HASH JOIN (Cost=136 Card=21190 Bytes=1589250)
   6    5             TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
   7    5             HASH JOIN (Cost=133 Card=21190 Bytes=1334970)
   8    7               TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2 Card=41 Bytes=451)
   9    7               HASH JOIN (OUTER) (Cost=130 Card=22740 Bytes=1182480)
  10    9                 TABLE ACCESS (BY INDEX ROWID) OF 'T_AGC' (Cost=109 Card=22740 Bytes=841380)
  11   10                   INDEX (RANGE SCAN) OF 'T_AGC_INX_HMTAG' (NON-UNIQUE) (Cost=4 Card=36646)
  12    9                 TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)
  13    4           HASH JOIN (OUTER) (Cost=72 Card=3030 Bytes=236340)
  14   13             HASH JOIN (Cost=67 Card=3030 Bytes=190890)
  15   14               TABLE ACCESS (FULL) OF 'T_ZHIZHANG' (Cost=2 Card=49 Bytes=588)
  16   14               HASH JOIN (Cost=64 Card=3030 Bytes=154530)
  17   16                 TABLE ACCESS (FULL) OF 'T_DIANCHANG' (Cost=2Card=41 Bytes=451)
  18   16                 TABLE ACCESS (FULL) OF 'T_NAGC' (Cost=61 Card=3474 Bytes=138960)
  19   13             TABLE ACCESS (FULL) OF 'S_U_USER' (Cost=3 Card=49 Bytes=735)

Statistics
----------------------------------------------------------
        133  recursive calls
          0  db block gets
       1132  consistent gets
          0  physical reads
          0  redo size
       3522  bytes sent via SQL*Net to client
        529  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         50  rows processed

 

 

喜欢

0

阅读┊ 收藏 ┊ 喜欢▼ ┊打印┊举报/Report
  

新浪BLOG意见反馈留言板　欢迎批评指正

新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 产品答疑

Copyright © 1996 - 2022 SINA Corporation, All Rights Reserved

新浪公司 版权所有