orace SQL 建立索引优化问题 sql索引优化 解决方案 » 免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货 我在EXT_LCJK_LCHJMX B 表建立分别建立XDRQ,XDSJ,LCID三个字段的索引，还分别建立EXT_LCJK_LC A表CJDW,CJRQ的索引但是查询还是很慢！求求大神帮助优化一下 select * from table(dbms_xplan.display_cursor('sql_id',null,'advanced'));把全部的执行计划贴出来 语句方面优化空间还很大1、最外层的GROUP BY CJDW, M.JGMC去掉外层查询直接SELECT M.JGMC AS CJDW,T.LCZS,T.LCWCZS,T.LCWWCZS,T.LCCSWCZS,T.LCWCSWCZS子查询中已经做完汇总了，外层再汇总就没啥必要了2、 AND TO_DATE(LPAD(B.DDRQ, 8, '0') || LPAD(B.DDSJ, 6, '0'), 'YYYYMMDDHH24MISS') = (SELECT MAX(TO_DATE(LPAD(DDRQ, 8, '0') || LPAD(DDSJ, 6, '0'), 'YYYYMMDDHH24MISS')) FROM EXT_LCJK_LCHJMX WHERE LCID = B.LCID)这个条件可以考虑使用分析函数max()keep(dense_rank last order by)的方式实现，比表自身关联要快3、通过cjdw获取JGMC这个没啥必要，保留原来的cjdwSELECT (select JGMC FROM EXT_YX_SYS_JGXX WHERE JGBM = A.CJDW) AS CJDW,改为select A.CJDW外层关联条件改为T.CJDW(+) = M.JGBM SELECT STATEMENT, GOAL = ALL_ROWS 21419 1 111 2280824746 0 0 21153 SELECT STATEMENT ALL_ROWS 1514 21419 YPGJ_USER 258 2014-11-20 16:02:57 HASH GROUP BY 21419 1 111 2280824746 1 1 21153 HASH GROUP BY 0 1514 1 (#keys=2) "CJDW"[VARCHAR2,40], "JGMC"[VARCHAR2,40], SUM("T"."LCWCSWCZS")[22], SUM("T"."LCCSWCZS")[22], SUM("T"."LCWWCZS")[22], SUM("T"."LCWCZS")[22], SUM("T"."LCZS")[22] SEL$66276EDA YPGJ_USER 258 2014-11-20 16:02:57 HASH JOIN OUTER 21418 1 111 "T"."CJDW"(+)="JGMC" 2272257882 2 2 21153 HASH JOIN OUTER 1 1514 1 (#keys=1) "JGMC"[VARCHAR2,40], "T"."CJDW"[VARCHAR2,40], "T"."LCWCSWCZS"[NUMBER,22], "T"."LCZS"[NUMBER,22], "T"."LCWCZS"[NUMBER,22], "T"."LCWWCZS"[NUMBER,22], "T"."LCCSWCZS"[NUMBER,22] YPGJ_USER 258 2014-11-20 16:02:57 TABLE ACCESS BY INDEX ROWID YPGJ_USER EXT_YX_SYS_JGXX 2 1 24 15023 3 3 2 EXT_YX_SYS_JGXX@SEL$5 7 TABLE TABLE ACCESS ANALYZED BY INDEX ROWID 2 1514 1 "JGMC"[VARCHAR2,40] SEL$66276EDA YPGJ_USER 1 2014-11-20 16:02:57 INDEX RANGE SCAN YPGJ_USER PK_EXT_YX_SYS_JGXX 1 1 "JGBM" LIKE '17%' 7521 4 "JGBM" LIKE '17%' 4 1 EXT_YX_SYS_JGXX@SEL$5 INDEX (UNIQUE) INDEX ANALYZED RANGE SCAN 3 1514 1 "EXT_YX_SYS_JGXX".ROWID[ROWID,10] SEL$66276EDA 1 YPGJ_USER 1 2014-11-20 16:02:57 VIEW YPGJ_USER 21416 148 12876 2267944477 3 5 21151 T@SEL$1 1 VIEW 2 1514 2 "CJDW"[VARCHAR2,40], "T"."LCZS"[NUMBER,22], "T"."LCWCZS"[NUMBER,22], "T"."LCWWCZS"[NUMBER,22], "T"."LCCSWCZS"[NUMBER,22], "T"."LCWCSWCZS"[NUMBER,22] SEL$833EAD59 YPGJ_USER 257 2014-11-20 16:02:57 HASH GROUP BY 21416 148 21164 2267944477 4 6 21151 HASH GROUP BY 5 1514 1 (#keys=1) "A"."CJDW"[VARCHAR2,40], COUNT(CASE WHEN ("A"."LCZT"=2 AND TO_DATE(LPAD(TO_CHAR("B"."XDRQ"),8,'0')||LPAD(TO_CHAR("B"."XDSJ"),6,'0'),'YYYYMMDDHH24MISS')<SYSDATE@!) THEN 1 ELSE NULL END )[22], COUNT(CASE WHEN ("A"."LCZT"=2 AND TO_DATE(LPAD(TO_CHAR("B"."XDRQ"),8,'0')||LPAD(TO_CHAR("B"."XDSJ"),6,'0'),'YYYYMMDDHH24MISS')>SYSDATE@!) THEN 1 ELSE NULL END )[22], COUNT(CASE "A"."LCZT" WHEN 0 THEN 1 ELSE NULL END )[22], COUNT(CASE "A"."LCZT" WHEN 2 THEN 1 ELSE NULL END )[22], COUNT(*)[22] SEL$833EAD59 YPGJ_USER 257 2014-11-20 16:02:57 HASH JOIN 21415 148 21164 "VW_COL_1"=TO_DATE(LPAD(TO_CHAR("B"."DDRQ"),8,'0')||LPAD(TO_CHAR("B"."DDSJ"),6,'0'),'YYYYMMDDHH24MISS') AND "ITEM_0"="B"."LCID" 2259329539 5 7 21151 HASH JOIN 6 1514 1 (#keys=2) "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22], "B"."XDSJ"[NUMBER,22], "B"."XDRQ"[NUMBER,22] YPGJ_USER 257 2014-11-20 16:02:57 TABLE ACCESS BY INDEX ROWID YPGJ_USER EXT_LCJK_LCHJMX 4 6 330 36686 6 8 4 B@SEL$2 4 TABLE TABLE ACCESS ANALYZED BY INDEX ROWID 7 1514 1 "B"."LCID"[VARCHAR2,40], "B"."DDRQ"[NUMBER,22], "B"."DDSJ"[NUMBER,22], "B"."XDRQ"[NUMBER,22], "B"."XDSJ"[NUMBER,22] SEL$833EAD59 YPGJ_USER 1 2014-11-20 16:02:57 NESTED LOOPS 10005 14799 1494699 231578456 7 9 9978 NESTED LOOPS 8 1514 1 (#keys=0) "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22], "B".ROWID[ROWID,10], "B"."LCID"[VARCHAR2,40] YPGJ_USER 121 2014-11-20 16:02:57 PARTITION LIST ALL 1588 2469 113574 155824986 8 10 1570 PARTITION LIST ALL 9 10 1 5 1514 1 "A"."LCID"[VARCHAR2,40], "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22] YPGJ_USER 20 2014-11-20 16:02:57 TABLE ACCESS FULL YPGJ_USER EXT_LCJK_LC 1588 2469 113574 155824986 9 "A"."CJRQ">=20141101 AND "A"."CJDW" LIKE '17%' AND "A"."CJRQ"<=20141131 11 1570 A@SEL$2 3 TABLE TABLE ACCESS ANALYZED FULL 10 10 1 5 1514 1 "A"."LCID"[VARCHAR2,40], "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22] SEL$833EAD59 YPGJ_USER 20 2014-11-20 16:02:57 INDEX RANGE SCAN YPGJ_USER LCID_INDEX 2 6 "A"."LCID"="B"."LCID" 16493 8 12 2 B@SEL$2 INDEX INDEX ANALYZED RANGE SCAN 9 1514 2 "B".ROWID[ROWID,10], "B"."LCID"[VARCHAR2,40] SEL$833EAD59 1 YPGJ_USER 1 2014-11-20 16:02:57 VIEW SYS VW_SQ_1 11406 273907 11504094 1993857100 6 13 11173 VW_SQ_1@SEL$93120962 8 VIEW VIEW 7 1514 2 "VW_COL_1"[DATE,7], "ITEM_0"[VARCHAR2,40] SEL$8F9407EC YPGJ_USER 137 2014-11-20 16:02:57 HASH GROUP BY 11406 273907 12051908 1993857100 7 14 11173 HASH GROUP BY 13 1514 1 (#keys=1) "LCID"[VARCHAR2,40], MAX(TO_DATE(LPAD(TO_CHAR("DDRQ"),8,'0')||LPAD(TO_CHAR("DDSJ"),6,'0'),'YYYYMMDDHH24MISS'))[7] SEL$8F9407EC YPGJ_USER 92783000 137 2014-11-20 16:02:57 INDEX FAST FULL SCAN YPGJ_USER LCID_DDRQ_DDSJ 2802 1642088 72251872 287319970 8 15 2768 EXT_LCJK_LCHJMX@SEL$4 INDEX INDEX ANALYZED FAST FULL SCAN 14 1514 1 "LCID"[VARCHAR2,40], "DDRQ"[NUMBER,22], "DDSJ"[NUMBER,22] SEL$8F9407EC YPGJ_USER 34 2014-11-20 16:02:57这是从plsql粘出来的全部执行计划 谢谢版主的回答，我那最外层的汇总确实没必要，已经去掉了，第三个通过cjdw获取JGMC这个没啥必要，因为我要拿创建单位（cjdw是一个编码）去EXT_YX_SYS_JGXX表中找到对应的机构名称(JGMC) EXT_YX_SYS_JGXX 中jgbm和jgmc是一一对应的吧，使用jgmc关联和使用jgbm关联效果是一样的名称在外层做外连接的时候使用M.JGMC就直接取出来了，外连接之前使用jgbm与cjdw关联就可以了 求教 oracle10g 创建资料档案库时出错 解决办法 关于接口表短时间频繁的查询,删除问题. oracle 过程参数疑问？ update from where 语句问题 请教2个sql文 请教SQL 如何写？？ 怎么插入数据？ instr('2',share1dept)>0出不来结果啊 创建数据库的问题 oracle菜鸟关于排序删重问题 oracle+mscs+ofs监听配置 請問HINT 用法：
1、最外层的GROUP BY CJDW, M.JGMC去掉
外层查询直接SELECT M.JGMC AS CJDW,T.LCZS,T.LCWCZS,T.LCWWCZS,T.LCCSWCZS,T.LCWCSWCZS
子查询中已经做完汇总了，外层再汇总就没啥必要了
2、 AND TO_DATE(LPAD(B.DDRQ, 8, '0') || LPAD(B.DDSJ, 6, '0'),
'YYYYMMDDHH24MISS') =
(SELECT MAX(TO_DATE(LPAD(DDRQ, 8, '0') || LPAD(DDSJ, 6, '0'),
'YYYYMMDDHH24MISS'))
FROM EXT_LCJK_LCHJMX
WHERE LCID = B.LCID)
这个条件可以考虑使用分析函数max()keep(dense_rank last order by)的方式实现，比表自身关联要快
3、通过cjdw获取JGMC这个没啥必要，保留原来的cjdw
SELECT (select JGMC FROM EXT_YX_SYS_JGXX WHERE JGBM = A.CJDW) AS CJDW,
改为select A.CJDW
外层关联条件改为T.CJDW(+) = M.JGBM
SELECT STATEMENT, GOAL = ALL_ROWS 21419 1 111 2280824746 0 0 21153 SELECT STATEMENT ALL_ROWS 1514 21419 YPGJ_USER 258 2014-11-20 16:02:57
HASH GROUP BY 21419 1 111 2280824746 1 1 21153 HASH GROUP BY 0 1514 1 (#keys=2) "CJDW"[VARCHAR2,40], "JGMC"[VARCHAR2,40], SUM("T"."LCWCSWCZS")[22], SUM("T"."LCCSWCZS")[22], SUM("T"."LCWWCZS")[22], SUM("T"."LCWCZS")[22], SUM("T"."LCZS")[22] SEL$66276EDA YPGJ_USER 258 2014-11-20 16:02:57
HASH JOIN OUTER 21418 1 111 "T"."CJDW"(+)="JGMC" 2272257882 2 2 21153 HASH JOIN OUTER 1 1514 1 (#keys=1) "JGMC"[VARCHAR2,40], "T"."CJDW"[VARCHAR2,40], "T"."LCWCSWCZS"[NUMBER,22], "T"."LCZS"[NUMBER,22], "T"."LCWCZS"[NUMBER,22], "T"."LCWWCZS"[NUMBER,22], "T"."LCCSWCZS"[NUMBER,22] YPGJ_USER 258 2014-11-20 16:02:57
TABLE ACCESS BY INDEX ROWID YPGJ_USER EXT_YX_SYS_JGXX 2 1 24 15023 3 3 2 EXT_YX_SYS_JGXX@SEL$5 7 TABLE TABLE ACCESS ANALYZED BY INDEX ROWID 2 1514 1 "JGMC"[VARCHAR2,40] SEL$66276EDA YPGJ_USER 1 2014-11-20 16:02:57
INDEX RANGE SCAN YPGJ_USER PK_EXT_YX_SYS_JGXX 1 1 "JGBM" LIKE '17%' 7521 4 "JGBM" LIKE '17%' 4 1 EXT_YX_SYS_JGXX@SEL$5 INDEX (UNIQUE) INDEX ANALYZED RANGE SCAN 3 1514 1 "EXT_YX_SYS_JGXX".ROWID[ROWID,10] SEL$66276EDA 1 YPGJ_USER 1 2014-11-20 16:02:57
VIEW YPGJ_USER 21416 148 12876 2267944477 3 5 21151 T@SEL$1 1 VIEW 2 1514 2 "CJDW"[VARCHAR2,40], "T"."LCZS"[NUMBER,22], "T"."LCWCZS"[NUMBER,22], "T"."LCWWCZS"[NUMBER,22], "T"."LCCSWCZS"[NUMBER,22], "T"."LCWCSWCZS"[NUMBER,22] SEL$833EAD59 YPGJ_USER 257 2014-11-20 16:02:57
HASH GROUP BY 21416 148 21164 2267944477 4 6 21151 HASH GROUP BY 5 1514 1 (#keys=1) "A"."CJDW"[VARCHAR2,40], COUNT(CASE WHEN ("A"."LCZT"=2 AND TO_DATE(LPAD(TO_CHAR("B"."XDRQ"),8,'0')||LPAD(TO_CHAR("B"."XDSJ"),6,'0'),'YYYYMMDDHH24MISS')<SYSDATE@!) THEN 1 ELSE NULL END )[22], COUNT(CASE WHEN ("A"."LCZT"=2 AND TO_DATE(LPAD(TO_CHAR("B"."XDRQ"),8,'0')||LPAD(TO_CHAR("B"."XDSJ"),6,'0'),'YYYYMMDDHH24MISS')>SYSDATE@!) THEN 1 ELSE NULL END )[22], COUNT(CASE "A"."LCZT" WHEN 0 THEN 1 ELSE NULL END )[22], COUNT(CASE "A"."LCZT" WHEN 2 THEN 1 ELSE NULL END )[22], COUNT(*)[22] SEL$833EAD59 YPGJ_USER 257 2014-11-20 16:02:57
HASH JOIN 21415 148 21164 "VW_COL_1"=TO_DATE(LPAD(TO_CHAR("B"."DDRQ"),8,'0')||LPAD(TO_CHAR("B"."DDSJ"),6,'0'),'YYYYMMDDHH24MISS') AND "ITEM_0"="B"."LCID" 2259329539 5 7 21151 HASH JOIN 6 1514 1 (#keys=2) "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22], "B"."XDSJ"[NUMBER,22], "B"."XDRQ"[NUMBER,22] YPGJ_USER 257 2014-11-20 16:02:57
TABLE ACCESS BY INDEX ROWID YPGJ_USER EXT_LCJK_LCHJMX 4 6 330 36686 6 8 4 B@SEL$2 4 TABLE TABLE ACCESS ANALYZED BY INDEX ROWID 7 1514 1 "B"."LCID"[VARCHAR2,40], "B"."DDRQ"[NUMBER,22], "B"."DDSJ"[NUMBER,22], "B"."XDRQ"[NUMBER,22], "B"."XDSJ"[NUMBER,22] SEL$833EAD59 YPGJ_USER 1 2014-11-20 16:02:57
NESTED LOOPS 10005 14799 1494699 231578456 7 9 9978 NESTED LOOPS 8 1514 1 (#keys=0) "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22], "B".ROWID[ROWID,10], "B"."LCID"[VARCHAR2,40] YPGJ_USER 121 2014-11-20 16:02:57
PARTITION LIST ALL 1588 2469 113574 155824986 8 10 1570 PARTITION LIST ALL 9 10 1 5 1514 1 "A"."LCID"[VARCHAR2,40], "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22] YPGJ_USER 20 2014-11-20 16:02:57
TABLE ACCESS FULL YPGJ_USER EXT_LCJK_LC 1588 2469 113574 155824986 9 "A"."CJRQ">=20141101 AND "A"."CJDW" LIKE '17%' AND "A"."CJRQ"<=20141131 11 1570 A@SEL$2 3 TABLE TABLE ACCESS ANALYZED FULL 10 10 1 5 1514 1 "A"."LCID"[VARCHAR2,40], "A"."CJDW"[VARCHAR2,40], "A"."LCZT"[NUMBER,22] SEL$833EAD59 YPGJ_USER 20 2014-11-20 16:02:57
INDEX RANGE SCAN YPGJ_USER LCID_INDEX 2 6 "A"."LCID"="B"."LCID" 16493 8 12 2 B@SEL$2 INDEX INDEX ANALYZED RANGE SCAN 9 1514 2 "B".ROWID[ROWID,10], "B"."LCID"[VARCHAR2,40] SEL$833EAD59 1 YPGJ_USER 1 2014-11-20 16:02:57
VIEW SYS VW_SQ_1 11406 273907 11504094 1993857100 6 13 11173 VW_SQ_1@SEL$93120962 8 VIEW VIEW 7 1514 2 "VW_COL_1"[DATE,7], "ITEM_0"[VARCHAR2,40] SEL$8F9407EC YPGJ_USER 137 2014-11-20 16:02:57
HASH GROUP BY 11406 273907 12051908 1993857100 7 14 11173 HASH GROUP BY 13 1514 1 (#keys=1) "LCID"[VARCHAR2,40], MAX(TO_DATE(LPAD(TO_CHAR("DDRQ"),8,'0')||LPAD(TO_CHAR("DDSJ"),6,'0'),'YYYYMMDDHH24MISS'))[7] SEL$8F9407EC YPGJ_USER 92783000 137 2014-11-20 16:02:57
INDEX FAST FULL SCAN YPGJ_USER LCID_DDRQ_DDSJ 2802 1642088 72251872 287319970 8 15 2768 EXT_LCJK_LCHJMX@SEL$4 INDEX INDEX ANALYZED FAST FULL SCAN 14 1514 1 "LCID"[VARCHAR2,40], "DDRQ"[NUMBER,22], "DDSJ"[NUMBER,22] SEL$8F9407EC YPGJ_USER 34 2014-11-20 16:02:57
这是从plsql粘出来的全部执行计划
谢谢版主的回答，我那最外层的汇总确实没必要，已经去掉了，第三个通过cjdw获取JGMC这个没啥必要，因为我要拿创建单位（cjdw是一个编码）去EXT_YX_SYS_JGXX表中找到对应的机构名称(JGMC)
名称在外层做外连接的时候使用M.JGMC就直接取出来了，外连接之前使用jgbm与cjdw关联就可以了