分区的方法

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');