问题描述:
Oracle 库里有一视图 A,select * from A ;1秒出结果。 返回数据370条。我新建了一张表B, 执行 insert into B select * from A  却执行了6分30秒。下面是视图的执行计划和insert into 的执行计划,求高手指点。视图的:
insert into 的
 

解决方案 »

  1.   

    这么复杂的select能叫select * from A从insert执行6分钟来看,select不可能1秒出的,之所以一秒出,是因为plsql developer自作主张只给你返回头几行的关系,你的性能问题在于select,而不是insert。执行计划不完整,没法分析更多,先在最后一个经历TABLE ACCESS FULL的表的关联字段上加上索引试试。
      

  2.   

    视图内SQL:
    SELECT 
      TO_NUMBER(TO_CHAR(C.TRD_DT, 'YYYYMMDD')) AS JYRQ,
           A.TRD_CODE AS ZSDM,
           A.CHI_NAME AS ZSMC,
           A.ENG_NAME AS ZSYW,
           B.TRD_CODE AS ZQDM,
           B.SECU_SHT AS ZQMC,
           D.ENG_NAME AS ZQYW,
           CASE
             WHEN D.EXCH_CODE = 101 THEN
              '1'
             WHEN D.EXCH_CODE = 105 THEN
              '2'
           END AS ZQSC,
           C.CLS_PRC AS JSPJ,
           E.TTL_SHR AS ZGB,
           NULL AS JSGB,
           f.wt_val AS QZYZ,
           C.TMKT_VAL AS ZSZ,
           NULL AS JSSZ,
           f.wt_val AS QZ,
           'CNY' AS JYHB
      FROM IDX_BAS_INFO A 
      JOIN IDX_COMP B --指数成分构成
        ON A.SECU_ID = B.IDX_SECU_ID
        LEFT join idx_comp_wt F 
           join (select IDX_SECU_ID ,max(trd_dt)as trd_dt from idx_comp_wt group by IDX_SECU_ID ) FF
           on f.IDX_SECU_ID=ff.IDX_SECU_ID and f.trd_dt=ff.trd_dt
        on  B.IDX_SECU_ID=F.IDX_SECU_ID and b.secu_id=f.secu_id
        LEFT JOIN STKCN_BAS_INFO D 
        --ON F.SECU_ID = D.SECU_ID
        ON B.SECU_ID=D.SECU_ID
        LEFT JOIN STKCN_MKT_QUOT_IDX C 
      JO
    视图执行计划:
     Plan Hash Value  : 3421027833 ---------------------------------------------------------------------------------------------------------
    | Id   | Operation                           | Name                  | Rows  | Bytes  | Cost | Time     |
    ---------------------------------------------------------------------------------------------------------
    |    0 | SELECT STATEMENT                    |                       |    99 |  36729 | 1515 | 00:00:19 |
    |  * 1 |   HASH JOIN OUTER                   |                       |    99 |  36729 | 1515 | 00:00:19 |
    |    2 |    NESTED LOOPS OUTER               |                       |    95 |  34200 | 1357 | 00:00:17 |
    |    3 |     NESTED LOOPS OUTER              |                       |    95 |  30685 |  311 | 00:00:04 |
    |  * 4 |      HASH JOIN RIGHT OUTER          |                       |    95 |  19570 |  216 | 00:00:03 |
    |    5 |       VIEW                          |                       |     2 |     78 |   55 | 00:00:01 |
    |    6 |        NESTED LOOPS                 |                       |     2 |     88 |   55 | 00:00:01 |
    |    7 |         NESTED LOOPS                |                       |     2 |     88 |   55 | 00:00:01 |
    |    8 |          VIEW                       |                       |     1 |     22 |   27 | 00:00:01 |
    |    9 |           HASH GROUP BY             |                       |     1 |     13 |   27 | 00:00:01 |
    | * 10 |            INDEX RANGE SCAN         | IDX_IDX_COMP_WT       |  4646 |  60398 |   27 | 00:00:01 |
    | * 11 |          INDEX RANGE SCAN           | IDX_IDX_COMP_WT       |     2 |        |   26 | 00:00:01 |
    |   12 |         TABLE ACCESS BY INDEX ROWID | IDX_COMP_WT           |     2 |     44 |   28 | 00:00:01 |
    |   13 |       NESTED LOOPS                  |                       |    95 |  15865 |  161 | 00:00:02 |
    |   14 |        TABLE ACCESS BY INDEX ROWID  | IDX_BAS_INFO          |     1 |    139 |    2 | 00:00:01 |
    | * 15 |         INDEX UNIQUE SCAN           | IX_IDX_BAS_INFO       |     1 |        |    1 | 00:00:01 |
    | * 16 |        TABLE ACCESS BY INDEX ROWID  | IDX_COMP              |    95 |   2660 |  159 | 00:00:02 |
    | * 17 |         INDEX RANGE SCAN            | IX_IDX_COMP           |   190 |        |    2 | 00:00:01 |
    |   18 |      TABLE ACCESS BY INDEX ROWID    | STKCN_BAS_INFO        |     1 |    117 |    1 | 00:00:01 |
    | * 19 |       INDEX UNIQUE SCAN             | IX_STKCN_BAS_INFO     |     1 |        |    0 | 00:00:01 |
    |   20 |     VIEW PUSHED PREDICATE           |                       |     1 |     37 |   11 | 00:00:01 |
    |   21 |      NESTED LOOPS                   |                       |     1 |     46 |   11 | 00:00:01 |
    |   22 |       NESTED LOOPS                  |                       |     1 |     46 |   11 | 00:00:01 |
    |   23 |        VIEW                         |                       |     1 |     22 |    8 | 00:00:01 |
    |   24 |         SORT GROUP BY               |                       |     1 |     13 |    8 | 00:00:01 |
    | * 25 |          INDEX RANGE SCAN           | IX_STKCN_MKT_QUOT_IDX |  1611 |  20943 |    8 | 00:00:01 |
    | * 26 |        INDEX RANGE SCAN             | IX_STKCN_MKT_QUOT_IDX |     1 |        |    2 | 00:00:01 |
    |   27 |       TABLE ACCESS BY INDEX ROWID   | STKCN_MKT_QUOT_IDX    |     1 |     24 |    3 | 00:00:01 |
    |   28 |    TABLE ACCESS FULL                | COMCN_SHR_CHG_NEW     | 17996 | 197956 |  158 | 00:00:02 |
    ---------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ------------------------------------------
    * 1 - access("D"."COM_ID"="E"."COM_ID"(+))
    * 4 - access("B"."SECU_ID"="F"."SECU_ID"(+) AND "B"."IDX_SECU_ID"="F"."IDX_SECU_ID"(+))
    * 10 - access("IDX_SECU_ID"=37627)
    * 11 - access("F"."IDX_SECU_ID"=37627 AND "F"."TRD_DT"="FF"."TRD_DT")
    * 11 - filter("F"."TRD_DT"="FF"."TRD_DT")
    * 15 - access("A"."SECU_ID"=37627)
    * 16 - filter("B"."NEW_STS"='1')
    * 17 - access("B"."IDX_SECU_ID"=37627)
    * 19 - access("B"."SECU_ID"="D"."SECU_ID"(+))
    * 25 - access("SECU_ID"="D"."SECU_ID")
    * 26 - access("C"."SECU_ID"="D"."SECU_ID" AND "C"."TRD_DT"="CC"."TRD_DT")
    * 26 - filter("C"."SECU_ID"="CC"."SECU_ID")insert 执行计划:
     Plan Hash Value  : 3487003956 -------------------------------------------------------------------------------------------------------------
    | Id   | Operation                            | Name                  | Rows  | Bytes  | Cost    | Time     |
    -------------------------------------------------------------------------------------------------------------
    |    0 | INSERT STATEMENT                     |                       |    99 |  34551 | 1374590 | 04:34:56 |
    |    1 |   LOAD TABLE CONVENTIONAL            | HSZG_ZSCFQZ_JAMMY_T   |       |        |         |          |
    |  * 2 |    HASH JOIN OUTER                   |                       |    99 |  34551 | 1374590 | 04:34:56 |
    |    3 |     NESTED LOOPS OUTER               |                       |    95 |  32110 | 1374433 | 04:34:54 |
    |    4 |      NESTED LOOPS OUTER              |                       |    95 |  28595 |    3014 | 00:00:37 |
    |    5 |       NESTED LOOPS OUTER             |                       |    95 |  26980 |     256 | 00:00:04 |
    |    6 |        NESTED LOOPS                  |                       |    95 |  15865 |     161 | 00:00:02 |
    |    7 |         TABLE ACCESS BY INDEX ROWID  | IDX_BAS_INFO          |     1 |    139 |       2 | 00:00:01 |
    |  * 8 |          INDEX UNIQUE SCAN           | IX_IDX_BAS_INFO       |     1 |        |       1 | 00:00:01 |
    |  * 9 |         TABLE ACCESS BY INDEX ROWID  | IDX_COMP              |    95 |   2660 |     159 | 00:00:02 |
    | * 10 |          INDEX RANGE SCAN            | IX_IDX_COMP           |   190 |        |       2 | 00:00:01 |
    |   11 |        TABLE ACCESS BY INDEX ROWID   | STKCN_BAS_INFO        |     1 |    117 |       1 | 00:00:01 |
    | * 12 |         INDEX UNIQUE SCAN            | IX_STKCN_BAS_INFO     |     1 |        |       0 | 00:00:01 |
    |   13 |       VIEW PUSHED PREDICATE          |                       |     1 |     17 |      29 | 00:00:01 |
    | * 14 |        FILTER                        |                       |       |        |         |          |
    |   15 |         NESTED LOOPS                 |                       |     1 |     44 |      29 | 00:00:01 |
    |   16 |          NESTED LOOPS                |                       |     1 |     44 |      29 | 00:00:01 |
    |   17 |           VIEW                       |                       |     1 |     22 |      27 | 00:00:01 |
    |   18 |            SORT GROUP BY             |                       |     1 |     13 |      27 | 00:00:01 |
    | * 19 |             FILTER                   |                       |       |        |         |          |
    | * 20 |              INDEX RANGE SCAN        | IDX_IDX_COMP_WT       |  4646 |  60398 |      27 | 00:00:01 |
    | * 21 |           INDEX UNIQUE SCAN          | IDX_IDX_COMP_WT       |     1 |        |       1 | 00:00:01 |
    |   22 |          TABLE ACCESS BY INDEX ROWID | IDX_COMP_WT           |     1 |     22 |       2 | 00:00:01 |
    |   23 |      VIEW PUSHED PREDICATE           |                       |     1 |     37 |   14436 | 00:02:54 |
    |   24 |       NESTED LOOPS                   |                       |     1 |     33 |   14436 | 00:02:54 |
    |   25 |        TABLE ACCESS BY INDEX ROWID   | STKCN_MKT_QUOT_IDX    |  1611 |  38664 |    1531 | 00:00:19 |
    | * 26 |         INDEX RANGE SCAN             | IX_STKCN_MKT_QUOT_IDX |  1611 |        |       8 | 00:00:01 |
    | * 27 |        VIEW PUSHED PREDICATE         |                       |     1 |      9 |       8 | 00:00:01 |
    | * 28 |         FILTER                       |                       |       |        |         |          |
    |   29 |          SORT AGGREGATE              |                       |     1 |     13 |         |          |
    | * 30 |           INDEX RANGE SCAN           | IX_STKCN_MKT_QUOT_IDX |  1611 |  20943 |       8 | 00:00:01 |
    |   31 |     TABLE ACCESS FULL                | COMCN_SHR_CHG_NEW     | 17996 | 197956 |     158 | 00:00:02 |
    -------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - access("D"."COM_ID"="E"."COM_ID"(+))
    * 8 - access("A"."SECU_ID"=37627)
    * 9 - filter("B"."NEW_STS"='1')
    * 10 - access("B"."IDX_SECU_ID"=37627)
    * 12 - access("B"."SECU_ID"="D"."SECU_ID"(+))
    * 14 - filter("B"."IDX_SECU_ID"=37627)
    * 19 - filter("B"."IDX_SECU_ID"=37627)
    * 20 - access("IDX_SECU_ID"=37627)
    * 21 - access("F"."IDX_SECU_ID"="B"."IDX_SECU_ID" AND "F"."SECU_ID"="B"."SECU_ID" AND "F"."TRD_DT"="FF"."TRD_DT")
    * 21 - filter("F"."IDX_SECU_ID"=37627)
    * 26 - access("C"."SECU_ID"="D"."SECU_ID")
    * 27 - filter("C"."TRD_DT"="CC"."TRD_DT")
    * 28 - filter(COUNT(*)>0)
    * 30 - access("SECU_ID"="C"."SECU_ID")
      

  3.   

    视图SQL:
     SELECT 
      TO_NUMBER(TO_CHAR(C.TRD_DT, 'YYYYMMDD')) AS JYRQ,
           A.TRD_CODE AS ZSDM,
           A.CHI_NAME AS ZSMC,
           A.ENG_NAME AS ZSYW,
           B.TRD_CODE AS ZQDM,
           B.SECU_SHT AS ZQMC,
           D.ENG_NAME AS ZQYW,
           CASE
             WHEN D.EXCH_CODE = 101 THEN
              '1'
             WHEN D.EXCH_CODE = 105 THEN
              '2'
           END AS ZQSC,
           C.CLS_PRC AS JSPJ,
           E.TTL_SHR AS ZGB,
           NULL AS JSGB,
           f.wt_val AS QZYZ,
           C.TMKT_VAL AS ZSZ,
           NULL AS JSSZ,
           f.wt_val AS QZ,
           'CNY' AS JYHB
      FROM IDX_BAS_INFO A 
      JOIN IDX_COMP B
        ON A.SECU_ID = B.IDX_SECU_ID
        LEFT join idx_comp_wt F 
           join (select IDX_SECU_ID ,max(trd_dt)as trd_dt from idx_comp_wt group by IDX_SECU_ID ) FF
           on f.IDX_SECU_ID=ff.IDX_SECU_ID and f.trd_dt=ff.trd_dt
        on  B.IDX_SECU_ID=F.IDX_SECU_ID and b.secu_id=f.secu_id
        LEFT JOIN STKCN_BAS_INFO D 
        --ON F.SECU_ID = D.SECU_ID
        ON B.SECU_ID=D.SECU_ID
        LEFT JOIN STKCN_MKT_QUOT_IDX C 
      JOIN (SELECT SECU_ID, MAX(TRD_DT) AS TRD_DT
              FROM STKCN_MKT_QUOT_IDX
             GROUP BY SECU_ID) CC
        ON C.SECU_ID = CC.SECU_ID   AND C.TRD_DT = CC.TRD_DT
        ON D.SECU_ID = C.SECU_ID
         LEFT JOIN COMCN_SHR_CHG_NEW E 
        ON D.COM_ID = E.COM_ID
        WHERE  B.NEW_STS='1'  and b.IDX_SECU_ID in (37267)
      

  4.   

    执行计划完全不一样,cost也差了1000倍,手动加hint试试。
      

  5.   

    先在COMCN_SHR_CHG_NEW表的连接字段COM_ID上加上索引试试