实验：分区别对比普通表

创建分区表
range的
create table rg_yzy (
OBJECT_NAME VARCHAR2(128),
OBJECT_ID number,
CREATED date not null)
partition by range(created)
(
partition rg_001 values less than (to_date('2012/01/01','yyyy/mm/dd')),
partition rg_002 values less than (to_date('2013/01/01','yyyy/mm/dd'))
);


ORA-14037: partition bound of partition "RG_001" is too high
http://blog.csdn.net/pengje/article/details/1551216


创建本地索引
create index idx_rg_yzy on rg_yzy(created) local
(partition rg_001 tablespace users,
partition rg_002 tablespace users);


插入数据
insert into rg_yzy select OBJECT_NAME,object_id,created from dba_objects where created < to_date('2013/01/01','yyyy/mm/dd') and object_id is not null;


ORA-14400: inserted partition key does not map to any partition


http://stackoverflow.com/questions/24454591/oracle-partition-error-ora14400-inserted-partition-key-does-not-map-to-any-p
插入失败，调整后插入0行！
增加一个分区


alter table rg_yzy add partition rg_003 values less than (to_date('2015/01/01','yyyy/mm/dd'));




SQL> select segment_name,partition_name,tablespace_name from dba_segments where segment_name='IDX_RG_YZY';
SEGMENT_NAME                                                                     PARTITION_NAME                 TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------------------
IDX_RG_YZY                                                                       RG_003                         USERS
IDX_RG_YZY                                                                       RG_002                         USERS
IDX_RG_YZY                                                                       RG_001                         USERS


SQL> select segment_name,partition_name,tablespace_name from dba_segments where segment_name='RG_YZY';
SEGMENT_NAME                                                                     PARTITION_NAME                 TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------ ----------------------
RG_YZY                                                                           RG_003                         USERS
RG_YZY                                                                           RG_002                         USERS
RG_YZY                                                                           RG_001                         USERS
可见分区索引是自动维护的
再次插入数据：
insert into rg_yzy select OBJECT_NAME,object_id,created from dba_objects where created < to_date('2015/01/01','yyyy/mm/dd') and object_id is not null；
87752 rows inserted
SQL> commit;
Commit complete


SQL> select count(*) from rg_yzy partition(rg_001);
  COUNT(*)
----------
         0


SQL> select count(*) from rg_yzy partition(rg_002);
  COUNT(*)
----------
         0


SQL> select count(*) from rg_yzy partition(rg_003);
  COUNT(*)
----------
     87752
发现不符合对测试环境的要求
修改分区，数据不合适，直接update
SQL> alter table lcl.rg_yzy enable row movement;
Table altered


SQL> update rg_yzy set created = to_date('2012/08/24','yyyy/mm/dd') where rownum<40000;
39999 rows updated


SQL> commit;
Commit complete


SQL> alter table lcl.rg_yzy disable row movement;
Table altered


SQL> select count(*) from rg_yzy partition(rg_003);
  COUNT(*)
----------
     47753


SQL> select count(*) from rg_yzy partition(rg_002);
  COUNT(*)
----------
     39999


SQL> select count(*) from rg_yzy partition(rg_001);
  COUNT(*)
----------
         0
         
SQL> alter table lcl.rg_yzy enable row movement;
Table altered


SQL> update rg_yzy set created = to_date('2011/08/24','yyyy/mm/dd') where rownum<1000;
999 rows updated


SQL> alter table lcl.rg_yzy disable row movement;
Table altered


SQL> commit;
Commit complete


SQL> select count(*) from rg_yzy partition(rg_003);
  COUNT(*)
----------
     47753


SQL> select count(*) from rg_yzy partition(rg_002);
  COUNT(*)
----------
     39000


SQL>  select count(*) from rg_yzy partition(rg_001);
  COUNT(*)
----------
       999
       
现在可以了，实验开始
创建数据量一样的普通表
SQL> create table rg_yzy_cm  as select OBJECT_NAME,OBJECT_ID,CREATED from dba_objects;
Table created
update数据
SQL> update rg_yzy_cm set created = to_date('2012/08/24','yyyy/mm/dd') where rownum<40000;
39999 rows updated


SQL> commit;
Commit complete


SQL> update rg_yzy_cm set created = to_date('2011/08/24','yyyy/mm/dd') where rownum<1000;
999 rows updated


SQL> commit;
Commit complete;
查询速度对比：
select count(*) from rg_yzy where created<to_date('2012/08/20','yyyy/mm/dd');


select count(*) from rg_yzy_cm where created<to_date('2012/08/20','yyyy/mm/dd');
分区表：


SQL> select count(*) from rg_yzy where created<to_date('2012/08/20','yyyy/mm/dd');
       999




Execution Plan
----------------------------------------------------------
Plan hash value: 504157362


--------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |            |     1 |     9 |    11   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |            |     1 |     9 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|            |   999 |  8991 |    11   (0)| 00:00:01 |     1 |     2 |
|*  3 |    INDEX RANGE SCAN       | IDX_RG_YZY |   999 |  8991 |    11   (0)| 00:00:01 |     1 |     2 |
--------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   3 - access("CREATED"<TO_DATE(' 2012-08-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         76  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
          
数据量不太行啊。。。。