分区的方法

SQL> conn sys/123 as sysdba


SQL> create tablespace ts_sales_2009_1 datafile 'D:\sqldata\sales_2009_1.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_2 datafile 'D:\sqldata\sales_2009_2.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_3 datafile 'D:\sqldata\sales_2009_3.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_4 datafile 'D:\sqldata\sales_2009_4.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_5 datafile 'D:\sqldata\sales_2009_5.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_6 datafile 'D:\sqldata\sales_2009_6.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_7 datafile 'D:\sqldata\sales_2009_7.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_8 datafile 'D:\sqldata\sales_2009_8.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_9 datafile 'D:\sqldata\sales_2009_9.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_10 datafile 'D:\sqldata\sales_2009_10.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_11 datafile 'D:\sqldata\sales_2009_11.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2009_12 datafile 'D:\sqldata\sales_2009_12.dbf' size 50M autoextend on;

Tablespace created

SQL> create tablespace ts_sales_2010_1 datafile 'D:\sqldata\sales_2010_1.dbf' size 50M autoextend on;

Tablespace created

SQL> conn scott/scott
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott@ORCL

SQL> create table sale_data
2 (
3 sale_id number(5),
4 salesman_name varchar2(30),
5 sales_amount number(5),
6 sales_date date
7 )
8 partition by range(sales_date)
9 (
10 partition sales_2009_1 values less than(to_date('01/02/2009','DD/MM/YYYY')) tablespace ts_sales_2009_1,
11 partition sales_2009_2 values less than(to_date('01/03/2009','DD/MM/YYYY')) tablespace ts_sales_2009_2,
12 partition sales_2009_3 values less than(to_date('01/04/2009','DD/MM/YYYY')) tablespace ts_sales_2009_3,
13 partition sales_2009_4 values less than(to_date('01/05/2009','DD/MM/YYYY')) tablespace ts_sales_2009_4,
14 partition sales_2009_5 values less than(to_date('01/06/2009','DD/MM/YYYY')) tablespace ts_sales_2009_5,
15 partition sales_2009_6 values less than(to_date('01/07/2009','DD/MM/YYYY')) tablespace ts_sales_2009_6,
16 partition sales_2009_7 values less than(to_date('01/08/2009','DD/MM/YYYY')) tablespace ts_sales_2009_7,
17 partition sales_2009_8 values less than(to_date('01/09/2009','DD/MM/YYYY')) tablespace ts_sales_2009_8,
18 partition sales_2009_9 values less than(to_date('01/10/2009','DD/MM/YYYY')) tablespace ts_sales_2009_9,
19 partition sales_2009_10 values less than(to_date('01/11/2009','DD/MM/YYYY')) tablespace ts_sales_2009_10,
20 partition sales_2009_11 values less than(to_date('01/12/2009','DD/MM/YYYY')) tablespace ts_sales_2009_11,
21 partition sales_2009_12 values less than(to_date('01/01/2010','DD/MM/YYYY')) tablespace ts_sales_2009_12,
22 partition sales_2010_1 values less than(to_date('01/02/2010','DD/MM/YYYY')) tablespace ts_sales_2010_1
23 );

Table created

SQL> create index indsale_date on sale_data(sales_date)
2 local
3 (
4 partition sales_2009_1 tablespace ts_sales_2009_1,
5 partition sales_2009_2 tablespace ts_sales_2009_2,
6 partition sales_2009_3 tablespace ts_sales_2009_3,
7 partition sales_2009_4 tablespace ts_sales_2009_4,
8 partition sales_2009_5 tablespace ts_sales_2009_5,
9 partition sales_2009_6 tablespace ts_sales_2009_6,
10 partition sales_2009_7 tablespace ts_sales_2009_7,
11 partition sales_2009_8 tablespace ts_sales_2009_8,
12 partition sales_2009_9 tablespace ts_sales_2009_9,
13 partition sales_2009_10 tablespace ts_sales_2009_10,
14 partition sales_2009_11 tablespace ts_sales_2009_11,
15 partition sales_2009_12 tablespace ts_sales_2009_12,
16 partition sales_2010_1 tablespace ts_sales_2010_1
17 );

Index created

SQL> insert into sale_data values(12,'aas',33,to_date('20090303 09:19:10','yyyymmdd hh24:mi:ss'));

SQL> col SALES_DATE FORMAT A30;
SQL> select sale_id,sales_date from sale_data where Sales_date>to_date('2009/03/01','yyyy/mm/dd hh24:mi:ss') and Sales_date<to_date('2009/04/01','yyyy/mm/dd hh24:mi:ss');