Oracle中需要关联的表太多(大概20个表,包括有两三个View),并且有些表的数据达到百万条。目前的sql文的写法,就是一般的左关联(+)。表里建的索引也都用到了,速度也没有得到有效的改善。 具体的sql文如下:
SELECT C.NAIBUSHNCD,
C.JAN,
C.HINBAN,
C.TITLENM,
C.NM_ITEM,
D.DAIGENRENM,
C.USE_FLG
FROM (SELECT C_B.NAIBUSHNCD as NAIBUSHNCD,
C_B.JAN as JAN,
C_B.HINBAN as HINBAN,
C_B.TITLENM as TITLENM,
C_B.USE_FLG as USE_FLG,
C_B.DAIGENRECD as DAIGENRECD,
B110.NM_ITEM as NM_ITEM,
B110.MK_ITMSHUYAKUCD as MK_ITMSHUYAKUCD
FROM CCCTITLE_BASIC C_B,
(SELECT K1.JAN
FROM CCCTITLE_BASIC K1
WHERE K1.JAN > LPAD(' ', 13, ' ')
AND K1.USE_FLG IN (0, 1)
GROUP BY K1.JAN
HAVING COUNT(1) > 1) B,
(SELECT CD_ITEM, NM_ITEM, MK_ITMSHUYAKUCD
FROM BWM110
WHERE DISP_FLG = 0) B110
WHERE C_B.JAN = B.JAN
AND C_B.SHOHINKEITAICD = B110.CD_ITEM(+)) C,
CCCTITLE_BIG_GENRE D
WHERE C.DAIGENRECD = D.DAIGENRECD(+)
AND C.MK_ITMSHUYAKUCD = D.ITMSHUYAKUCD(+)
ORDER BY C.JAN
请求达人给个高招。
SELECT C.NAIBUSHNCD,
C.JAN,
C.HINBAN,
C.TITLENM,
C.NM_ITEM,
D.DAIGENRENM,
C.USE_FLG
FROM (SELECT C_B.NAIBUSHNCD as NAIBUSHNCD,
C_B.JAN as JAN,
C_B.HINBAN as HINBAN,
C_B.TITLENM as TITLENM,
C_B.USE_FLG as USE_FLG,
C_B.DAIGENRECD as DAIGENRECD,
B110.NM_ITEM as NM_ITEM,
B110.MK_ITMSHUYAKUCD as MK_ITMSHUYAKUCD
FROM CCCTITLE_BASIC C_B,
(SELECT K1.JAN
FROM CCCTITLE_BASIC K1
WHERE K1.JAN > LPAD(' ', 13, ' ')
AND K1.USE_FLG IN (0, 1)
GROUP BY K1.JAN
HAVING COUNT(1) > 1) B,
(SELECT CD_ITEM, NM_ITEM, MK_ITMSHUYAKUCD
FROM BWM110
WHERE DISP_FLG = 0) B110
WHERE C_B.JAN = B.JAN
AND C_B.SHOHINKEITAICD = B110.CD_ITEM(+)) C,
CCCTITLE_BIG_GENRE D
WHERE C.DAIGENRECD = D.DAIGENRECD(+)
AND C.MK_ITMSHUYAKUCD = D.ITMSHUYAKUCD(+)
ORDER BY C.JAN
请求达人给个高招。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货