我在测试向oracle的一个临时表插数据,数据量很少,只有20多条,查询数据部份很快,只用了0.1秒左右,但如果把查询出来的数据插到临时表,则要用20秒左右,临时表是先创建好的,没有索引
Create Global temporary table tmp_query3808010_1 (
retailid varchar2(32) ,
retype varchar2(40),
d_name varchar2(40),
x_name varchar2(40),
sums number
) on Commit preserve rows ; insert into tmp_query3808010_2(retailid,retype,d_name,x_name,sums)
select a.retailid,trim(a.retype)as retype,d.d_name,b.x_name,Sum(a.sums) as sums
from pos_skfs a,
(select retailid,Max(x_name)as x_name,Max(depotid)as depotid,Max(sure_date)as sure_date from d_retail Group by retailid) b,
j_depot d ,sys_powerdepot e
where a.retailid=b.retailid
And b.depotid=d.depotid
And b.sure_date Between 20090601 and 20090612 and b.depotid = e.depotid and e.acc=1 and e.selection=1 and e.user_no=2665
group by a.retailid,trim(a.retype),d.d_name,b.x_name ;select 部份很快,但如果insert 到 表,则要花20多秒.我测试创建临时表同时插数据,则差不多只用了10秒
Create Global temporary table tmp_query3808010_3 on commit preserve rows as (
select a.retailid,trim(a.retype)as retype,d.d_name,b.x_name,Sum(a.sums) as sums
from pos_skfs a,
(select retailid,Max(x_name)as x_name,Max(depotid)as depotid,Max(sure_date)as sure_date from d_retail Group by retailid) b,
j_depot d ,sys_powerdepot e
where a.retailid=b.retailid
And b.depotid=d.depotid
And b.sure_date Between 20090601 and 20090612 and b.depotid = e.depotid and e.acc=1 and e.selection=1 and e.user_no=2665
group by a.retailid,trim(a.retype),d.d_name,b.x_name );
不明白为什么插数据这么慢,服务器是新买的,性能还行的
Create Global temporary table tmp_query3808010_1 (
retailid varchar2(32) ,
retype varchar2(40),
d_name varchar2(40),
x_name varchar2(40),
sums number
) on Commit preserve rows ; insert into tmp_query3808010_2(retailid,retype,d_name,x_name,sums)
select a.retailid,trim(a.retype)as retype,d.d_name,b.x_name,Sum(a.sums) as sums
from pos_skfs a,
(select retailid,Max(x_name)as x_name,Max(depotid)as depotid,Max(sure_date)as sure_date from d_retail Group by retailid) b,
j_depot d ,sys_powerdepot e
where a.retailid=b.retailid
And b.depotid=d.depotid
And b.sure_date Between 20090601 and 20090612 and b.depotid = e.depotid and e.acc=1 and e.selection=1 and e.user_no=2665
group by a.retailid,trim(a.retype),d.d_name,b.x_name ;select 部份很快,但如果insert 到 表,则要花20多秒.我测试创建临时表同时插数据,则差不多只用了10秒
Create Global temporary table tmp_query3808010_3 on commit preserve rows as (
select a.retailid,trim(a.retype)as retype,d.d_name,b.x_name,Sum(a.sums) as sums
from pos_skfs a,
(select retailid,Max(x_name)as x_name,Max(depotid)as depotid,Max(sure_date)as sure_date from d_retail Group by retailid) b,
j_depot d ,sys_powerdepot e
where a.retailid=b.retailid
And b.depotid=d.depotid
And b.sure_date Between 20090601 and 20090612 and b.depotid = e.depotid and e.acc=1 and e.selection=1 and e.user_no=2665
group by a.retailid,trim(a.retype),d.d_name,b.x_name );
不明白为什么插数据这么慢,服务器是新买的,性能还行的
这样新数据会存放在新的数据块中,不会使用以前的空间另外,是不是索引建的太多了