由于对oracle不是很熟悉,今天遇到一个执行sql的问题,想请教下大家:(JS_BC_BASICINFO中数据有3万,COM_ZONECODE中有5万多)
select c.CNNAME,c.zonecode,
sum(z.xzczs) as xzczs,
sum(z.lxcczrks) as lxcczrks,
sum(z.cyxxs) as cyxxs,
sum(z.hbbxrs) as hbbxrs,
sum(z.hbpxrs) as hbpxrs,
sum(z.hbhhs) as hbhhs,
sum(z.hbwfx) as hbwfx,
sum(z.yw_nx) as yw_nx,
sum( z.yw_px) as yw_px,
sum( z.yw_hhx) as yw_hhx,
sum(z.yw_wfx) as yw_wfx,
sum( z.ffabds) as ffabds,
sum(z.ffafrs) as ffafrs,
sum( z.sjabds) as sjabds,
sum( z.sjafrs) as sjafrs,
sum( z.ss_nx) as ss_nx,
sum( z.ss_px) as ss_px,
sum( z.ss_hhx) as ss_hhx,
sum( z.ss_wdx) as ss_wdx,
count(distinct substr(z.town_code,0,6)) as nxian,
count(distinct z.town_code) as ntown,
count(distinct (z.town_code||z.villagecode)) as nvillage
from (
select
substr(b.town_code, 0,2) || substr('0000000', 2) as zonecode, b.yw_wfx
as yw_wfx,b.town_code as town_code,b.village_code as villagecode,
b.xzczs as xzczs,b.lxcczrks as lxcczrks,b.cyxxs as cyxxs,b.hbbxrs as hbbxrs,b.hbpxrs as hbpxrs,b.hbhhs as hbhhs,b.hbwfx as hbwfx,
b.yw_nx as yw_nx,b.yw_px as yw_px,b.yw_hhx as yw_hhx,b.ffabds as ffabds,b.ffafrs as ffafrs,b.sjabds as sjabds,b.sjafrs as sjafrs,
b.ss_nx as ss_nx,b.ss_px as ss_px,b.ss_hhx as ss_hhx,b.ss_wdx as ss_wdx
from js_bc_basicinfo b
where town_code like '' || '%' and years = '2009' and b.flag1 = '11' and (inmonth='' or '' is null) ) z, zonecode c
where z.zonecode = c.zonecode
group by c.CNNAME,c.zonecode order by c.zonecode这是我在sql 语句中看到的执行计划:
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
NESTED LOOPS
TABLE ACCESS FULL Object owner=DBCDC Object name=JS_BC_BASICINFO
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=COM_ZONECODE
INDEX UNIQUE SCAN Object owner=DBCDC Object name=PK_COM_ZONECODE 查出33条数据,不到1秒的时间我看到了 “TABLE ACCESS FULL”,于是我想建个索引肯定会更快些,于是:
我在town_code,years,flag1上建立了一个组合索引IDX_BC_BASICINFO_04执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=JS_BC_BASICINFO
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=COM_ZONECODE
INDEX RANGE SCAN Object owner=DBCDC Object name=IDX_COM_ZONECODE
INDEX RANGE SCAN Object owner=DBCDC Object name=IDX_BC_BASICINFO_04
结果令我大跌眼镜,卡死了,决定不是机子问题,我很郁闷,不知道是什么原因啊???
希望大家帮帮我!
select c.CNNAME,c.zonecode,
sum(z.xzczs) as xzczs,
sum(z.lxcczrks) as lxcczrks,
sum(z.cyxxs) as cyxxs,
sum(z.hbbxrs) as hbbxrs,
sum(z.hbpxrs) as hbpxrs,
sum(z.hbhhs) as hbhhs,
sum(z.hbwfx) as hbwfx,
sum(z.yw_nx) as yw_nx,
sum( z.yw_px) as yw_px,
sum( z.yw_hhx) as yw_hhx,
sum(z.yw_wfx) as yw_wfx,
sum( z.ffabds) as ffabds,
sum(z.ffafrs) as ffafrs,
sum( z.sjabds) as sjabds,
sum( z.sjafrs) as sjafrs,
sum( z.ss_nx) as ss_nx,
sum( z.ss_px) as ss_px,
sum( z.ss_hhx) as ss_hhx,
sum( z.ss_wdx) as ss_wdx,
count(distinct substr(z.town_code,0,6)) as nxian,
count(distinct z.town_code) as ntown,
count(distinct (z.town_code||z.villagecode)) as nvillage
from (
select
substr(b.town_code, 0,2) || substr('0000000', 2) as zonecode, b.yw_wfx
as yw_wfx,b.town_code as town_code,b.village_code as villagecode,
b.xzczs as xzczs,b.lxcczrks as lxcczrks,b.cyxxs as cyxxs,b.hbbxrs as hbbxrs,b.hbpxrs as hbpxrs,b.hbhhs as hbhhs,b.hbwfx as hbwfx,
b.yw_nx as yw_nx,b.yw_px as yw_px,b.yw_hhx as yw_hhx,b.ffabds as ffabds,b.ffafrs as ffafrs,b.sjabds as sjabds,b.sjafrs as sjafrs,
b.ss_nx as ss_nx,b.ss_px as ss_px,b.ss_hhx as ss_hhx,b.ss_wdx as ss_wdx
from js_bc_basicinfo b
where town_code like '' || '%' and years = '2009' and b.flag1 = '11' and (inmonth='' or '' is null) ) z, zonecode c
where z.zonecode = c.zonecode
group by c.CNNAME,c.zonecode order by c.zonecode这是我在sql 语句中看到的执行计划:
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
NESTED LOOPS
TABLE ACCESS FULL Object owner=DBCDC Object name=JS_BC_BASICINFO
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=COM_ZONECODE
INDEX UNIQUE SCAN Object owner=DBCDC Object name=PK_COM_ZONECODE 查出33条数据,不到1秒的时间我看到了 “TABLE ACCESS FULL”,于是我想建个索引肯定会更快些,于是:
我在town_code,years,flag1上建立了一个组合索引IDX_BC_BASICINFO_04执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE
SORT GROUP BY
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=JS_BC_BASICINFO
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID Object owner=DBCDC Object name=COM_ZONECODE
INDEX RANGE SCAN Object owner=DBCDC Object name=IDX_COM_ZONECODE
INDEX RANGE SCAN Object owner=DBCDC Object name=IDX_BC_BASICINFO_04
结果令我大跌眼镜,卡死了,决定不是机子问题,我很郁闷,不知道是什么原因啊???
希望大家帮帮我!
from
(select count(...) as ..., sum(...) as ...
from js_bc_basicinfo b
where town_code like '' || '%' and years = '2009' and b.flag1 = '11'
and (inmonth='' or '' is null)
group by b.zonecode --js_bc_basicinfo.zonecode加索引
order by b.zonecode) z
left outer join zonecode c on z.zonecode = c.zonecode
--如果js_bc_basicinfo.zonecode不是外键,最后可以加上where c.zonecode is not null
为什么要在zonecode上加索引啊??