11g中的分区表及分区索引

在创建分区表的时候，可以指定是否启用行移动，就是分区键在更新的时候，数据是否迁移到新的分区，默认的情况下是不启用的。在创建范围分区的时候，可以使用interval来自动创建新分区，分区字段必须是整形或日期。

CREATE TABLE customers_demo (

customer_id number(6),

cust_first_name varchar2(20),

cust_last_name varchar2(20),

credit_limit number(9,2))

PARTITION BY RANGE (credit_limit)

INTERVAL (1000)

(PARTITION p1 VALUES LESS THAN (5001));

SQL> insert into customers_demo values(1,'bai','xiaoyu',100);

已创建 1 行。

SQL> commit;

提交完成。

SQL> insert into customers_demo values(1,'bai','xiaoyu',1200);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',5000);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',5001);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',5002);

已创建 1 行。

SQL> commit;
SQL> select table_name,partition_name,tablespace_name,interval,segment_created from user_tab_partitions where table_name=upper('customers_demo');


TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME      INT SEG
-------------------- -------------------- -------------------- --- ---
CUSTOMERS_DEMO       P1                   SYSTEM               NO  YES
CUSTOMERS_DEMO       SYS_P21              SYSTEM               YES YES

SQL> select * from customers_demo partition(p1);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                        100
          1 bai                  xiaoyu                       1200
          1 bai                  xiaoyu                       5000

SQL> select * from customers_demo partition(SYS_P21);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       5001

在默认设置的情况下，更新分区列

SQL> update customers_demo set credit_limit=60001 where customer_id=1;
update customers_demo set credit_limit=60001 where customer_id=1
       *
第 1 行出现错误:
ORA-14402: 更新分区关键字列将导致分区的更改

看到提示不允许更新操作，对表进行启用行移动，row movement与row 迁移不同，行迁移是指原地址放个指针指向新的地方，rowid不变，行移动是rowid都变了。

SQL> alter table customers_demo enable row movement;

表已更改。

SQL> update customers_demo set credit_limit=60001 where customer_id=1;

已更新5行。

SQL> commit;

提交完成。

SQL> select table_name,partition_name,tablespace_name,interval,segment_created from user_tab_partitions where table_name=upper('customers_demo');


TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME      INT SEG
-------------------- -------------------- -------------------- --- ---
CUSTOMERS_DEMO       P1                   SYSTEM               NO  YES
CUSTOMERS_DEMO       SYS_P21              SYSTEM               YES YES
CUSTOMERS_DEMO       SYS_P22              SYSTEM               YES YES
SQL> select * from customers_demo partition(p1);

未选定行

SQL> select * from customers_demo partition(sys_p21);

未选定行

SQL> select * from customers_demo partition(sys_p22);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                      60001
          1 bai                  xiaoyu                      60001
          1 bai                  xiaoyu                      60001
          1 bai                  xiaoyu                      60001
          1 bai                  xiaoyu                      60001

全部移动到新的分区了

CREATE TABLE customers_demo (
  customer_id number(6),
  cust_first_name varchar2(20),
  cust_last_name varchar2(20),
  credit_limit number(9,2))
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE users
PARTITION BY RANGE (credit_limit)
INTERVAL (1000)STORE IN (baixyu1,baixyu2, baixyu3)
(PARTITION p1 VALUES LESS THAN (5001) tablespace system);

SQL> insert into customers_demo values(1,'bai','xiaoyu',100);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',5002);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',6002);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',7002);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',8002);

已创建 1 行。

SQL> insert into customers_demo values(1,'bai','xiaoyu',9002);

已创建 1 行。

SQL> select table_name,partition_name,tablespace_name,interval,segment_created from user_tab_partitions where table_name=upper('custom


TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME                INT SEG
-------------------- -------------------- ------------------------------ --- ---
CUSTOMERS_DEMO       P1                   SYSTEM                         NO  YES
CUSTOMERS_DEMO       SYS_P23              BAIXYU2                        YES YES
CUSTOMERS_DEMO       SYS_P24              BAIXYU3                        YES YES
CUSTOMERS_DEMO       SYS_P25              BAIXYU1                        YES YES
CUSTOMERS_DEMO       SYS_P26              BAIXYU2                        YES YES
CUSTOMERS_DEMO       SYS_P27              BAIXYU3                        YES YES

已选择6行。

SQL> select * from customers_demo partition(p1);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                        100

SQL> select * from customers_demo partition(sys_p23);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       5002

SQL> select * from customers_demo partition(sys_p24);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       6002

SQL> select * from customers_demo partition(sys_p25);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       7002

SQL> select * from customers_demo partition(sys_p26);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       8002

SQL> select * from customers_demo partition(sys_p27);

CUSTOMER_ID CUST_FIRST_NAME      CUST_LAST_NAME       CREDIT_LIMIT
----------- -------------------- -------------------- ------------
          1 bai                  xiaoyu                       9002

SQL>

可以看到分区是在轮询的使用指定的表空间。

在分区表上创建本地索引

create index idx_id on customers_demo(customer_id) local;

select index_name,partition_name,tablespace_name from user_ind_partitions;
SQL> select index_name,partition_name,tablespace_name from user_ind_partitions;

INDEX_NAME           PARTITION_NAME       TABLESPACE_NAME
-------------------- -------------------- ------------------------------
IDX_ID               P1                   SYSTEM
IDX_ID               SYS_P28              BAIXYU2
IDX_ID               SYS_P29              BAIXYU3
IDX_ID               SYS_P30              BAIXYU1
IDX_ID               SYS_P31              BAIXYU2
IDX_ID               SYS_P32              BAIXYU3

看到对应的索引是分区索引并且存放的位置是分区的表空间。

删除本地分区索引，创建普通索引

SQL> drop index idx_id ;

索引已删除。

SQL> create index idx_id2 on customers_demo(customer_id);

索引已创建。

SQL> select index_name,partition_name,tablespace_name from user_ind_partitions;

未选定行
SQL> select index_name,tablespace_name from user_indexes;

INDEX_NAME           TABLESPACE_NAME
-------------------- --------------------
IDX_ID2              USERS
PK_EMP               USERS
PK_DEPT              USERS

看到上面创建的就是普通的索引。删除普通索引，创建全局索引。全局索引既可以是分区的，也可以不是分区的。

SQL> create index idx_id3 on customers_demo(customer_id) global;

索引已创建。

SQL> select index_name,partition_name,tablespace_name from user_ind_partitions;

未选定行

SQL> select index_name,tablespace_name from user_indexes;

INDEX_NAME           TABLESPACE_NAME
-------------------- --------------------
IDX_ID3              USERS
PK_EMP               USERS
PK_DEPT              USERS

上面这种全局索引就是非分区的全局索引，全局索引中包含了所有的分区数据。

本地索引，一个分区内的索引只维护对应分区的数据，当查询中的where条件中查询了几个分区的数据，本地分区索引效果可能就不高了，要用全局索引。

那么分区表上的普通索引与非分区全局索引有什么区别呢？

SQL> create index idx_id3 on customers_demo(customer_id) global;

索引已创建。

SQL> select dbms_metadata.get_ddl('INDEX','IDX_ID3') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_ID3')
--------------------------------------------------------------------------------

  CREATE INDEX "SCOTT"."IDX_ID3" ON "SCOTT"."CUSTOMERS_DEMO" ("CUSTOMER_ID")

看定义的话全局非分区索引跟普通的索引是一样的。全局分区索引维护上比较麻烦，分区做变更的时候，不会自动维护。那么费分区全局索引呢？下面测试

SQL> select table_name,partition_name,tablespace_name,interval,segment_created from user_tab_partitions where table_name=upper('customers_demo');


TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME      INT SEG
-------------------- -------------------- -------------------- --- ---
CUSTOMERS_DEMO       P1                   SYSTEM               NO  YES
CUSTOMERS_DEMO       SYS_P23              BAIXYU2              YES YES
CUSTOMERS_DEMO       SYS_P24              BAIXYU3              YES YES
CUSTOMERS_DEMO       SYS_P25              BAIXYU1              YES YES
CUSTOMERS_DEMO       SYS_P26              BAIXYU2              YES YES
CUSTOMERS_DEMO       SYS_P27              BAIXYU3              YES YES

已选择6行。

SQL> alter table  customers_demo drop partition baixyu3;
alter table  customers_demo drop partition baixyu3
                                           *
第 1 行出现错误:
ORA-02149: 指定的分区不存在


SQL> alter table  customers_demo drop partition sys_p27;

表已更改。

SQL> alter table  customers_demo drop partition p1;
alter table  customers_demo drop partition p1
                                           *
第 1 行出现错误:
ORA-14758: 不能删除范围段中的最后一个分区

看到p1是手工创建的分区，是最后一个不能删除，下面查看下索引的状态，可以看到对于非分区的全局索引也是一样的，也不会自动去维护

SQL> select index_name,status from user_indexes;

INDEX_NAME           STATUS
-------------------- --------
IDX_ID3              UNUSABLE
PK_EMP               VALID
PK_DEPT              VALID

原文链接：https://blog.csdn.net/aoerqileng/article/details/53539283

你可能也喜欢