Oracle分区,索引,测试(1)
Oracle分区,索引,测试(1)
-----本次只做插入.看看怎么插入速度快
准备工作
Sql代码
--数据文件
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),
city nvarchar2(30),
primary key(city_id)
);
create table employee(
EMPLOYEE_ID number(10),
FIRST_NAME nvarchar2(30),
LAST_NAME nvarchar2(30),
MANAGER_ID number(10),
primary key(EMPLOYEE_ID)
);
--- insert city
--id 从1到24
INSERT INTO CITY
SELECT ROWNUM,CITY FROM HR.LOCATIONS;
-- insert employee
--id 从100到206
insert into employee
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID FROM HR.EMPLOYEES;
---分区表
create table sales_data(
sales_date date,
city_id number(10),
employee_id number(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_id
local ;
---分区表 但不建位图
create table sales_data1(
sales_date date,
city_id number(10),
employee_id number(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_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<to_date ('2009-02-1' ,'yyyy-mm-dd');
再用 nologging 试试
Sql代码
--用时 203.28,189.064,167.993 最小167.993 秒
alter table SALES_DATA1 nologging;
insert into SALES_DATA1
select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd');
alter table SALES_DATA1 logging;
时间竟然比 logging 还多. .. 理论上应该是短的. 反正至少看起来 nolog 不明显
再试试 append
Sql代码
--用时 81.867 , 89.569,84.574 最小 81.867 秒
insert /*+append*/ into SALES_DATA1
select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd');
再试试别的 append + nologging
Sql代码
--用时 68.559,84.127,84.907 最小68.559 秒
insert /*+append*/ into SALES_DATA1 nologging
select * from SALES_DATA3 where sales_date<to_date ('2009-02-1' ,'yyyy-mm-dd') ;
似乎 append + nologging 中 nologging 才有点作用.
电脑垃圾 并行就不测了 粘下语句
Sql代码
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 影响很大 时间可以相差几倍
Sql代码
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;
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。