1.2 system分区

实验1：

SQL>

SQL>

SQL> create table tab_1(time date,id number,city_id number,value1 number,value2 varchar2(10))

  2  partition by system

  3  (

  4      partition p_t_tab_0 tablespace tbs_1,

  5      partition p_t_tab_1 tablespace tbs_2,

  6      partition p_t_tab_2 tablespace tbs_3,

  7      partition p_t_tab_3 tablespace tbs_4

  8  )

  9  ;

Table created.

SQL> select partition_name from user_tab_partitions where table_name='TAB_1';

PARTITION_NAME                                                                 

------------------------------                                                 

P_T_TAB_0                                                                      

P_T_TAB_1                                                                      

P_T_TAB_2                                                                       

P_T_TAB_3                                                                      

SQL> insert into tab_1(id) values(1);

insert into tab_1(id) values(1)

            *

ERROR at line 1:

ORA-14701: partition-extended name or bind variable must be used for DMLs on

tables partitioned by the System method

--会报错，insert 方式应该按如下写法：

SQL> insert into tab_1 partition (p_t_tab_0) values(to_date('2011-1-1 1:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

1 row created.

--在insert时，需执行partition

SQL> insert into tab_1 partition (p_t_tab_1) values(sysdate,2,20,2,'b');

1 row created.

SQL> select * from tab_1;

TIME              ID    CITY_ID     VALUE1 VALUE2                              

--------- ---------- ---------- ---------- ----------                          

01-JAN-11          1         10          1 a                                   

18-MAR-11          2         20          2 b                                   

SQL> explain plan for select * from tab_1 where id=1;

Explained.

--在查询时最好指定partition，否则会partition all扫描

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT                                                                                                      

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 1728362101                                                                                            

----------------------------------------------------------------------------------------------                         

| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                         

----------------------------------------------------------------------------------------------                         

|   0 | SELECT STATEMENT     |       |     1 |    55 |     4   (0)| 00:00:01 |       |       |                         

|   1 |  PARTITION SYSTEM ALL|       |     1 |    55 |     4   (0)| 00:00:01 |     1 |     4 |                         

|*  2 |   TABLE ACCESS FULL  | TAB_1 |     1 |    55 |     4   (0)| 00:00:01 |     1 |     4 |                         

----------------------------------------------------------------------------------------------                         

Predicate Information (identified by operation id):                                                                     

---------------------------------------------------                                                                    

   2 - filter("ID"=1)                                                                                                  

Note                                                                                                                   

-----                                                                                                                  

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

18 rows selected.

SQL> commit;

Commit complete.

SQL> delete from tab_1 partition(p_t_tab_0) ;

1 row deleted.

SQL> delete from tab_1 partition(p_t_tab_1);

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> delete from tab_1;

2 rows deleted.

--delete时可以指定分区，也可以不指定

SQL> rollback;

Rollback complete.

SQL> select * from tab_1;

TIME              ID    CITY_ID     VALUE1 VALUE2                                                                      

--------- ---------- ---------- ---------- ----------                                                                  

01-JAN-11          1         10          1 a                                                          

18-MAR-11          2         20          2 b                                   

试验2：

SQL> create unique index ind_t_tab on tab_1(id) loacl;

create unique index ind_t_tab on tab_1(id) loacl

                                           *

ERROR at line 1:

ORA-02158: invalid CREATE INDEX option

--system分区的时候，不能创建本地的unique index，这主要是因为在system分区时，不需要指定分区键，而创建本地的unique index时，需要分区键作为索引的子集

SQL> create unique index ind_t_tab on tab_1(id);

Index created.

SQL> drop index ind_t_tab;

Index dropped.

SQL> create index ind_t_tab on tab_1(id) local;

Index created.

--创建本地的normal index 还是没问题的

SQL> explain plan for select * from tab_1 partition(p_t_tab_0) where id=1;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3341454122

-------------------------------------------------------------------------------------------------

| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |       |     1 |    55 |     3   (0)| 00:00:01 |       |       |

|   1 |  PARTITION SYSTEM SINGLE|       |     1 |    55 |     3   (0)| 00:00:01 |     1 |     1 |

|*  2 |   TABLE ACCESS FULL     | TAB_1 |     1 |    55 |     3   (0)| 00:00:01 |     1 |     1 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ID"=1)

Note

-----

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

18 rows selected.

SQL> create table tab_2    

  2  partition by system

  3  ( partition p1 tablespace tbs_1)

  4  as select 1 as id from dual;

as select 1 as id from dual

*

ERROR at line 4:

ORA-14704: Create table as select disallowed for SYSTEM patitioned tables

--system分区的表不支持CTAS语法