通过视图查询慢

zyo_320

视图内容：
create or replace view v_market_tran_info_base
(id, market_id, market_name, transantion_date, wholesaler_id, wholesaler_name, retailer_id, retailer_name, tran_id, update_time)
as
select
        row_number() over(order by t.UPDATE_TIME),
        t.MARKET_ID,
        t.MARKET_NAME,
        t.TRANSANTION_DATE,
        t.WHOLESALER_ID,
        t.WHOLESALER_NAME,
        t.RETAILER_ID,
        t.RETAILER_NAME,
        t.TRAN_ID,
        t.UPDATE_TIME

        from
        (
        select
        agen.rrstAgenNo as MARKET_ID,
        agen.fullname as MARKET_NAME,
        to_char(to_date(ch. ClinchDate, 'YYYYMMDD'), 'YYYY-MM-DD') as TRANSANTION_DATE,
        sel.code as WHOLESALER_ID,
        sel.name as WHOLESALER_NAME,
        buy.code as RETAILER_ID,
        buy.name as RETAILER_NAME,
        chd.retailRrstno as TRAN_ID,
         ch.chktime as UPDATE_TIME

        from T_WS_CLINCH ch,
        T_WS_CLINCHD chd,
        T_SYS_CODE_AGEN agen,
        T_REG_BUSINESS sel,
        T_REG_BUSINESS buy

        where chd.clinchNo = ch.clinchNo
        and ch.agenId = agen.id
        and ch.busiId = sel.id
        and ch.buyBusiId = buy.id
        and ch.busiId <> ch.buyBusiId
        and ch.chktime > to_date('2011-10-1','YYYY-MM-DD')
  and chd.price<>0

        UNION ALL

        select
        agen.rrstAgenNo as MARKET_ID,
        agen.fullname as MARKET_NAME,
        to_char(to_date(fm. ClinchDate, 'YYYYMMDD'), 'YYYY-MM-DD') as TRANSANTION_DATE ,
        sel.code as WHOLESALER_ID,
         sel.name as WHOLESALER_NAME,
         buy.code as RETAILER_ID,
        buy.name as RETAILER_NAME,
        fmd.retailRrstno as TRAN_ID,
        fm.chktime as UPDATE_TIME

        from T_WS_CLINCHFM fm,
        T_WS_CLINCHFMD fmd,
        T_SYS_CODE_AGEN agen,
        T_REG_BUSINESS sel,
        T_REG_BUSINESS buy

        where fmd.clinchNo = fm.clinchNo
        and fm.agenId = agen.id
        and fm.busiId = sel.id
        and fm.buyBusiId = buy.id
        and fm.chktime > to_date('2011-10-1','YYYY-MM-DD')
  and fmd.price<>0

        UNION ALL

        select
        agen.rrstAgenNo as MARKET_ID,
        agen.fullname as MARKET_NAME,
        to_char(to_date(vegch. ClinchDate, 'YYYYMMDD'), 'YYYY-MM-DD') as TRANSANTION_DATE,
        sel.code as WHOLESALER_ID,
        sel.name as WHOLESALER_NAME,
         buy.code as RETAILER_ID,
        buy.name as RETAILER_NAME,
         vegchd.retailRrstno as TRAN_ID,
         vegch.chktime as UPDATE_TIME

        from T_WSVEG_CLINCH vegch,
        T_WSVEG_CLINCHD vegchd,
        T_SYS_CODE_AGEN agen,
        T_REG_BUSINESS sel,
        T_REG_BUSINESS buy

        where vegchd.clinchNo = vegch.clinchNo
        and vegch.agenId = agen.id
        and vegch.busiId = sel.id
        and vegch.buyBusiId = buy.id
        and vegch.chktime > to_date('2011-11-1','YYYY-MM-DD')
  and vegchd.price<>0) t

        order by t.UPDATE_TIME;

通过视图执行的sql语句：
explain plan for
SELECT MARKET_ID,COUNT(WHOLESALER_ID) * 10 PLAN_NUM --上报过数据的业户数*10            
                 FROM (SELECT DISTINCT MARKET_ID, WHOLESALER_ID                           
                             FROM V_MARKET_TRAN_INFO_BASE                                
                                   WHERE TRANSANTION_DATE >= '2013-01-04'                                
                                           AND TRANSANTION_DATE <= '2013-02-04')                    
                                            GROUP BY MARKET_ID


执行计划：
PLAN_TABLE_OUTPUT
Plan hash value: 687484610

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                         |  5085K|    67M|       |   111K  (2)| 00:22:17 |
|   1 |  HASH GROUP BY                         |                         |  5085K|    67M|       |   111K  (2)| 00:22:17 |
|   2 |   VIEW                                 |                         |  5085K|    67M|       |   111K  (2)| 00:22:17 |
|   3 |    HASH UNIQUE                         |                         |  5085K|   101M|   311M|   111K  (2)| 00:22:17 |
|*  4 |     VIEW                               | V_MARKET_TRAN_INFO_BASE |  5085K|   101M|       | 78208   (2)| 00:15:39 |
|   5 |      WINDOW SORT                       |                         |  5085K|  1629M|       | 78208   (2)| 00:15:39 |
|   6 |       VIEW                             |                         |  5085K|  1629M|       | 78208   (2)| 00:15:39 |
|   7 |        UNION-ALL                       |                         |       |       |       |            |          |
|*  8 |         HASH JOIN                      |                         |   123K|    21M|       |  2031   (2)| 00:00:25 |
|   9 |          TABLE ACCESS FULL             | T_REG_BUSINESS          | 17223 |   454K|       |   111   (1)| 00:00:02 |
|* 10 |          HASH JOIN                     |                         |   123K|    18M|       |  1919   (2)| 00:00:24 |
|  11 |           TABLE ACCESS FULL            | T_REG_BUSINESS          | 17223 |   454K|       |   111   (1)| 00:00:02 |
|* 12 |           HASH JOIN                    |                         |   123K|    15M|       |  1806   (2)| 00:00:22 |
|  13 |            TABLE ACCESS FULL           | T_SYS_CODE_AGEN         |   207 |  8073 |       |     5   (0)| 00:00:01 |
|* 14 |            HASH JOIN                   |                         |   123K|    10M|  7000K|  1799   (2)| 00:00:22 |
|* 15 |             TABLE ACCESS FULL          | T_WS_CLINCHD            |   123K|  5548K|       |   599   (2)| 00:00:08 |
|* 16 |             TABLE ACCESS FULL          | T_WS_CLINCH             |   143K|  6452K|       |   462   (2)| 00:00:06 |
|* 17 |         TABLE ACCESS BY INDEX ROWID    | T_WS_CLINCHFMD          |     1 |    46 |       |     1   (0)| 00:00:01 |
|  18 |          NESTED LOOPS                  |                         |   563 |   100K|       |  5606   (2)| 00:01:08 |
|  19 |           NESTED LOOPS                 |                         |   562 | 76994 |       |  5493   (2)| 00:01:06 |
|  20 |            NESTED LOOPS                |                         |   562 | 61820 |       |  5437   (2)| 00:01:06 |
|* 21 |             HASH JOIN                  |                         |   562 | 46646 |       |  5381   (2)| 00:01:05 |
|  22 |              TABLE ACCESS FULL         | T_SYS_CODE_AGEN         |   207 |  8073 |       |     5   (0)| 00:00:01 |
|* 23 |              TABLE ACCESS FULL         | T_WS_CLINCHFM           |   562 | 24728 |       |  5375   (2)| 00:01:05 |
|  24 |             TABLE ACCESS BY INDEX ROWID| T_REG_BUSINESS          |     1 |    27 |       |     1   (0)| 00:00:01 |
|* 25 |              INDEX UNIQUE SCAN         | PK_ID                   |     1 |       |       |     1   (0)| 00:00:01 |
|  26 |            TABLE ACCESS BY INDEX ROWID | T_REG_BUSINESS          |     1 |    27 |       |     1   (0)| 00:00:01 |
|* 27 |             INDEX UNIQUE SCAN          | PK_ID                   |     1 |       |       |     1   (0)| 00:00:01 |
|* 28 |           INDEX RANGE SCAN             | KEY_WSCLINCHFMD         |     1 |       |       |     1   (0)| 00:00:01 |
|* 29 |         HASH JOIN                      |                         |  4961K|   856M|       | 70571   (2)| 00:14:07 |
|  30 |          TABLE ACCESS FULL             | T_REG_BUSINESS          | 17223 |   454K|       |   111   (1)| 00:00:02 |
|* 31 |          HASH JOIN                     |                         |  4961K|   728M|       | 70414   (2)| 00:14:05 |
|  32 |           TABLE ACCESS FULL            | T_REG_BUSINESS          | 17223 |   454K|       |   111   (1)| 00:00:02 |
|* 33 |           HASH JOIN                    |                         |  4961K|   600M|       | 70257   (2)| 00:14:04 |
|  34 |            TABLE ACCESS FULL           | T_SYS_CODE_AGEN         |   207 |  8073 |       |     5   (0)| 00:00:01 |
|* 35 |            HASH JOIN                   |                         |  4961K|   416M|   237M| 70206   (1)| 00:14:03 |
|* 36 |             TABLE ACCESS FULL          | T_WSVEG_CLINCH          |  4371K|   187M|       | 23470   (2)| 00:04:42 |
|* 37 |             TABLE ACCESS FULL          | T_WSVEG_CLINCHD         |  4961K|   203M|       | 21891   (2)| 00:04:23 |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("TRANSANTION_DATE">='2013-01-04' AND "TRANSANTION_DATE"<='2013-02-04')
   8 - access("CH"."BUYBUSIID"="BUY"."ID")
  10 - access("CH"."BUSIID"="SEL"."ID")
  12 - access("CH"."AGENID"="AGEN"."ID")
  14 - access("CHD"."CLINCHNO"="CH"."CLINCHNO")
  15 - filter("CHD"."PRICE"<>0)
  16 - filter("CH"."CHKTIME">TO_DATE('2011-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "CH"."BUSIID"<>"CH"."BUYBUSIID")
  17 - filter("FMD"."PRICE"<>0)
  21 - access("FM"."AGENID"="AGEN"."ID")
  23 - filter("FM"."BUYBUSIID" IS NOT NULL AND "FM"."CHKTIME">TO_DATE('2011-10-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
  25 - access("FM"."BUSIID"="SEL"."ID")
  27 - access("FM"."BUYBUSIID"="BUY"."ID")
  28 - access("FMD"."CLINCHNO"="FM"."CLINCHNO")
  29 - access("VEGCH"."BUYBUSIID"="BUY"."ID")
  31 - access("VEGCH"."BUSIID"="SEL"."ID")
  33 - access("VEGCH"."AGENID"="AGEN"."ID")
  35 - access("VEGCHD"."CLINCHNO"="VEGCH"."CLINCHNO")
  36 - filter("VEGCH"."CHKTIME">TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
  37 - filter("VEGCHD"."PRICE"<>0)

sunnyihui

亲爱的，怎么就一个index没有呢

zyo_320

关键是不走索引，我在测试环境有做测试，对谓词后面的相关条件创建了索引，但是没能得到想要的结果。

bjup

chktime >字段创建索引了吗

bjup

那个chktime上建索引意义也不大，我把to_char(to_date(vegch. ClinchDate, 'YYYYMMDD'), 'YYYY-MM-DD') as TRANSANTION_DATE,这里看成是对chktime做to_char了。
vegchd.clinchNo = vegch.clinchNo 这2列上有索引吗

Maclean Liu(刘相兵

@?/rdbms/admin/sqltrpt 对这个SQL_ID有啥结果？  :)

zyo_320

bjup 发表于 2013-3-21 18:03
那个chktime上建索引意义也不大，我把to_char(to_date(vegch. ClinchDate, 'YYYYMMDD'), 'YYYY-MM-DD') as  ...

有创建索引！

zyo_320

Maclean Liu(刘相兵 发表于 2013-3-21 22:51
@?/rdbms/admin/sqltrpt 对这个SQL_ID有啥结果？  :)

谢谢刘大！

Maclean Liu(刘相兵

sql tuning advisor给了2个索引建议：

  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DB_NFS.IDX$$_78040001 on DB_NFS.T_WS_CLINCHFM('BUYBUSIID');


  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index DB_NFS.IDX$$_78040002 on DB_NFS.T_WS_CLINCHFMD('CLINCHNO');


考虑在测试环境 或者 使用invisible index测试 创建这2个索引后的性能

zyo_320

Maclean Liu(刘相兵 发表于 2013-3-22 14:21
sql tuning advisor给了2个索引建议：

  Recommendation (estimated benefit: 100%)

谢谢，刘大！