这个sql怎么优化!

myfriend2010 · 发表于 2008-5-6 08:53

==测试下!

原帖由 wangzhonnew 于 2008-5-5 23:28 发表
q: Q3的USEE8_TIME+CUST_ID换成CUST_ID+USEE8_TIME会怎么样？
a: it will be completely different if you change the order... i suppose it will pick the index....

actually it may worth to also create a index CUST_ID+USEE8_TIME, i'm not sure which will be better in this situation, maybe running a db2advis is a good idea

myfriend2010 · 发表于 2008-5-6 09:38

月表的DDL

CREATE TABLE CCP.CUST_BILLSUM_yyyymm
(CUST_ID       DECIMAL(16, 0),
  STAT_MONTH    VARCHAR(6),
  INSTORAGE_DAY TIMESTAMP,
  CITY_CODE       INTEGER,
  AREA_CODE       DECIMAL(6, 0),
  INNET_TIME    TIMESTAMP,
  STRA_FLAG       VARCHAR(2),
  TELE_EMP_FLAG VARCHAR(2),
  CUST_NAME       VARCHAR(500),
  ACC_NBR       VARCHAR(24),
  CONTACT_NBR    VARCHAR(50),
  OFFER_NAME    VARCHAR(500),
  EFF_TIME       VARCHAR(100),
  BRAND_NAME    VARCHAR(100),
  PHS_FLAG       INTEGER,
  TEL_FLAG       INTEGER,
  XKT_FLAG       INTEGER,
  LAN_FLAG       INTEGER,
  ADSL_FLAG       INTEGER,
  VDSL_FLAG       INTEGER,
  IPTV_FLAG       INTEGER,
  OTH_FLAG       INTEGER,
  PRE_FEE       DECIMAL(16, 2),
  POST_FEE       DECIMAL(16, 2),
  TOTAL_COUNT    DECIMAL(16, 0),
  CHARGE_LEN    DECIMAL(16, 0),
  PRE_RENT       DECIMAL(16, 2),
  POST_RENT       DECIMAL(16, 2),
  PRE_LOC       DECIMAL(16, 2),
  POST_LOC       DECIMAL(16, 2),
  COUNT_LOC       DECIMAL(16, 0),
  CHARLEN_LOC    DECIMAL(16, 0),
  PRE_NET       DECIMAL(16, 2),
  POST_NET       DECIMAL(16, 2),
  COUNT_NET       DECIMAL(16, 0),
  CHARLEN_NET    DECIMAL(16, 0),
  PRE_TRATOLL    DECIMAL(16, 2),
  POST_TRATOLL    DECIMAL(16, 2),
  COUNT_TRATOLL DECIMAL(16, 0),
  CHARLEN_TRATOLL  DECIMAL(16, 0),
  PRE_17909       DECIMAL(16, 2),
  POST_17909    DECIMAL(16, 2),
  COUNT_17909    DECIMAL(16, 0),
  CHARLEN_17909 DECIMAL(16, 0),
  PRE_11808       DECIMAL(16, 2),
  POST_11808    DECIMAL(16, 2),
  COUNT_11808    DECIMAL(16, 0),
  CHARLEN_11808 DECIMAL(16, 0),
  PRE_INTERGAT    DECIMAL(16, 2),
  POST_INTERGAT DECIMAL(16, 2),
  COUNT_INTERGAT DECIMAL(16, 0),
  CHARLEN_INTERGAT DECIMAL(16, 0),
  PRE_NARBAND    DECIMAL(16, 2),
  POST_NARBAND    DECIMAL(16, 2),
  COUNT_NARBAND DECIMAL(16, 0),
  CHARLEN_NARBAND  DECIMAL(16, 0),
  PRE_BROBAND    DECIMAL(16, 2),
  POST_BROBAND    DECIMAL(16, 2),
  COUNT_BROBAND DECIMAL(16, 0),
  CHARLEN_BROBAND  DECIMAL(16, 0),
  PRE_VIEW       DECIMAL(16, 2),
  POST_VIEW       DECIMAL(16, 2),
  PRE_CALLSHOW    DECIMAL(16, 2),
  POST_CALLSHOW DECIMAL(16, 2),
  PRE_SMS       DECIMAL(16, 2),
  POST_SMS       DECIMAL(16, 2),
  COUNT_SMS       DECIMAL(16, 0),
  PRE_SPSMS       DECIMAL(16, 2),
  POST_SPSMS    DECIMAL(16, 2),
  COUNT_SPSMS    DECIMAL(16, 0),
  PREFUN_CRING    DECIMAL(16, 2),
  POSTFUN_CRING DECIMAL(16, 2),
  PREINF_CRING    DECIMAL(16, 2),
  POSTINF_CRING DECIMAL(16, 2),
  COUNT_CRING    DECIMAL(16, 0),
  PREOTH_CRING    DECIMAL(16, 2),
  POSTOTH_CRING DECIMAL(16, 2),
  PRE_COMMAJ    DECIMAL(16, 2),
  POST_COMMAJ    DECIMAL(16, 2),
  PRE_SAFNET    DECIMAL(16, 2),
  POST_SAFNET    DECIMAL(16, 2),
  PRE_OTHADD    DECIMAL(16, 2),
  POST_OTHADD    DECIMAL(16, 2),
  PRE_ANYCALL    DECIMAL(16, 2),
  POST_ANYCALL    DECIMAL(16, 2),
  PRE_OTH       DECIMAL(16, 2),
  POST_OTH       DECIMAL(16, 2),
  FREE_FEE       DECIMAL(16, 2),
  NOPAY_FEE       DECIMAL(16, 2),
  USEE8_TIME    TIMESTAMP
)
  DATA CAPTURE NONE
IN CCP
  PARTITIONING KEY
(CUST_ID
) USING HASHING;

ALTER TABLE CCP.CUST_BILLSUM
  LOCKSIZE ROW
  APPEND OFF
  NOT VOLATILE
  LOG INDEX BUILD NULL;

myfriend2010 · 发表于 2008-5-6 10:31

还是不理想,仍就走了全表扫描!

index脚本如下:
CREATE UNIQUE INDEX CCP.IDX_CBSUM_CU_0802
  ON "CCP"."CUST_BILLSUM_200802"
("USEE8_TIME" ASC,
  "CUST_ID" ASC
);
commit;

RUNSTATS ON TABLE CCP.CUST_BILLSUM_200802
  FOR INDEX CCP.IDX_CBSUM_CU_0802
  SHRLEVEL REFERENCE;

commit;

CREATE UNIQUE INDEX CCP.IDX_CBSUM_C_0801
  ON "CCP"."CUST_BILLSUM_200801"
("CUST_ID" ASC
);
commit;

RUNSTATS ON TABLE CCP.CUST_BILLSUM_200801
  FOR INDEX CCP.IDX_CBSUM_C_0801
  SHRLEVEL REFERENCE;

commit;

CREATE UNIQUE INDEX CCP.IDX_CBSUM_C_0712
  ON "CCP"."CUST_BILLSUM_200712"
("CUST_ID" ASC
);
commit;

RUNSTATS ON TABLE CCP.CUST_BILLSUM_200712
  FOR INDEX CCP.IDX_CBSUM_C_0712
  SHRLEVEL REFERENCE;

commit;

myfriend2010 · 发表于 2008-5-6 10:33

执行结果如下:

wangzhonnew · 发表于 2008-5-6 11:26

what suggestion given by db2advis?

myfriend2010 · 发表于 2008-5-6 11:40

C:Program FilesIBMSQLLIBBIN>db2advis -d dw -i c: .sql

将用户标识用作缺省模式名称。使用 -n 选项来指定模式
在时间戳记 2008-05-06-11.27.24.140000 时开始执行
从输入文件中找到 [1] SQL 语句
正在建议索引...
  0  当前解决方案中的索引数
[20957956.0000] timeron（没有建议）
[20957956.0000] timeron（对于当前解决方案）
[0.00%] 提高

--
--
-- 建议的索引列表
-- ===========================
--  未对此工作负载建议任何索引。

--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================

--
--
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "CCP    "."IDX_CBSUM_C_0712";
-- DROP INDEX "CCP    "."I_C_BILLSUM_200803";
-- DROP INDEX "CCP    "."CUST_CCP709_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CCP712_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CCP801_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CCP802_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CCP803_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CHURN_USE_S";
-- DROP INDEX "CCP    "."CUST_CHURN_REGTY";
-- DROP INDEX "CCP    "."CUST_CHURN10_REGTY";
-- DROP INDEX "CCP    "."CUST_CHURN10_USE_S";
-- DROP INDEX "CCP    "."CUST_CHURN12_REGTY";
-- DROP INDEX "CCP    "."CUST_CHURN12_USE_S";
-- DROP INDEX "CCP    "."CUST_CHUR801_REGTY";
-- DROP INDEX "CCP    "."CUST_CHUR801_USE_S";
-- DROP INDEX "CCP    "."CUST_CHUR803_REGTY";
-- DROP INDEX "CCP    "."CUST_CHUR803_USE_S";
-- DROP INDEX "CCP    "."CUST_F0801_I1";
-- DROP INDEX "DB2ADMIN"."IDX_CUST_GRP_REG";
-- DROP INDEX "CCP    "."CUST_GROUP_TAB_MOD";
-- DROP INDEX "CCP    "."V_CUST_VALUE_T287";
-- DROP INDEX "DALI "."TEST_INDEX_I";
-- DROP INDEX "DALI "."TEST_INDEX_ID";
-- DROP INDEX "DB2ADMIN"."P_OFR_PRD_ATOM";
-- DROP INDEX "DB2ADMIN"."OFR_PRD_INST_MAIN";
-- DROP INDEX "QUEST "."TS_KEYS";
-- DROP INDEX "TEL    "."IND_CUST_GCONT";
-- DROP INDEX "TEL    "."IDX_FOII_REG_C";
-- DROP INDEX "TEL    "."IDX_OPIM_REGION_C";
-- DROP INDEX "TEL    "."IDX_PARC_REG_C";
-- DROP INDEX "TEL    "."IDX_TJ_BTID";
-- DROP INDEX "CCP    "."IDX_CBSUM_C_0801";
-- DROP INDEX "CCP    "."IDX_CBSUM_CU_0802";
-- DROP INDEX "CCP    "."CUST_CCP710_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CCP711_IDMCLV";
-- DROP INDEX "CCP    "."CUST_CHURN09_REGTY";
-- DROP INDEX "CCP    "."CUST_CHURN09_USE_S";
-- DROP INDEX "CCP    "."CUST_CHURN11_REGTY";
-- DROP INDEX "CCP    "."CUST_CHURN11_USE_S";
-- DROP INDEX "CCP    "."CUST_CHUR802_REGTY";
-- DROP INDEX "CCP    "."CUST_CHUR802_USE_S";
-- DROP INDEX "CCP    "."V_CUST_VALUE_T288";
-- DROP INDEX "CCP    "."V_CUST_VALUE_T289";
-- DROP INDEX "CCP    "."V_CUST_VALUE_T290";
-- DROP INDEX "CCP    "."IBM01";
-- DROP INDEX "CCP    "."IBM24";
-- DROP INDEX "DB2ADMIN"."PK_BRAND";
-- DROP INDEX "DB2ADMIN"."SQL070918141325480";
-- DROP INDEX "DB2ADMIN"."CUST_ID_IDX2";
-- DROP INDEX "QUEST "."QCOBLST_REPO045_IX";
-- DROP INDEX "QUEST "."QCOBLST_STMT045_IX";
-- DROP INDEX "QUEST "."QCREPORT_DET_IX";
-- DROP INDEX "QUEST "."QCREPORT_REPO_IX";
-- DROP INDEX "QUEST "."IX_KEYS";
-- DROP INDEX "QUEST "."TB_KEYS";
-- DROP INDEX "TEL    "."IDX_FPII_REG_C";
-- DROP INDEX "CCP    "."FACT_PROD_1";
-- DROP INDEX "TEL    "."IND_PROD_PRPTY";
-- DROP INDEX "TEL    "."IDX_SERV_SERVID";
-- DROP INDEX "TEL    "."SERV#L";
-- ===========================
--

顾问程序已经评估了 0 个解决方案
“DB2 工作负载性能顾问程序”工具已结束。

wangzhonnew · 发表于 2008-5-6 11:47

用optlevel=0看看出来的访问计划是什么样子，看看如果用索引的开销是多少？
个人感觉SORT被低估了，你们的SORTHEAP只有1555，应该会发生很多sort overflow的

wangzhonnew · 发表于 2008-5-6 11:51

还有别忘了每次db2exfmt之前要flush package cache

myfriend2010 · 发表于 2008-5-6 11:58

优化级别为0以后,确实走索引了!

不过看样子效果还是不佳

看执行计划....

myfriend2010 · 发表于 2008-5-6 12:03

恩,我觉得也是SORTHEAP太小了,导致写入磁盘了..

不过这个SORTHEAP是DB2配置顾问程序给的建议...不曾改动过

其实上周和大菠萝讨论过是否可以同时增加SHEAPTHRES和SORTHEAP2个参数的值,但未果!呵呵

个人感觉SORT被低估了，你们的SORTHEAP只有1555，应该会发生很多sort overflow的

这个sql怎么优化!

浏览过的版块