Oracle-维护存在主键的分区表时的注意事项

最新推荐文章于 2021-06-17 10:55:21 发布
最新推荐文章于 2021-06-17 10:55:21 发布
阅读量1.6w 收藏 18
点赞数 3
分类专栏： 【Oracle基础】 文章标签： oracle 分区表中的主键 global索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yangshangwei/article/details/80694081
版权

文章目录

在这里插入图片描述

概述

ORACLE关于维护分区表的官方指导文档： Maintaining Partitions

我们知道，当将表中某个字段设置为主键的时候，oracle会自动的创建一个同名的唯一性索引。 分区表亦是如此。

案例

Step1.新建测试表，构造测试数据

我们这里建立一个list-hash的复合分区的测试表 ,同时为ARTISAN_ID这个字段创建了local索引,同时将test_primarykey_id 设置为主键。

-- Create table
create table GLOBAL_INDEX_PRIMARYKEY
(
  test_primarykey_id NUMBER(12) not null,
  artisan_id         NUMBER(12) not null,
  created_date       DATE not null,
  eff_date           DATE not null,
  exp_date           DATE,
  part_id            NUMBER(6) default to_number(to_char(sysdate,'dd')) not null
)
partition by list (PART_ID)
subpartition by hash (TEST_PRIMARYKEY_ID)
(
  partition P1 values (1)
    tablespace TAB_ARTISAN
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition P1_1 tablespace TAB_ARTISAN,
    subpartition P2_1 tablespace TAB_ARTISAN,
    subpartition P3_1 tablespace TAB_ARTISAN,
    subpartition P4_1 tablespace TAB_ARTISAN,
    subpartition P5_1 tablespace TAB_ARTISAN,
    subpartition P6_1 tablespace TAB_ARTISAN,
    subpartition P7_1 tablespace TAB_ARTISAN,
    subpartition P8_1 tablespace TAB_ARTISAN
  ),
  partition P2 values (2)
    tablespace TAB_ARTISAN
    pctfree 10
    initrans 1
    maxtrans 255
  (
    subpartition P1_2 tablespace TAB_ARTISAN,
    subpartition P2_2 tablespace TAB_ARTISAN,
    subpartition P3_2 tablespace TAB_ARTISAN,
    subpartition P4_2 tablespace TAB_ARTISAN,
    subpartition P5_2 tablespace TAB_ARTISAN,
    subpartition P6_2 tablespace TAB_ARTISAN,
    subpartition P7_2 tablespace TAB_ARTISAN,
    subpartition P8_2 tablespace TAB_ARTISAN
  )
);
-- Create/Recreate indexes 
create index IDX_ARTISAN_ID on GLOBAL_INDEX_PRIMARYKEY (ARTISAN_ID)
  local;
-- Create/Recreate primary, unique and foreign key constraints 
alter table GLOBAL_INDEX_PRIMARYKEY
  add constraint PK_GLOBAL_INDEX primary key (TEST_PRIMARYKEY_ID)
  using index 
  tablespace TAB_ARTISAN
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

PLSQL中查看

这里写图片描述

这里写图片描述

然后构造部分数据

select count(1)  from GLOBAL_INDEX_PRIMARYKEY  partition(p1) a ;  -- 140
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p1_1) a ; --8
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p2_1) a ; -- 19
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p3_1) a ;-- 21
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p4_1) a ;-- 13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p5_1) a ;-- 16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p6_1) a ;-- 25
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p7_1) a ;-- 16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p8_1) a ;-- 22


select count(1)  from GLOBAL_INDEX_PRIMARYKEY  partition(p2) a ; -- 90
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p1_2) a ;--12
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p2_2) a ;--13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p3_2) a ;--9
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p4_2) a ;--6
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p5_2) a ;--13
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p6_2) a ;--10
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p7_2) a ;--16
select count(1)  from GLOBAL_INDEX_PRIMARYKEY  subpartition(p8_2) a ;--11

Step2. 查看索引状态

Step2.1 普通索引

索引 如果是N/A 继续查user_ind_partitions

SQL> select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';
 
TABLE_NAME       INDEX_NAME       STATUS
------------------------------ ------------------------------ --------
GLOBAL_INDEX_PRIMARYKEY PK_GLOBAL_INDEX VALID
GLOBAL_INDEX_PRIMARYKEY IDX_ARTISAN_ID N/A
 
SQL>

Step2.2 分区索引

分区索引 如果是N/A 继续查 user_ind_subpartitions

SQL> select a.partition_name, a.index_name, a.status
  2    from user_ind_partitions a
  3   where a.index_name in (select a.index_name
  4                            from user_indexes a
  5                           where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');
 
PARTITION_NAME INDEX_NAME       STATUS
------------------------------ ------------------------------ --------
P1                           IDX_ARTISAN_ID N/A
P2                           IDX_ARTISAN_ID N/A
 
SQL>

Step2.3 子分区索引

子分区， 因为该表复合分区 ,所以应该可以在 user_ind_subpartitions 查看到 索引的状态 USABLE

SQL> select a.index_name, a.partition_name, a.subpartition_name, a.status
  2    from user_ind_subpartitions a
  3   where a.index_name in
  4         (select a.index_name
  5            from user_ind_partitions a
  6           where a.index_name in
  7                 (select a.index_name
  8                    from user_indexes a
  9                   where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));
 
INDEX_NAME       PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
IDX_ARTISAN_ID P1                           P1_1                       USABLE
IDX_ARTISAN_ID P1                           P2_1                       USABLE
IDX_ARTISAN_ID P1                           P3_1                       USABLE
IDX_ARTISAN_ID P1                           P4_1                       USABLE
IDX_ARTISAN_ID P1                           P5_1                       USABLE
IDX_ARTISAN_ID P1                           P6_1                       USABLE
IDX_ARTISAN_ID P1                           P7_1                       USABLE
IDX_ARTISAN_ID P1                           P8_1                       USABLE
IDX_ARTISAN_ID P2                           P1_2                       USABLE
IDX_ARTISAN_ID P2                           P2_2                       USABLE
IDX_ARTISAN_ID P2                           P3_2                       USABLE
IDX_ARTISAN_ID P2                           P4_2                       USABLE
IDX_ARTISAN_ID P2                           P5_2                       USABLE
IDX_ARTISAN_ID P2                           P6_2                       USABLE
IDX_ARTISAN_ID P2                           P7_2                       USABLE
IDX_ARTISAN_ID P2                           P8_2                       USABLE
 
16 rows selected
 
SQL>

Step3. 探究truncate/drop分区对global索引以及local索引的影响

Step3.1 不指定update global indexes的场景

导致全局索引失效，向表中写入数据失败，抛出 ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state 。local索引正常。需要重建global 索引

--- 1.1    140条数据   
select count(1)  from GLOBAL_INDEX_PRIMARYKEY partition(P1) a ;    -- 140 


-- 1.2    truncate 分区   不指定update global indexes的情况即不维护全局索引。  
alter table  GLOBAL_INDEX_PRIMARYKEY  truncate  partition  P1 ;


-- 1.3    全局索引  ---------------- 失效   UNUSABLE状态
select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	UNUSABLE
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


--1.4  local索引   ------------- OK 
select a.partition_name, a.index_name, a.status
  from user_ind_partitions a
 where a.index_name in
       (select a.index_name
          from user_indexes a
         where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));

IDX_ARTISAN_ID	P1	P1_1	USABLE
IDX_ARTISAN_ID	P1	P2_1	USABLE
IDX_ARTISAN_ID	P1	P3_1	USABLE
IDX_ARTISAN_ID	P1	P4_1	USABLE
IDX_ARTISAN_ID	P1	P5_1	USABLE
IDX_ARTISAN_ID	P1	P6_1	USABLE
IDX_ARTISAN_ID	P1	P7_1	USABLE
IDX_ARTISAN_ID	P1	P8_1	USABLE
IDX_ARTISAN_ID	P2	P1_2	USABLE
IDX_ARTISAN_ID	P2	P2_2	USABLE
IDX_ARTISAN_ID	P2	P3_2	USABLE
IDX_ARTISAN_ID	P2	P4_2	USABLE
IDX_ARTISAN_ID	P2	P5_2	USABLE
IDX_ARTISAN_ID	P2	P6_2	USABLE
IDX_ARTISAN_ID	P2	P7_2	USABLE
IDX_ARTISAN_ID	P2	P8_2	USABLE


--1.5 写入数据  ，此时抛出ORA-01502 异常，会影响业务。

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);


ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state 

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation  

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition


-- 1.5 重建 全局索引 

alter index PK_GLOBAL_INDEX  rebuild  online  nologging;

-- 1.6 重新查询 ，全局索引valid状态，OK
select table_name,index_name,status from user_indexes  a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


-- 重新写入数据
insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);

-- OK的.  回滚吧,暂时不写入表里.

Step3.2指定update global indexes的场景

因为指定了update global indexes ，全局索引VALID ，local索引正常。 数据写入正常，不会影响业务。

------ 为了验证 维护全局索引的情况， P1被truncate掉了， 我们使用P2分区 

--- 2.1    90 条数据   
select count(1)  from GLOBAL_INDEX_PRIMARYKEY partition(P2) a ;    -- 90 

--先查下全局索引的状态  VALID 
select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';
GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


-- 2.2    truncate 分区   加  update global indexes的情况 
alter table  GLOBAL_INDEX_PRIMARYKEY  truncate  partition  P2   update global indexes ; 
-- 2.3    全局索引  ---------------- 有效   VALID
select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A




--2.4  local索引   ------------- OK 
select a.partition_name, a.index_name, a.status
  from user_ind_partitions a
 where a.index_name in
       (select a.index_name
          from user_indexes a
         where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY');


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));

IDX_ARTISAN_ID	P1	P1_1	USABLE
IDX_ARTISAN_ID	P1	P2_1	USABLE
IDX_ARTISAN_ID	P1	P3_1	USABLE
IDX_ARTISAN_ID	P1	P4_1	USABLE
IDX_ARTISAN_ID	P1	P5_1	USABLE
IDX_ARTISAN_ID	P1	P6_1	USABLE
IDX_ARTISAN_ID	P1	P7_1	USABLE
IDX_ARTISAN_ID	P1	P8_1	USABLE
IDX_ARTISAN_ID	P2	P1_2	USABLE
IDX_ARTISAN_ID	P2	P2_2	USABLE
IDX_ARTISAN_ID	P2	P3_2	USABLE
IDX_ARTISAN_ID	P2	P4_2	USABLE
IDX_ARTISAN_ID	P2	P5_2	USABLE
IDX_ARTISAN_ID	P2	P6_2	USABLE
IDX_ARTISAN_ID	P2	P7_2	USABLE
IDX_ARTISAN_ID	P2	P8_2	USABLE


--1.5 写入数据   OK 

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);

commit;

select * from GLOBAL_INDEX_PRIMARYKEY  a where a.test_primarykey_id in (8888,9999);

8888	345	2018-06-14	2018-06-14	2018-06-14	1
9999	345	2018-06-14	2018-06-14	2018-06-14	2

Step4. 探究exchange分区对global索引以及local索引的影响

Step4.1 不指定update global indexes的场景

导致全局索引失效，分区索引失效，向表中写入数据失败，抛出 ORA-01502: index "ARTISAN.PK_GLOBAL_INDEX" or partition of such index is in unusable state ，需要重建全局索引和分区索引

select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';
GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));  -- USABLE 

--- 1.1    1 40条数据   
select count(1)  from GLOBAL_INDEX_PRIMARYKEY subpartition(P1_1) a ;    -- 8
-- 1.2     创建临时表，并 exchange  分区   不加  update global indexes的情况  

create table  TEMP_ARTISAN as select * from   GLOBAL_INDEX_PRIMARYKEY  where 1=2 ;

alter table  GLOBAL_INDEX_PRIMARYKEY  exchange   subpartition  P1_1 with  table  TEMP_ARTISAN    ; 

-- 如果想交换 P1 这个包含子分区的分区，就不能使用 non-partitioned table 

Message:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table

Cause:
A composite partition can only be exchanged with a partitioned table.

Action:
Ensure that the table being exchanged is partitioned or that that the partition being exchanged is non-composite.




-- 1.3    全局索引  ---------------- 失效   UNUSABLE状态
select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	UNUSABLE
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A



--1.4  local索引   ------------- 失效
select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));
                 
1	IDX_ARTISAN_ID	P1	P1_1	UNUSABLE
                 

--1.5 写入数据 

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);

ORA-01502: index "CC.PK_GLOBAL_INDEX" or partition of such index is in unusable state 

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation  

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition


-- 1.5 重建 全局索引   和   local 索引  

alter index PK_GLOBAL_INDEX  rebuild  online  nologging;

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (9999, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 2);

--写入OK  

--重建local索引
alter index IDX_ARTISAN_ID  rebuild subpartition P1_1 online;


-- 1.6 重新查询 
select table_name,index_name,status from user_indexes  a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'))
                 and a.subpartition_name = 'P1_1';

IDX_ARTISAN_ID	P1	P1_1	USABLE   -- OK

Step4.2指定update global indexes的场景

因为指定了update global indexes ，全局索引VALID ，但是分区索引失效了，需要重建local索引。

--- 2.1    

select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';
GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));  -- USABLE 

--- 1.1    
select count(1)  from GLOBAL_INDEX_PRIMARYKEY subpartition(P1_2) a ;    -- 12
-- 1.2     创建临时表，并 exchange  分区   加  update global indexes的情况  

create table  TEMP_ARTISAN_2  as select * from   GLOBAL_INDEX_PRIMARYKEY  where 1=2   ;

alter table  GLOBAL_INDEX_PRIMARYKEY  exchange   subpartition  P1_1 with  table  TEMP_ARTISAN_2   UPDATE GLOBAL INDEXES ; 




-- 1.3    全局索引  ----------------  VALID
select  a.table_name ,a.index_name,a.status from user_indexes a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


--1.4  local索引   ------------- USABLE    OK  
select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'));
                 

                 

--1.5 写入数据   OK 

insert into GLOBAL_INDEX_PRIMARYKEY (TEST_PRIMARYKEY_ID, ARTISAN_ID, CREATED_DATE, EFF_DATE, EXP_DATE, PART_ID)
values (8888, 345, to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), to_date('14-06-2018', 'dd-mm-yyyy'), 1);



-- 1.6 重新查询 
select table_name,index_name,status from user_indexes  a where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY';

GLOBAL_INDEX_PRIMARYKEY	PK_GLOBAL_INDEX	VALID
GLOBAL_INDEX_PRIMARYKEY	IDX_ARTISAN_ID	N/A


select a.index_name, a.partition_name, a.subpartition_name, a.status
  from user_ind_subpartitions a
 where a.index_name in
       (select a.index_name
          from user_ind_partitions a
         where a.index_name in
               (select a.index_name
                  from user_indexes a
                 where a.table_name = 'GLOBAL_INDEX_PRIMARYKEY'))
                 and a.subpartition_name = 'P1_2';

IDX_ARTISAN_ID	P2	P1_2	USABLE
  -- OK

Step5 附加

释放回收空间：

alter table table_name truncate partition partition_name drop storage;

维护全局索引：

alter table table_name truncate  partition partition_name update global indexes;

UPDATE GLOBAL INDEXES只维护全局索引

UPDATE INDEXES同时维护全局和本地索引， 经验证，local索引也会失效。 11.2.0.4.0 的版本 。 慎重使用。

INCLUDING INDEXES : 交换分区的同时，也将索引包含进去。 需要新建索引名

ALTER TABLE table_name EXCHANGE PARTITION partition_name WITH TABLE table_name_new INCLUDING INDEXES UPDATE GLOBAL INDEXES
小小工匠
关注
  • 3
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Oracle数据库从入门到精通系列之二十六：理解Oracle数据库分区表，创建Oracle数据库分区表，并插入数据
zhengzaifeidelushang的博客
05-24 316
创建语句创建了一个名为sales_orders的分区表，包含常见字段order_id、order_date、customer_id、product_id、quantity、price、amount和status，并按照order_date字段进行了范围分区，分为四个分区。以上SQL语句向sales_orders表中添加了一个新的分区p201905，该分区包含order_date字段小于2019年6月1日的所有数据。如果需要添加更多的分区，可以按照类似的方式执行ALTER TABLE语句。
分区表不建议创建主键的原因
weixin_34206899的博客
05-19 1120
分区表尽量不要建主键，因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则 会使索引失效，导致数据无法入库。 如果非要建主键，要2种方法： 1.应用上drop 分区表时显示指定update global indexes， 2.将主键上的索引建成本地索引 上述2种方法虽然可以实现，但效果都不好...
oracle绑定主键,请教Oracle如何给分区表添加主键
weixin_39888943的博客
04-02 606
本帖最后由 zhenghaishu 于 2014-9-24 21:58 编辑1 创建季度分区表create table t(mydate varchar2(20), amount number) partition by range(mydate)(partition p20140101 values less than ('20140101') tablespace QUR_DT01,parti...
oracle根据主键分区,oracle分区表和主键关系
weixin_27070451的博客
04-04 470
--全局唯一索引create table test_par1(tdate varchar2(8) primary key)partition by range ( tdate )(partition p1 values less than ('20090201'),partition p2 values less than ('20090301'),partition pm values ...
@Transactional 主键注意事项
liuqiang211的专栏
03-12 406
1.抛出非RuntimeException异常时 事务可以提交 @Transactional public void insertUser() throws FileNotFoundException{ //抛出FileNotFoundException FileInputStream file = new FileInputStream(new File("D：//")); ...
oracle分区表和主键关系
zj0078的专栏
04-26 6861
 --全局唯一索引 create table test_par1 (   tdate   varchar2(8) primary key ) partition by range ( tdate ) (      partition p1 values less than ('20090201'),      partition p2 values less than ('2
【eygle】Oracle的分区表和Local索引创建与维护
ctpq29224的博客
06-07 274
Oracle的分区技术在某些条件下可以极大的提高查询的性能，所以被广泛采用。从产品上说，分区技术是Oracle企业版中独立收费的一个组件。以下是对于分区及本地索引的一个示例。首先根据字典表创建一个测试分区表： SQL> ...
ORACLE19c中的混合分区表的使用.pdf
10-09
在Oracle 19c数据库中，混合分区表是一种高级特性，它允许用户将内部分区（存储在数据库中）与外部分区（存储在操作系统文件中）相结合，以优化大规模数据管理和查询性能。混合分区表的设计目的是为了更好地处理大量...
ORACLE大表分区
12-25
-- 注意：如果在分区化改造过程中数据回迁之前抛出异常,则手动数据回迁前注意检查分区表有无主键索引。 3.分区维护主过程:AGGRE_PM_PARTITIONM.SQL, 意思是PARTITION MANAGEMENT; 4.创建分区维护JOB -- 对在分区...
Oracle的表结构转成Mysql的表结构
05-18
#### 已知限制与注意事项 - **不支持的功能**：此函数目前不支持Oracle中的分区、函数索引、位图索引等特殊索引定义、自定义数据类型、ROWID、RAW等特殊数据类型、外键以及自定义约束。 - **数据类型转换**：Oracle...
Mysql的表对象Sql语句转换单表，转换成Oracle创建表sql
12-31
8. **注意事项**：数据迁移时需考虑字符集兼容性，因为MySQL和Oracle可能使用的字符集不同，可能导致编码问题。 9. **优化与调整**：转换后的Oracle表可能需要根据Oracle的最佳实践进行一些调整，如分区策略、索引...
Oracle堆表转换成分区表方案
10-20
#### 五、注意事项 - 在执行转换操作之前，请确保已经备份了所有重要数据。 - 对于大型数据库，转换操作可能需要较长时间，应合理安排执行时间以减少对业务的影响。 - 转换完成后，应对新的分区表进行全面测试，...
Oracle的分区操作和修改分区主键
weixin_43599216的博客
06-17 905
1、增加一个分区 ALTER TABLE sales ADD PARTITION jan96 VALUES LESS THAN ( ‘01-FEB-1999’ ) TABLESPACE tsx; 增加一个列表分区 ALTER TABLE q1_sales_by_region ADD PARTITION q1_nonmainland VALUES (‘HI’, ‘PR’) STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3 NOLOGGING; 2、合并分区 alt
MySQL主键设计时应注意的问题
Curiosity
08-27 338
学习MySQL数据库，MySQL主键是不能不提到的，下面就对MySQL主键的设计原则进行说明，希望能让您对MySQL主键有更深的认识。 关系数据库依赖于主键---它是数据库物理模式的基石。主键在物理层面上只有两个用途： 1. 惟一地标识一行。 2. 作为一个可以被外键有效引用的对象。 基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则： 1. MySQL主键应当是对...
oracle根据主键分区,Oracle的分区操作和修改分区主键
weixin_42741930的博客
04-04 293
1、增加一个分区ALTER TABLE salesADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )TABLESPACE tsx;增加一个列表分区ALTER TABLE q1_sales_by_regionADD PARTITION q1_nonmainland VALUES ('HI', 'PR')STORAGE (INITIAL 20K...
【ORACLE】创建表-主键分区索引
m0_47139992的博客
04-19 933
– Create table create table xxx ( f1 VARCHAR2(12) not null, f2 VARCHAR2(8) not null, sjjcpch VARCHAR2(20), sjjcsj DATE ) partition by list (f2) ( partition P201701 values (‘201701’) tablespace txx pctfree 10 initrans 1 maxtran
Oracle 表分区与索引分区
D-Boy的专栏
04-26 7165
分区技术简介Oracle是最早支持物理分区的数据库管理系统供应商，表分区的功能是在Oracle 8.0版本推出的。分区功能能够改善应用程序的性能、可管理性和可用性，是数据库管理中一个非常关键的技术。尤其在今天，数据库应用系统的规模越来越大，还有海量数据的数据仓储系统，因此，几乎所有的Oracle数据库都使用分区功能来提高查询的性能，并且简化数据库的日常管理维护工作。 那么使用分区技术有哪些优点呢？
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

小小工匠

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值