select a.channel,a.division,a.et,c.label_one,sum(a.cost) as cost2m,'201007' as yearmonth
from temp_kkc_I_II_mCost a, (select t.org_seq
from temp_kkc_I_II_mCost t
where t.yearmonth between 201006 and 201007
group by t.org_seq
having count(distinct t.yearmonth)=2)b,
temp_kkc_dmc_olayi_ii c
where a.org_seq=b.org_seq
and a.product_id=c.product_id
and a.yearmonth between 201006 and 201007
group by a.channel,a.division,a.et,c.label_one 从上这段代码执行只需要10多秒,但是前面加了create tabel temp_kkc_cost as,即创建表,执行时候没报错,且一直在执行,请高手解答原因。
from temp_kkc_I_II_mCost a, (select t.org_seq
from temp_kkc_I_II_mCost t
where t.yearmonth between 201006 and 201007
group by t.org_seq
having count(distinct t.yearmonth)=2)b,
temp_kkc_dmc_olayi_ii c
where a.org_seq=b.org_seq
and a.product_id=c.product_id
and a.yearmonth between 201006 and 201007
group by a.channel,a.division,a.et,c.label_one 从上这段代码执行只需要10多秒,但是前面加了create tabel temp_kkc_cost as,即创建表,执行时候没报错,且一直在执行,请高手解答原因。
先create table table_name (...)
再insert into table_name select ..... from
不可以吗?
你说这个我也试过,先create table ,再用insert into 结果是一样,insert一直执行下去。
from temp_kkc_I_II_mCost a,
temp_kkc_dmc_olayi_ii c
where a.org_seq in (select t.org_seq
from temp_kkc_I_II_mCost t
where t.yearmonth between 201006 and 201007
group by t.org_seq
having count(distinct t.yearmonth)=2)
and a.product_id=c.product_id
and a.yearmonth between 201006 and 201007
group by a.channel,a.division,a.et,c.label_one
以上代码是可以解决这个问题,我只是困惑,所以来求高手解答。
create table tname as 这样的话 是要找出所有的记录。 可能会慢点。 这要看你的数据量了。
INSERT 和CREATE动作使得原来的执行计划发生变化。解决1:使用HINR将执行计划写得和做查询的时候一样?
解决2:由于你返回出来的数据量很少,是否考虑过用游标for循环,一条一条处理插入到结果表中