?-----本次只做插入.看看怎么插入速度快

?

?

准备工作

--数据文件
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