ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总十

oracle10g--11gR2分区表汇总十

原创 Linux操作系统 作者:jack22220613 时间:2011-03-21 09:34:42 0 删除 编辑

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语法

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    350287