Oracle分区,索引,测试(1)(三)
07,
partition sales_200908 tablespace ts_sales_200908,
partition sales_200909 tablespace ts_sales_200909,
partition sales_200910 tablespace ts_sales_200910,
partition sales_200911 tablespace ts_sales_200911,
partition sales_200912 tablespace ts_sales_200912,
partition sales_201001 tablespace ts_sales_201001,
partition sales_201002 tablespace ts_sales_201002
);
--一模一样的不分区表
create table sales_data2(
sales_date date,
city_id number(10),
employee_id number(10),
sales_type nvarchar2(30),
sales_amount number(10)
);
--一模一样的不分区表2
create table sales_data3(
sales_date date,
city_id number(10),
employee_id number(10),
sales_type nvarchar2(30),
sales_amount number(10)
);
set serveroutput on
---插入数据 1千万
DECLARE
MAXRECORDS CONSTANT INT:=10000000;
sales_date int:=0 ;
sales number:=0;
I INT :=1;
city_id int:=0 ;
employee_id int:=0;
sales_date1 nvarchar2(20):='';
sales_month int:=0 ;
begin
FOR I IN 1..MAXRECORDS LOOP
CITY_ID:= ABS(MOD(DBMS_RANDOM.RANDOM,24));
employee_id:=ABS(MOD(DBMS_RANDOM.RANDOM,106))+100;
SALES_DATE:=ABS(MOD(DBMS_RANDOM.RANDOM,28));
sales_month :=ABS(MOD(DBMS_RANDOM.RANDOM,5));
--保证不为0
while city_id=0 or SALES_DATE=0 or sales_month=0 loop
CITY_ID:= ABS(MOD(DBMS_RANDOM.RANDOM,24));
sales_month :=ABS(MOD(DBMS_RANDOM.RANDOM,5));
SALES_DATE:=ABS(MOD(DBMS_RANDOM.RANDOM,28));
end loop;
sales:=ABS(MOD(DBMS_RANDOM.RANDOM,100000));
sales_date1:='2009-0'||to_char(sales_month)||'-'||to_char(sales_date);
INSERT INTO SALES_DATA3 VALUES
(to_date(sales_date1,'YYYY-MM-DD'),city_id,employee_id,'toby',sales);
end loop;
dbms_output.put_line('done!');
commit;
end;
现在 SALES_DATA3 有1千万数据,分布在1到5月之间.
三个表
SALES_DATA 分区, 有位图
SALES_DATA1 分区, 无位图
SALES_DATA2 无分区
一共跑3次 减少偶然性 取最小时间
先做 普通的插入
Sql代码 --用时 167.218,137.045,135.247 最小135.247 秒 insert into SALES_DATA1 select * from SALES_DATA3 where sales_date