请看下面SQL语句(要运行20秒),如何改进,提高效率.
SELECT DISTINCT
kotan.siten_cd, kotan.siten_nm,
kotan.tanto_cd, kotan.tanto_nm,
DECODE(tcm_tb1.tcm1, 0, NULL, tcm_tb1.tcm1) tcm1,
DECODE(tcm_tball1.tcmall1, 0, NULL, tcm_tball1.tcmall1) tcmall1,
DECODE(tcm_tb2.tcm2, 0, NULL, tcm_tb2.tcm2) tcm2,
DECODE(tcm_tball2.tcmall2, 0, NULL, tcm_tball2.tcmall2) tcmall2,
DECODE(tcm_tb3.tcm3, 0, NULL, tcm_tb3.tcm3) tcm3,
DECODE(tcm_tball3.tcmall3, 0, NULL, tcm_tball3.tcmall3) tcmall3,
DECODE(tcm_tb4.tcm4, 0, NULL, tcm_tb4.tcm4) tcm4
FROM
(SELECT DISTINCT kotan.siten_cd, kotan.tanto_cd, siten_nm, tanto_nm
FROM th_m_kotan kotan, th_m_siten siten, th_m_tanto tanto
WHERE kotan.siten_cd = siten.siten_cd AND kotan.tanto_cd = tanto.tanto_cd
) kotan,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm1, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%A%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb1,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall1, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%A%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball1,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm2, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%B%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb2,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall2, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%B%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball2,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm3, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%C%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb3,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall3, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%C%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball3,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm4, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%D%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb4,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall4, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%D%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball4
WHERE
kotan.siten_cd = tcm_tb1.siten_cd(+) AND kotan.tanto_cd = tcm_tb1.tanto_cd(+)
AND kotan.siten_cd = tcm_tball1.siten_cd(+) AND kotan.tanto_cd = tcm_tball1.tanto_cd(+)
AND kotan.siten_cd = tcm_tb2.siten_cd(+) AND kotan.tanto_cd = tcm_tb2.tanto_cd(+)
AND kotan.siten_cd = tcm_tball2.siten_cd(+) AND kotan.tanto_cd = tcm_tball2.tanto_cd(+)
AND kotan.siten_cd = tcm_tb3.siten_cd(+) AND kotan.tanto_cd = tcm_tb3.tanto_cd(+)
AND kotan.siten_cd = tcm_tball3.siten_cd(+) AND kotan.tanto_cd = tcm_tball3.tanto_cd(+)
AND kotan.siten_cd = tcm_tb4.siten_cd(+) AND kotan.tanto_cd = tcm_tb4.tanto_cd(+)
AND kotan.siten_cd = tcm_tball4.siten_cd(+) AND kotan.tanto_cd = tcm_tball4.tanto_cd(+)
SELECT DISTINCT
kotan.siten_cd, kotan.siten_nm,
kotan.tanto_cd, kotan.tanto_nm,
DECODE(tcm_tb1.tcm1, 0, NULL, tcm_tb1.tcm1) tcm1,
DECODE(tcm_tball1.tcmall1, 0, NULL, tcm_tball1.tcmall1) tcmall1,
DECODE(tcm_tb2.tcm2, 0, NULL, tcm_tb2.tcm2) tcm2,
DECODE(tcm_tball2.tcmall2, 0, NULL, tcm_tball2.tcmall2) tcmall2,
DECODE(tcm_tb3.tcm3, 0, NULL, tcm_tb3.tcm3) tcm3,
DECODE(tcm_tball3.tcmall3, 0, NULL, tcm_tball3.tcmall3) tcmall3,
DECODE(tcm_tb4.tcm4, 0, NULL, tcm_tb4.tcm4) tcm4
FROM
(SELECT DISTINCT kotan.siten_cd, kotan.tanto_cd, siten_nm, tanto_nm
FROM th_m_kotan kotan, th_m_siten siten, th_m_tanto tanto
WHERE kotan.siten_cd = siten.siten_cd AND kotan.tanto_cd = tanto.tanto_cd
) kotan,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm1, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%A%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb1,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall1, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%A%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball1,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm2, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%B%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb2,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall2, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%B%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball2,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm3, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%C%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb3,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall3, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%C%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball3,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcm4, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.maker_cd = '11' AND b.kisyu_cd LIKE '%D%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tb4,
(SELECT a.siten_cd, COUNT(b.maker_cd) tcmall4, a.tanto_cd
FROM th_m_kotan a, th_m_syary b
WHERE a.kokya_cd = b.kokya_cd AND a.jigyo_cd = b.jigyo_cd AND a.ren_bn = b.ren_bn
AND b.kisyu_cd LIKE '%D%'
GROUP BY a.siten_cd , a.tanto_cd) tcm_tball4
WHERE
kotan.siten_cd = tcm_tb1.siten_cd(+) AND kotan.tanto_cd = tcm_tb1.tanto_cd(+)
AND kotan.siten_cd = tcm_tball1.siten_cd(+) AND kotan.tanto_cd = tcm_tball1.tanto_cd(+)
AND kotan.siten_cd = tcm_tb2.siten_cd(+) AND kotan.tanto_cd = tcm_tb2.tanto_cd(+)
AND kotan.siten_cd = tcm_tball2.siten_cd(+) AND kotan.tanto_cd = tcm_tball2.tanto_cd(+)
AND kotan.siten_cd = tcm_tb3.siten_cd(+) AND kotan.tanto_cd = tcm_tb3.tanto_cd(+)
AND kotan.siten_cd = tcm_tball3.siten_cd(+) AND kotan.tanto_cd = tcm_tball3.tanto_cd(+)
AND kotan.siten_cd = tcm_tb4.siten_cd(+) AND kotan.tanto_cd = tcm_tb4.tanto_cd(+)
AND kotan.siten_cd = tcm_tball4.siten_cd(+) AND kotan.tanto_cd = tcm_tball4.tanto_cd(+)
看看關于優化的資料把
http://tech.sina.com.cn/c/2002-02-25/11339.html
http://www.zdnet.com.cn/developer/code/story/0,2000081534,39128560-1,00.htm
1.如果可能可以考虑用存储过程把数据放入临时表解决2.合理建立索引3.大表用分区表然后建立局部索引