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
上一个:Oracle分区,索引,测试(2)
下一个:创建DBlinksql
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?