Oracle 分区,索引,测试 (1)
--数据文件alter system set db_create_file_dest='d:\toby\oracle\data';--表空间create tablespace ts_sales_200901 datafile size 5M autoextend on ;create tablespace ts_sales_200902 datafile size 5M autoextend on ;create tablespace ts_sales_200903 datafile size 5M autoextend on ;create tablespace ts_sales_200904 datafile size 5M autoextend on ;create tablespace ts_sales_200905 datafile size 5M autoextend on ;create tablespace ts_sales_200906 datafile size 5M autoextend on ;create tablespace ts_sales_200907 datafile size 5M autoextend on ;create tablespace ts_sales_200908 datafile size 5M autoextend on ;create tablespace ts_sales_200909 datafile size 5M autoextend on ;create tablespace ts_sales_200910 datafile size 5M autoextend on ;create tablespace ts_sales_200911 datafile size 5M autoextend on ;create tablespace ts_sales_200912 datafile size 5M autoextend on ;create tablespace ts_sales_201001 datafile size 5M autoextend on ;create tablespace ts_sales_201002 datafile size 5M autoextend on ;create table city(city_id number(10),citynvarchar2(30),primary key(city_id));create table employee(EMPLOYEE_ID number(10),FIRST_NAMEnvarchar2(30),LAST_NAMEnvarchar2(30),MANAGER_ID number(10),primary key(EMPLOYEE_ID));--- insert city --id 从1到24INSERT INTO CITYSELECT ROWNUM,CITY FROM HR.LOCATIONS;-- insert employee--id 从100到206insert into employeeselect EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID FROM HR.EMPLOYEES;---分区表create table sales_data(sales_date date,city_idnumber(10),employee_idnumber(10),sales_type nvarchar2(30), sales_amount number(10))partition by range (sales_date)(partition sales_200901 values less than (to_date('2009-02-01','YYYY-MM-DD')) tablespace ts_sales_200901,partition sales_200902 values less than (to_date('2009-03-01','YYYY-MM-DD')) tablespace ts_sales_200902,partition sales_200903 values less than (to_date('2009-04-01','YYYY-MM-DD')) tablespace ts_sales_200903,partition sales_200904 values less than (to_date('2009-05-01','YYYY-MM-DD')) tablespace ts_sales_200904,partition sales_200905 values less than (to_date('2009-06-01','YYYY-MM-DD')) tablespace ts_sales_200905,partition sales_200906 values less than (to_date('2009-07-01','YYYY-MM-DD')) tablespace ts_sales_200906,partition sales_200907 values less than (to_date('2009-08-01','YYYY-MM-DD')) tablespace ts_sales_200907,partition sales_200908 values less than (to_date('2009-09-01','YYYY-MM-DD')) tablespace ts_sales_200908,partition sales_200909 values less than (to_date('2009-10-01','YYYY-MM-DD')) tablespace ts_sales_200909,partition sales_200910 values less than (to_date('2009-11-01','YYYY-MM-DD')) tablespace ts_sales_200910,partition sales_200911 values less than (to_date('2009-12-01','YYYY-MM-DD')) tablespace ts_sales_200911,partition sales_200912 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace ts_sales_200912,partition sales_201001 values less than (to_date('2010-02-01','YYYY-MM-DD')) tablespace ts_sales_201001,partition sales_201002 values less than (to_date('2010-03-01','YYYY-MM-DD')) tablespace ts_sales_201002);--分区索引放在对应表空间create index index_sales_data_partition on sales_data (sales_date) local (partition sales_200901 tablespace ts_sales_200901,partition sales_200902 tablespace ts_sales_200902,partition sales_200903 tablespace ts_sales_200903,partition sales_200904 tablespace ts_sales_200904,partition sales_200905 tablespace ts_sales_200905,partition sales_200906 tablespace ts_sales_200906,partition sales_200907 tablespace ts_sales_200907,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 bitmap index index_sales_data_sales_type on sales_data (sales_type) local ;--位图连接create bitmap index index_sales_data_city on sales_data (city.city_id) from sales_data,city where sales_data.city_id=city.city_idlocal ;---分区表 但不建位图create table sales_data1(sales_date date,city_idnumber(10),employee_idnumber(10),sales_type nvarchar2(30), sales_amount number(10))partition by range (sales_date)(partition sales_200901 values less than (to_date('2009-02-01','YYYY-MM-DD')) tablespace ts_sales_200901,partition sales_200902 values less than (to_date('2009-03-01','YYYY-MM-DD')) tablespace ts_sales_200902,partition sales_200903 values less than (to_date('2009-04-01','YYYY-MM-DD')) tablespace ts_sales_200903,partition sales_200904 values less than (to_date('2009-05-01','YYYY-MM-DD')) tablespace ts_sales_200904,partition sales_200905 values less than (to_date('2009-06-01','YYYY-MM-DD')) tablespace ts_sales_200905,partition sales_200906 values less than (to_date('2009-07-01','YYYY-MM-DD')) tablespace ts_sales_200906,partition sales_200907 values less than (to_date('2009-08-01','YYYY-MM-DD')) tablespace ts_sales_200907,partition sales_200908 values less than (to_date('2009-09-01','YYYY-MM-DD')) tablespace ts_sales_200908,partition sales_200909 values less than (to_date('2009-10-01','YYYY-MM-DD')) tablespace ts_sales_200909,partition sales_200910 values less than (to_date('2009-11-01','YYYY-MM-DD')) tablespace ts_sales_200910,partition sales_200911 values less than (to_date('2009-12-01','YYYY-MM-DD')) tablespace ts_sales_200911,partition sales_200912 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace ts_sales_200912,partition sales_201001 values less than (to_date('2010-02-01','YYYY-MM-DD')) tablespace ts_sales_201001,partition sales_201002 values less than (to_date('2010-03-01','YYYY-MM-DD')) tablespace ts_sales_201002);--分区索引放在对应表空间create index index_sales_data_partition_1 on sales_data1 (sales_date) local (partition sales_200901 tablespace ts_sales_200901,partition sales_200902 tablespace ts_sales_200902,partition sales_200903 tablespace ts_sales_200903,partition sales_200904 tablespace ts_sales_200904,partition sales_200905 tablespace ts_sales_200905,partition sales_200906 tablespace ts_sales_200906,partition sales_200907 tablespace ts_sales_200907,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_idnumber(10),employee_idnumber(10),sales_type nvarchar2(30), sales_amount number(10));--一模一样的不分区表2create table sales_data3(sales_date date,city_idnumber(10),employee_idnumber(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 LOOPCITY_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)); --保证不为0while 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次 减少偶然性 取最小时间
?
先做 普通的插入
--用时 167.218,137.045,135.247 最小135.247 秒insert into SALES_DATA1select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd');??再用?nologging 试试
?
--用时 203.28,189.064,167.993最小167.993 秒alter table SALES_DATA1 nologging;insert into SALES_DATA1select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd');alter table SALES_DATA1 logging;?时间竟然比 logging 还多. .. 理论上应该是短的. ?反正至少看起来 nolog 不明显?
?再试试?append
?
--用时 81.867 ,89.569,84.574 最小81.867 秒insert /*+append*/ into SALES_DATA1select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd');?再试试别的?append + nologging?
?
--用时 68.559,84.127,84.907 最小68.559 秒insert /*+append*/ into SALES_DATA1 nologgingselect * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd') ;?似乎?append + nologging 中?nologging 才有点作用.?
?
电脑垃圾 并行就不测了 粘下语句
alter table SALES_DATA3 parallel;alter table SALES_DATA1 parallel;alter table SALES_DATA1 nologging;insert /*+append*/ into SALES_DATA1 select /*+ parallel(t,2) */ * from SALES_DATA3 t where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd') ;同样位图 对于insert 影响很大 时间可以相差几倍?
alter index index_sales_data_partition modify partition sales_200903 unusable;alter index INDEX_SALES_DATA_CITY modify partition sales_200903 unusable;alter index INDEX_SALES_DATA_SALES_TYPE modify partition sales_200903 unusable; -----insert 语句 -----重建也用并行alter index index_sales_data_partition rebuild partition sales_200903 NOLOGGING PARALLEL;alter index INDEX_SALES_DATA_CITY rebuild partition sales_200903 NOLOGGING PARALLEL;alter index INDEX_SALES_DATA_SALES_TYPE rebuild partition sales_200903 NOLOGGING PARALLEL;
?
?
?