Oracle分区,索引,测试(1)(二)
放在对应表空间
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,
p
artition 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_2009