当前位置:数据库 > Oracle >>

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 serve
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,