ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一条sql语句的优化

一条sql语句的优化

原创 Linux操作系统 作者:lfree 时间:2005-04-13 00:00:00 0 删除 编辑
今天跟踪程序,无意中发现一条不良的SQL语句
今天跟踪程序,无意中发现一条不良的SQL语句

分析如下:

SELECT   fee_stat_name, NVL (t1.b, 0)
    FROM (SELECT   fee_code, NVL (SUM (tot_cost), 0) b
              FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
                           "IF_BALANCELIST"."TOT_COST", '0', balance_dtime,
                           "IF_BALANCELIST"."OPER_CODE"
                      FROM "IF_BALANCELIST"
                     WHERE NVL (waste_flag, '0') <> '2'
                    UNION ALL
                    SELECT "IF_BALANCELIST"."FEE_CODE",
                           -"IF_BALANCELIST"."TOT_COST", '1', waste_dtime,
                           "OPER_CODE"
                      FROM "IF_BALANCELIST"
                     WHERE waste_flag = '1') ti1,
                   his_report_arg
             WHERE balance_dtime BETWEEN his_report_arg.start_dt
                                     AND his_report_arg.end_dt
               AND ti1.oper_code = his_report_arg.oper_code
               AND his_report_arg.SID = USERENV ('sessionid')
          GROUP BY fee_code) t1,
         (SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
                          "IA_FEESTATNEXUS"."FEE_STAT_CATE",
                          "IA_FEESTATNEXUS"."PRINT_ORDER"
                     FROM "IA_FEESTATNEXUS"
                    WHERE report_code = 'ZY01') t2
   WHERE t1.fee_code(+) = t2.fee_stat_cate
ORDER BY t2.print_order

执行计划如下:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=54 Bytes=2268)
   1    0   SORT (ORDER BY) (Cost=44 Card=54 Bytes=2268)
   2    1     HASH JOIN (OUTER) (Cost=40 Card=54 Bytes=2268)
   3    2       VIEW (Cost=5 Card=54 Bytes=1404)
   4    3         SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
   6    5             INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card=54)
   7    2       VIEW (Cost=35 Card=4 Bytes=64)
   8    7         SORT (GROUP BY) (Cost=35 Card=4 Bytes=216)
   9    8           NESTED LOOPS (Cost=31 Card=4 Bytes=216)
  10    9             TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
  11   10               INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
  12    9             VIEW
  13   12               UNION-ALL
  14   13                 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=3047 Bytes=63987)
  15   13                 TABLE ACCESS (FULL) OF 'IF_BALANCELIST' (Cost=15 Card=20313 Bytes=304695)

Statistics
----------------------------------------------------------
          0  recursive calls
         10  db block gets
       1059  consistent gets
          0  physical reads
          0  redo size
       1713  bytes sent via SQL*Net to client
        535  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         23  rows processed

主要的问题在于IF_BALANCELIST要执行两次全表扫描。而判断条件中使用balance_dtime BETWEEN
his_report_arg.start_dt AND his_report_arg.end_dt,没有很好地利用IF_BALANCELIST建立的
balance_dtime与waste_dtime索引,应该修改如下:

SELECT   fee_stat_name, NVL (SUM (c), 0)
    FROM (SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
            FROM (SELECT   fee_code, NVL (SUM (tot_cost), 0) b
                      FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
                                   "IF_BALANCELIST"."TOT_COST", '0',
                                   balance_dtime,
                                   "IF_BALANCELIST"."OPER_CODE"
                              FROM "IF_BALANCELIST"
                             WHERE NVL (waste_flag, '0') <> '2') ti1,
                           his_report_arg
                     WHERE balance_dtime BETWEEN his_report_arg.start_dt
                                             AND his_report_arg.end_dt
                       AND ti1.oper_code = his_report_arg.oper_code
                       AND his_report_arg.SID = USERENV ('sessionid')
                  GROUP BY fee_code) t1,
                 (SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
                                  "IA_FEESTATNEXUS"."FEE_STAT_CATE",
                                  "IA_FEESTATNEXUS"."PRINT_ORDER"
                             FROM "IA_FEESTATNEXUS"
                            WHERE report_code = 'ZY01') t2
           WHERE t1.fee_code(+) = t2.fee_stat_cate
          UNION ALL
          SELECT fee_stat_name, NVL (t1.b, 0) c, t2.print_order
            FROM (SELECT   fee_code, NVL (SUM (tot_cost), 0) b
                      FROM (SELECT "IF_BALANCELIST"."FEE_CODE",
                                   -"IF_BALANCELIST"."TOT_COST" tot_cost, '1',
                                   waste_dtime, "OPER_CODE"
                              FROM "IF_BALANCELIST"
                             WHERE waste_flag = '1') ti1,
                           his_report_arg
                     WHERE waste_dtime BETWEEN his_report_arg.start_dt
                                           AND his_report_arg.end_dt
                       AND ti1.oper_code = his_report_arg.oper_code
                       AND his_report_arg.SID = USERENV ('sessionid')
                  GROUP BY fee_code) t1,
                 (SELECT DISTINCT "IA_FEESTATNEXUS"."FEE_STAT_NAME",
                                  "IA_FEESTATNEXUS"."FEE_STAT_CATE",
                                  "IA_FEESTATNEXUS"."PRINT_ORDER"
                             FROM "IA_FEESTATNEXUS"
                            WHERE report_code = 'ZY01') t2
           WHERE t1.fee_code(+) = t2.fee_stat_cate)
GROUP BY fee_stat_name, print_order
ORDER BY print_order

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=108 Bytes=3888)
   1    0   SORT (GROUP BY) (Cost=26 Card=108 Bytes=3888)
   2    1     VIEW (Cost=22 Card=108 Bytes=3888)
   3    2       UNION-ALL
   4    3         HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
   5    4           VIEW (Cost=5 Card=54 Bytes=1404)
   6    5             SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
   8    7                 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
   9    4           VIEW (Cost=6 Card=1 Bytes=16)
  10    9             SORT (GROUP BY) (Cost=6 Card=1 Bytes=45)
  11   10               NESTED LOOPS (Cost=2 Card=1 Bytes=45)
  12   11                 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
  13   12                   INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
  14   11                 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=3047 Bytes=63987)
  15   14                   INDEX (RANGE SCAN) OF 'I_IF_BLANCELIST_BALANCE_DTIME' (NON-UNIQUE)
  16    3         HASH JOIN (OUTER) (Cost=11 Card=54 Bytes=2268)
  17   16           VIEW (Cost=5 Card=54 Bytes=1404)
  18   17             SORT (UNIQUE) (Cost=5 Card=54 Bytes=1296)
  19   18               TABLE ACCESS (BY INDEX ROWID) OF 'IA_FEESTATNEXUS' (Cost=1 Card=54 Bytes=1296)
  20   19                 INDEX (RANGE SCAN) OF 'PK_IA_FEESTATNEXUS' (UNIQUE) (Cost=1 Card =54)
  21   16           VIEW (Cost=6 Card=3 Bytes=48)
  22   21             SORT (GROUP BY) (Cost=6 Card=3 Bytes=117)
  23   22               NESTED LOOPS (Cost=2 Card=3 Bytes=117)
  24   23                 TABLE ACCESS (BY INDEX ROWID) OF 'HIS_REPORT_ARG' (Cost=1 Card=1 Bytes=24)
  25   24                   INDEX (UNIQUE SCAN) OF 'PK_HIS_REPORT_ARG' (UNIQUE)
  26   23                 TABLE ACCESS (BY INDEX ROWID) OF 'IF_BALANCELIST' (Cost=1 Card=20313 Bytes=304695)
  27   26                   INDEX (RANGE SCAN) OF 'I_IF_BALANCELIST_WASTE_DTIME' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
       1715  bytes sent via SQL*Net to client
       1131  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
         23  rows processed

这样就能够充分利用IF_BALANCELIST建立的balance_dtime与waste_dtime索引。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-82779/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 猪笼草和蜜蜂
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291614