oracle-维护存在主键的分区表时的注意事项（oracle分区键和主键）

文章目录

概述

案例

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

Step2. 查看索引状态

Step2.1 普通索引

Step2.2 分区索引

Step2.3 子分区索引

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

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

Step3.2指定update global indexes的场景

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

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

Step4.2指定update global indexes的场景

Step5 附加

概述

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 );

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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>

1

2

3

4

5

6

7

8

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>

1

2

3

4

5

6

7

8

9

10

11

12

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>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

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的. 回滚吧,暂时不写入表里.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

Step5 附加

释放回收空间：

alter table table_name truncate partition partition_name drop storage;

1

维护全局索引：

alter table table_name truncate partition partition_name update global indexes;

1

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

1

Oracle

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。