oracle10g--11gR2分区表汇总十
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/，如需转载，请注明出处，否则将追究法律责任。
