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小時內刪除侵權內容。