ITPub博客

首页 > 数据库 > Oracle > [20151212优化sql语句要注意关键字DISTINCT

[20151212优化sql语句要注意关键字DISTINCT

原创 Oracle 作者:lfree 时间:2015-12-14 15:27:00 0 删除 编辑

[20151212]优化sql语句要注意关键字DISTINCT.txt

--做sql语句优化要特别注意带DISTINCT语句,有一些情况往往是开发写错或者写少了连接条件,或者没有用exists等关键字。
--举最近优化生产系统遇到的例子:

--显示的问题,我做了格式化处理:

sql_id=gmg7wsyv1pjm7
SELECT DISTINCT CP_LJSD.SDBH
                 ,CP_LJSD.LJBH
                 ,CP_LJSD.CKLX
                 ,CP_LJSD.XSMC AS XSMC
                 ,CP_LJSD.RQXH
    FROM CP_ZXJL, CP_ZLHD, CP_LJSD
   WHERE     (CP_ZXJL.HDBH = CP_ZLHD.HDBH)
         AND (CP_ZXJL.SDBH = CP_LJSD.SDBH)
         AND (    (CP_ZXJL.DQZT = :"SYS_B_0")
              AND (CP_ZXJL.YSXH = :"SYS_B_1")
              AND (CP_ZXJL.BRLJ = :al_brlj)
              AND (CP_ZXJL.ZCWC <= :adt_date OR CP_ZXJL.ZZKS <= :adt_date))
ORDER BY CP_LJSD.RQXH;

--很明显如果看我以前写的链接:http://blog.itpub.net/267265/viewspace-1852195/
--可以这些代码可能出自一个人之手,他的风格明显就是把需要连接的表列出来,在写出sql语句。
--正确的写法应该这样。

SELECT
        CP_LJSD.SDBH
        ,CP_LJSD.LJBH
        ,CP_LJSD.CKLX
        ,CP_LJSD.XSMC AS XSMC
        ,CP_LJSD.RQXH
    FROM CP_LJSD
   WHERE EXISTS
            (SELECT 1
               FROM CP_ZXJL, CP_ZLHD
              WHERE     (CP_ZXJL.HDBH = CP_ZLHD.HDBH)
                    AND (CP_ZXJL.SDBH = CP_LJSD.SDBH)
                    AND (    (CP_ZXJL.DQZT = :"SYS_B_0")
                         AND (CP_ZXJL.YSXH = :"SYS_B_1")
                         AND (CP_ZXJL.BRLJ = :al_brlj)
                         AND (   CP_ZXJL.ZCWC <= :adt_date
                              OR CP_ZXJL.ZZKS <= :adt_date)))
ORDER BY CP_LJSD.RQXH;


--sql_id=d3g3mkq448tr8
SELECT DISTINCT zy_brry.brch
                 ,zy_brry.zyh
                 ,zy_brry.mzhm
                 ,zy_brry.brxm
                 ,ys_zy_jbzd.mszd
                 ,emr_zkbz.bzmc
                 , :"SYS_B_0" AS CP_BRLJ
    FROM zy_brry
        ,ys_zy_jbzd
        ,emr_zkbz
        ,EMR_BZGL
        ,GY_JBBM
   WHERE     zy_brry.cypb = :"SYS_B_1"
         AND TO_CHAR (zy_brry.zyh) = ys_zy_jbzd.jzhm
         AND ys_zy_jbzd.zdlb = :"SYS_B_2"
         AND ys_zy_jbzd.zdsj >= TRUNC (SYSDATE - :"SYS_B_3")
         AND ys_zy_jbzd.zfbz = :"SYS_B_4"
         AND GY_JBBM.ICD9 = EMR_BZGL.GLBM
         AND EMR_BZGL.cpbz = :"SYS_B_5"
         AND EMR_BZGL.bzbh = emr_zkbz.bzbh
         AND ys_zy_jbzd.mszd LIKE :"SYS_B_6" || GY_JBBM.jbmc || :"SYS_B_7"
         AND (brks = :al_brks OR :al_brks = :"SYS_B_8")
         AND ys_zy_jbzd.tjbz = :"SYS_B_9"
ORDER BY brch, mzhm;

--显示根本没有包括EMR_BZGL,GY_JBBM表。


--sql_id=fuk298uwgb3t9              
SELECT DISTINCT
         ZY_BQYZ.ZYH
        ,ZY_BRRY.BRCH
        ,ZY_BRRY.ZYHM
        ,ZY_BRRY.BRXM
        ,ZY_BRRY.BRXZ
        ,ZY_BRRY.ZLXZ
        , (CASE
              WHEN REGEXP_LIKE (ZY_BRRY.BRCH, :"SYS_B_00", :"SYS_B_01")
              THEN
                 LPAD (ZY_BRRY.BRCH, :"SYS_B_02", :"SYS_B_03")
              ELSE
                 LPAD (ZY_BRRY.BRCH, :"SYS_B_04", :"SYS_B_05")
           END)
            AS PLSX_CH
    FROM ZY_BQYZ, ZY_BRRY
   WHERE     ZY_BQYZ.ZYH = ZY_BRRY.ZYH
         AND (ZY_BQYZ.SRKS = ( :al_hsql))
         AND (ZY_BQYZ.JFBZ = :"SYS_B_06" OR ZY_BQYZ.JFBZ = :"SYS_B_07")
         AND (ZY_BQYZ.XMLX > :"SYS_B_08")
         AND (ZY_BQYZ.LSBZ = :"SYS_B_09" OR ZY_BQYZ.LSBZ = :"SYS_B_10")
         AND (ZY_BQYZ.YZPB = :"SYS_B_11")
         AND (ZY_BQYZ.SYBZ <> :"SYS_B_12")
         AND (   ZY_BQYZ.QRSJ IS NULL
              OR (ZY_BQYZ.TZSJ > ZY_BQYZ.QRSJ)
              OR (ZY_BQYZ.TZSJ IS NULL))
         AND (ZY_BQYZ.QRSJ <= :ad_today OR ZY_BQYZ.QRSJ IS NULL)
         AND ZY_BQYZ.ZXBQ IS NULL
ORDER BY PLSX_CH, ZY_BRRY.BRCH

--注意一个细节DISTINCT后面跟的是ZY_BQYZ.ZYH,视乎不是ZY_BRRY表的字段,实际上看看后面的连接条件ZY_BQYZ.ZYH = ZY_BRRY.ZYH就明白,
--完成可以换成ZY_BRRY.ZYH,这样实际上就是一个使用半连接exists的例子。

--sql_id=cfjq09vz71s5y

SELECT DISTINCT
       YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
  FROM YF_DB01
      ,YF_DB02
      ,YK_TYPK
      ,YF_YFLB
WHERE     (YF_DB02.SQYF = YF_DB01.SQYF)
       AND (YF_DB02.SQDH = YF_DB01.SQDH)
       AND (YF_DB02.YPXH = YK_TYPK.YPXH)
       AND (YF_DB01.SQYF = YF_YFLB.YFSB)
       AND (YF_DB01.CKBZ = :"SYS_B_0")
UNION ALL
SELECT DISTINCT
       GY_KSDM.KSMC AS KSMC, GY_KSDM.KSDM AS KSDM, GY_KSDM.PYDM AS PYDM
  FROM YF_CK01
      ,YF_CK02
      ,YK_TYPK
      ,GY_KSDM
WHERE     (YF_CK01.YFSB = YF_CK02.YFSB)
       AND (YF_CK01.CKDH = YF_CK02.CKDH)
       AND (YF_CK01.CKFS = YF_CK02.CKFS)
       AND (YF_CK01.KSDM = GY_KSDM.KSDM)
       AND (YF_CK02.YPXH = YK_TYPK.YPXH)

--我看了这个语句的平均每次的逻辑读18619.

> @ bind_cap cfjq09vz71s5y
C200
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*  select ksdm,  ksmc from gy_ksdm where bqsy='Y' or zysy='Y'  */      SELECT distinct YF_YFLB.YFMC AS KSMC,    YF_YFLB.YFSB AS  KSDM,    YF_YFLB.PYDM AS PYDM          FROM YF_DB01,   YF_DB02,   YK_T
YPK,   YF_YFLB               WHERE (YF_DB02.SQYF = YF_DB01.SQYF)       and (YF_DB02.SQDH = YF_DB01.SQDH)       and (YF_DB02.YPXH = YK_TYPK.YPXH)       and (YF_DB01.SQYF = YF_YFLB.YFSB)       and (YF_D
B01.CKBZ = :"SYS_B_0")    union all      SELECT distinct GY_KSDM.KSMC AS KSMC,    GY_KSDM.KSDM AS KSDM,   GY_KSDM.PYDM AS PYDM    FROM YF_CK01,   YF_CK02,   YK_TYPK,   GY_KSDM     WHERE (YF_CK01.YFSB
= YF_CK02.YFSB)       and (YF_CK01.CKDH = YF_CK02.CKDH)       and (YF_CK01.CKFS = YF_CK02.CKFS)       and (YF_CK01.KSDM = GY_KSDM.KSDM)       and (YF_CK02.YPXH = YK_TYPK.YPXH)


SQL_ID        CHILD_NUMBER WAS NAME                   POSITION MAX_LENGTH LAST_CAPTURED       DATATYPE_STRING VALUE_STRING
------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- -------------
cfjq09vz71s5y            0 YES :SYS_B_0                      1         22 2015-12-09 17:03:23 NUMBER          1

Plan hash value: 429408998
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |      1 |        |       |       |  4967 (100)|          |    259 |00:00:00.27 |   20343 |       |       |          |
|   1 |  UNION-ALL                        |                |      1 |        |       |       |            |          |    259 |00:00:00.27 |   20343 |       |       |          |
|   2 |   HASH UNIQUE                     |                |      1 |    152K|  9222K|     9M|  2957   (1)| 00:00:36 |     79 |00:00:00.23 |    3041 |    16M|  5692K| 4596K (0)|
|*  3 |    HASH JOIN                      |                |      1 |    152K|  9222K|       |   831   (1)| 00:00:10 |    155K|00:00:00.20 |    3041 |  4904K|  4904K| 1649K (0)|
|   4 |     INDEX STORAGE FAST FULL SCAN  | PK_YK_TYPK     |      1 |   2737 | 13685 |       |     3   (0)| 00:00:01 |   2737 |00:00:00.01 |      11 |  1025K|  1025K|          |
|*  5 |     HASH JOIN                     |                |      1 |    152K|  8478K|       |   828   (1)| 00:00:10 |    155K|00:00:00.16 |    3030 |  2455K|  2455K| 1253K (0)|
|   6 |      TABLE ACCESS STORAGE FULL    | YF_YFLB        |      1 |     88 |  2904 |       |     3   (0)| 00:00:01 |     88 |00:00:00.01 |       6 |  1025K|  1025K|          |
|*  7 |      HASH JOIN                    |                |      1 |    152K|  3570K|       |   825   (1)| 00:00:10 |    155K|00:00:00.12 |    3024 |  5615K|  5615K| 4192K (0)|
|   8 |       JOIN FILTER CREATE          | :BF0000        |      1 |  53855 |   578K|       |   139   (1)| 00:00:02 |  54257 |00:00:00.02 |     504 |       |       |          |
|*  9 |        TABLE ACCESS STORAGE FULL  | YF_DB01        |      1 |  53855 |   578K|       |   139   (1)| 00:00:02 |  54257 |00:00:00.01 |     504 |  1025K|  1025K|          |
|  10 |       JOIN FILTER USE             | :BF0000        |      1 |    152K|  1933K|       |   685   (1)| 00:00:09 |    156K|00:00:00.04 |    2520 |       |       |          |
|* 11 |        TABLE ACCESS STORAGE FULL  | YF_DB02        |      1 |    152K|  1933K|       |   685   (1)| 00:00:09 |    156K|00:00:00.03 |    2520 |  1025K|  1025K|          |
|  12 |   HASH UNIQUE                     |                |      1 |  14882 |   901K|  1008K|  2011   (1)| 00:00:25 |    180 |00:00:00.04 |   17302 |  2908K|  2908K| 2735K (0)|
|  13 |    NESTED LOOPS                   |                |      1 |  14882 |   901K|       |  1801   (1)| 00:00:22 |  15216 |00:00:00.04 |   17302 |       |       |          |
|* 14 |     HASH JOIN                     |                |      1 |  14882 |   828K|       |  1801   (1)| 00:00:22 |  15216 |00:00:00.02 |    1986 |  2326K|  2326K| 1267K (0)|
|  15 |      JOIN FILTER CREATE           | :BF0001        |      1 |   5063 |   197K|       |  1731   (0)| 00:00:21 |   5154 |00:00:00.01 |    1734 |       |       |          |
|* 16 |       HASH JOIN                   |                |      1 |   5063 |   197K|       |  1731   (0)| 00:00:21 |   5154 |00:00:00.01 |    1734 |  2701K|  2701K| 1256K (0)|
|  17 |        TABLE ACCESS STORAGE FULL  | GY_KSDM        |      1 |    427 | 10248 |       |     3   (0)| 00:00:01 |    428 |00:00:00.01 |       6 |  1025K|  1025K|          |
|  18 |        TABLE ACCESS BY INDEX ROWID| YF_CK01        |      1 |   5063 | 81008 |       |  1728   (0)| 00:00:21 |   5191 |00:00:00.01 |    1728 |       |       |          |
|* 19 |         INDEX FULL SCAN           | I_YF_CK01_KSDM |      1 |   5191 |       |       |    10   (0)| 00:00:01 |   5191 |00:00:00.01 |      10 |  1025K|  1025K|          |
|  20 |      JOIN FILTER USE              | :BF0001        |      1 |  16178 |   268K|       |    69   (0)| 00:00:01 |  16075 |00:00:00.01 |     252 |       |       |          |
|* 21 |       TABLE ACCESS STORAGE FULL   | YF_CK02        |      1 |  16178 |   268K|       |    69   (0)| 00:00:01 |  16075 |00:00:00.01 |     252 |  1025K|  1025K|          |
|* 22 |     INDEX UNIQUE SCAN             | PK_YK_TYPK     |  15216 |      1 |     5 |       |     0   (0)|          |  15216 |00:00:00.01 |   15316 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 170966610
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |      1 |        |       |       |  3252 (100)|          |    259 |00:00:00.27 |   18660 |       |       |          |
|   1 |  UNION-ALL                       |            |      1 |        |       |       |            |          |    259 |00:00:00.27 |   18660 |       |       |          |
|   2 |   HASH UNIQUE                    |            |      1 |    152K|  9222K|     9M|  2957   (1)| 00:00:36 |     79 |00:00:00.23 |    3041 |    16M|  5692K| 4604K (0)|
|*  3 |    HASH JOIN                     |            |      1 |    152K|  9222K|       |   831   (1)| 00:00:10 |    155K|00:00:00.20 |    3041 |  4904K|  4904K| 1733K (0)|
|   4 |     INDEX STORAGE FAST FULL SCAN | PK_YK_TYPK |      1 |   2737 | 13685 |       |     3   (0)| 00:00:01 |   2737 |00:00:00.01 |      11 |  1025K|  1025K|          |
|*  5 |     HASH JOIN                    |            |      1 |    152K|  8478K|       |   828   (1)| 00:00:10 |    155K|00:00:00.16 |    3030 |  2455K|  2455K| 1244K (0)|
|   6 |      TABLE ACCESS STORAGE FULL   | YF_YFLB    |      1 |     88 |  2904 |       |     3   (0)| 00:00:01 |     88 |00:00:00.01 |       6 |  1025K|  1025K|          |
|*  7 |      HASH JOIN                   |            |      1 |    152K|  3570K|       |   825   (1)| 00:00:10 |    155K|00:00:00.12 |    3024 |  5615K|  5615K| 4184K (0)|
|   8 |       JOIN FILTER CREATE         | :BF0000    |      1 |  53855 |   578K|       |   139   (1)| 00:00:02 |  54257 |00:00:00.02 |     504 |       |       |          |
|*  9 |        TABLE ACCESS STORAGE FULL | YF_DB01    |      1 |  53855 |   578K|       |   139   (1)| 00:00:02 |  54257 |00:00:00.01 |     504 |  1025K|  1025K|          |
|  10 |       JOIN FILTER USE            | :BF0000    |      1 |    152K|  1933K|       |   685   (1)| 00:00:09 |    156K|00:00:00.04 |    2520 |       |       |          |
|* 11 |        TABLE ACCESS STORAGE FULL | YF_DB02    |      1 |    152K|  1933K|       |   685   (1)| 00:00:09 |    156K|00:00:00.03 |    2520 |  1025K|  1025K|          |
|  12 |   HASH UNIQUE                    |            |      1 |  14882 |   901K|  1008K|   295   (1)| 00:00:04 |    180 |00:00:00.04 |   15619 |  2908K|  2908K| 2735K (0)|
|  13 |    NESTED LOOPS                  |            |      1 |  14882 |   901K|       |    86   (2)| 00:00:02 |  15216 |00:00:00.03 |   15619 |       |       |          |
|* 14 |     HASH JOIN                    |            |      1 |  14882 |   828K|       |    85   (0)| 00:00:02 |  15216 |00:00:00.02 |     303 |  2326K|  2326K| 1306K (0)|
|  15 |      JOIN FILTER CREATE          | :BF0001    |      1 |   5063 |   197K|       |    16   (0)| 00:00:01 |   5154 |00:00:00.01 |      51 |       |       |          |
|* 16 |       HASH JOIN                  |            |      1 |   5063 |   197K|       |    16   (0)| 00:00:01 |   5154 |00:00:00.01 |      51 |  2701K|  2701K| 1106K (0)|
|  17 |        JOIN FILTER CREATE        | :BF0002    |      1 |    427 | 10248 |       |     3   (0)| 00:00:01 |    428 |00:00:00.01 |       6 |       |       |          |
|  18 |         TABLE ACCESS STORAGE FULL| GY_KSDM    |      1 |    427 | 10248 |       |     3   (0)| 00:00:01 |    428 |00:00:00.01 |       6 |  1025K|  1025K|          |
|  19 |        JOIN FILTER USE           | :BF0002    |      1 |   5063 | 81008 |       |    13   (0)| 00:00:01 |   5154 |00:00:00.01 |      45 |       |       |          |
|* 20 |         TABLE ACCESS STORAGE FULL| YF_CK01    |      1 |   5063 | 81008 |       |    13   (0)| 00:00:01 |   5154 |00:00:00.01 |      45 |  1025K|  1025K|          |
|  21 |      JOIN FILTER USE             | :BF0001    |      1 |  16178 |   268K|       |    69   (0)| 00:00:01 |  16075 |00:00:00.01 |     252 |       |       |          |
|* 22 |       TABLE ACCESS STORAGE FULL  | YF_CK02    |      1 |  16178 |   268K|       |    69   (0)| 00:00:01 |  16075 |00:00:00.01 |     252 |  1025K|  1025K|          |
|* 23 |     INDEX UNIQUE SCAN            | PK_YK_TYPK |  15216 |      1 |     5 |       |     0   (0)|          |  15216 |00:00:00.01 |   15316 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--我改写如下:
SELECT YF_YFLB.YFMC AS KSMC, YF_YFLB.YFSB AS KSDM, YF_YFLB.PYDM AS PYDM
  FROM YF_YFLB
WHERE EXISTS
          (SELECT 1
             FROM YF_DB01, YF_DB02, YK_TYPK
            WHERE     (YF_DB02.SQYF = YF_DB01.SQYF)
                  AND (YF_DB02.SQDH = YF_DB01.SQDH)
                  AND (YF_DB02.YPXH = YK_TYPK.YPXH)
                  AND (YF_DB01.SQYF = YF_YFLB.YFSB)
                  AND (YF_DB01.CKBZ = 1))
UNION ALL
SELECT GY_KSDM.KSMC AS KSMC, GY_KSDM.KSDM AS KSDM, GY_KSDM.PYDM AS PYDM
  FROM GY_KSDM
WHERE EXISTS
          (SELECT /*+  PUSH_SUBQ   */ 1
             FROM YF_CK01, YF_CK02, YK_TYPK
            WHERE     (YF_CK01.YFSB = YF_CK02.YFSB)
                  AND (YF_CK01.CKDH = YF_CK02.CKDH)
                  AND (YF_CK01.CKFS = YF_CK02.CKFS)
                  AND (YF_CK01.KSDM = GY_KSDM.KSDM)
                  AND (YF_CK02.YPXH = YK_TYPK.YPXH));

Plan hash value: 2520417422

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                          |      1 |        |       |   320 (100)|          |    259 |00:00:00.01 |    2443 |       |       |          |
|   1 |  UNION-ALL                      |                          |      1 |        |       |            |          |    259 |00:00:00.01 |    2443 |       |       |          |
|*  2 |   FILTER                        |                          |      1 |        |       |            |          |     79 |00:00:00.01 |     748 |       |       |          |
|   3 |    TABLE ACCESS STORAGE FULL    | YF_YFLB                  |      1 |     88 |  2904 |     3   (0)| 00:00:01 |     88 |00:00:00.01 |       7 |  1025K|  1025K|          |
|   4 |    NESTED LOOPS                 |                          |     88 |      3 |    87 |     7   (0)| 00:00:01 |     79 |00:00:00.01 |     741 |       |       |          |
|   5 |     NESTED LOOPS                |                          |     88 |      3 |    72 |     7   (0)| 00:00:01 |     79 |00:00:00.01 |     583 |       |       |          |
|   6 |      TABLE ACCESS BY INDEX ROWID| YF_DB02                  |     88 |   1642 | 21346 |     4   (0)| 00:00:01 |     80 |00:00:00.01 |     344 |       |       |          |
|*  7 |       INDEX RANGE SCAN          | I_YF_DB02_SQDH           |     88 |   1928 |       |     3   (0)| 00:00:01 |     80 |00:00:00.01 |     264 |  1025K|  1025K|          |
|*  8 |      TABLE ACCESS BY INDEX ROWID| YF_DB01                  |     80 |      1 |    11 |     1   (0)| 00:00:01 |     79 |00:00:00.01 |     239 |       |       |          |
|*  9 |       INDEX UNIQUE SCAN         | PK_YF_DB01               |     80 |      1 |       |     0   (0)|          |     80 |00:00:00.01 |     159 |  1025K|  1025K|          |
|* 10 |     INDEX UNIQUE SCAN           | PK_YK_TYPK               |     79 |      1 |     5 |     0   (0)|          |     79 |00:00:00.01 |     158 |  1025K|  1025K|          |
|* 11 |   TABLE ACCESS STORAGE FULL     | GY_KSDM                  |      1 |     21 |   504 |     3   (0)| 00:00:01 |    180 |00:00:00.01 |    1695 |  1025K|  1025K|          |
|  12 |    NESTED LOOPS                 |                          |    428 |      3 |   114 |     6   (0)| 00:00:01 |    180 |00:00:00.01 |    1688 |       |       |          |
|  13 |     NESTED LOOPS                |                          |    428 |      3 |    99 |     6   (0)| 00:00:01 |    180 |00:00:00.01 |    1328 |       |       |          |
|  14 |      TABLE ACCESS BY INDEX ROWID| YF_CK01                  |    428 |     29 |   464 |     2   (0)| 00:00:01 |    180 |00:00:00.01 |     788 |       |       |          |
|* 15 |       INDEX RANGE SCAN          | I_YF_CK01_KSDM           |    428 |     28 |       |     1   (0)| 00:00:01 |    180 |00:00:00.01 |     608 |  1025K|  1025K|          |
|  16 |      TABLE ACCESS BY INDEX ROWID| YF_CK02                  |    180 |      2 |    34 |     2   (0)| 00:00:01 |    180 |00:00:00.01 |     540 |       |       |          |
|* 17 |       INDEX RANGE SCAN          | I_YF_CK02_YFSB_CKFS_CKDH |    180 |      2 |       |     1   (0)| 00:00:01 |    180 |00:00:00.01 |     360 |  1025K|  1025K|          |
|* 18 |     INDEX UNIQUE SCAN           | PK_YK_TYPK               |    180 |      1 |     5 |     0   (0)|          |    180 |00:00:00.01 |     360 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--如果对比A-TIME。更加要命的是实际上这样写一点问题都没有:
--还有1大堆,看来不至一个人,而是一个团队再犯这个错误,这个就是国内IT的现状,可悲!!!!

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2488
  • 访问量
    6293180