Oracle 12c数据库之后异步全局索引维护特性测试

 

测试数据库版本： 19.27

1. 创建测试表my_part_tab

create table my_part_tab(
id number,
name varchar2(40),
birthday date)
PARTITION BY RANGE ("BIRTHDAY") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "P01" VALUES LESS THAN (TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')));

2.插入数据，自动创建分区

set serverout on
declare
x number:=1;
begin
loop
--dbms_output.put_line(x);
insert into my_part_tab values (x, dbms_random.string('u',8), sysdate+x/14400);
x:=x+1;
exit when x>100000;
end loop;
commit;
end;
/

3.查看表分区

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='MY_PART_TAB';

TABLE_NAME           PARTITION_NAME       TABLESPACE_NAME
-------------------- -------------------- ------------------------------
MY_PART_TAB          P01                  USERS
MY_PART_TAB          SYS_P5289            USERS
MY_PART_TAB          SYS_P5290            USERS
MY_PART_TAB          SYS_P5291            USERS
MY_PART_TAB          SYS_P5292            USERS
MY_PART_TAB          SYS_P5293            USERS
MY_PART_TAB          SYS_P5294            USERS
MY_PART_TAB          SYS_P5295            USERS
MY_PART_TAB          SYS_P5296            USERS

4.创建全局所以，查看所以状态

SQL> create unique index pk_idx_id on my_part_tab(id);

Table altered.

SQL> alter table my_part_tab add primary key(id) using index pk_idx_id;

Table altered.

SQL> select owner, index_name, status, orphaned_entries from dba_indexes where table_name = 'MY_PART_TAB';

OWNER                INDEX_NAME           STATUS   ORP
-------------------- -------------------- -------- ---
SCOTT                PK_IDX_ID            VALID    NO

5. truncate 分区，再次查看全局索引状态

SQL> alter table my_part_tab truncate partition SYS_P5296 update global indexes;

Table truncated.

SQL> select owner, index_name, status, orphaned_entries from dba_indexes where table_name = 'MY_PART_TAB';

OWNER                INDEX_NAME           STATUS   ORP
-------------------- -------------------- -------- ---
SCOTT                PK_IDX_ID            VALID    YES

6.清理孤立索引

SQL> EXEC DBMS_PART.CLEANUP_GIDX(SCHEMA_NAME_IN=>'SCOTT', TABLE_NAME_IN=>'MY_PART_TAB');

PL/SQL procedure successfully completed.

SQL> select owner, index_name, status, orphaned_entries from dba_indexes where table_name = 'MY_PART_TAB';

OWNER                INDEX_NAME           STATUS   ORP
-------------------- -------------------- -------- ---
SCOTT                PK_IDX_ID            VALID    NO

或者：

SQL> ALTER INDEX PK_IDX_ID COALESCE CLEANUP;

Index altered.

SQL> select owner, index_name, status, orphaned_entries from dba_indexes where table_name = 'MY_PART_TAB';

OWNER                INDEX_NAME           STATUS   ORP
-------------------- -------------------- -------- ---
SCOTT                PK_IDX_ID            VALID    NO

 

使用隐含参数"_fast_index_maintenance"关闭异步全局索引维护特性， 再次truncate分区， 不再产生孤立索引

SQL> alter session set "_fast_index_maintenance" = false;

Session altered.

SQL> alter table my_part_tab truncate partition SYS_P5295 update global indexes;

Table truncated.

SQL> select owner, index_name, status, orphaned_entries from dba_indexes where table_name = 'MY_PART_TAB';

OWNER                INDEX_NAME           STATUS   ORP
-------------------- -------------------- -------- ---
SCOTT                PK_IDX_ID            VALID    NO