分区索引分时间段进行建立

今天在itpub上看到一个哥们提出想分时间段建立分区，于是进行了如下实验，证明是可以进行的：：
[oracle@localhost ~]$ sqlplus /nolog


SQL*Plus: Release 11.2.0.4.0 Production on 星期三 8月 6 16:12:25 2014


Copyright (c) 1982, 2013, Oracle.  All rights reserved.


SQL> conn change/change
Connected.
SQL> 
SQL> 
SQL> 
SQL> CREATE TABLE range_example
  2  (range_key_column date ,
  3   data varchar2(20)
  4  )
  5  PARTITION BY RANGE (range_key_column)
  6  ( PARTITION part_1 VALUES LESS THAN  (to_date('01/01/2012','dd/mm/yyyy')),
  7    PARTITION part_2  VALUES LESS THAN (to_date('01/01/2013','dd/mm/yyyy')),
  8    PARTITION part_3 VALUES LESS THAN  (MAXVALUE)
  9  );


Table created.


SQL>  insert into range_example (range_key_column,data) values(to_date('15-12-2012 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data');


1 row created.


SQL> insert into range_example (range_key_column,data) values(to_date('31-12-2012 23:59:59','dd-mm-yyyy hh24:mi:ss'),'application data');


1 row created.


SQL>  insert into  range_example (range_key_column,data) values(to_date('15-12-2011 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data');


1 row created.


SQL>  insert into  range_example (range_key_column,data) values(to_date('31-12-2011 23:59:59','dd-mm-yyyy hh24:mi:ss'),'application data');


1 row created.


SQL> commit;


Commit complete.


SQL>  select * from range_example;


RANGE_KEY_CO DATA
------------ ------------------------------------------------------------
15-12月-11   application data
31-12月-11   application data
15-12月-12   application data
31-12月-12   application data




SQL>  CREATE INDEX idx_range_example ON range_example (range_key_column)  LOCAL (PARTITION part_1 UNUSABLE, PARTITION part_2,PARTITION part_3);


Index created.




SQL>  SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS
  2    FROM USER_INDEXES
  3   WHERE INDEX_NAME = 'IDX_RANGE_EXAMPLE'
  4  UNION ALL
  5  SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS
  6    FROM USER_IND_PARTITIONS
  7   WHERE PARTITION_NAME LIKE '%PART_%';


INDEX OR PARTITION N STATUS
-------------------- ------------------------
IDX_RANGE_EXAMPLE    N/A
PART_1               UNUSABLE
PART_2               USABLE
PART_3               USABLE



SQL>   SELECT p.PARTITION_NAME,
  2           p.STATUS AS "PART_STATUS",
  3           p.SEGMENT_CREATED AS "SEG_CREATED" 
  4      FROM USER_IND_PARTITIONS p, USER_SEGMENTS s
  5     WHERE s.SEGMENT_NAME = 'IDX_RANGE_EXAMPLE';


PARTITION_ PART_STATUS              SEG_CREAT
---------- ------------------------ ---------
PART_1     UNUSABLE                 NO
PART_3     USABLE                  YES
PART_2     USABLE                 YES



SQL> alter index IDX_RANGE_EXAMPLE rebuild partition  PART_1 nologging parallel;


Index altered.


SQL>  
SQL>  SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS
  2    FROM USER_INDEXES
  3   WHERE INDEX_NAME = 'IDX_RANGE_EXAMPLE'
  4  UNION ALL
  5  SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS
  6    FROM USER_IND_PARTITIONS
  7   WHERE PARTITION_NAME LIKE '%PART_%';


INDEX OR PARTITION N STATUS
-------------------- ------------------------
IDX_RANGE_EXAMPLE    N/A
PART_1                USABLE
PART_2               USABLE
PART_3               USABLE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10701850/viewspace-1246665/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10701850/viewspace-1246665/