PMO_DEFERRED_GIDX_MAINT_JOB

 

 

- 12c 이후 부터 파티션 테이블 작업 시 update indexes 구문을 통한 글로벌 인덱스 online 작업 가능 - PMO_DEFERRED_GIDX_MAINT_JOB 에 의해서 매일 02:00에 실제 인덱스 갱신 작업이 발생하고 그전까진 SYS.INDEX_ORPHANED_ENTRY_V$ SYS.INDEX_ORPHANED_ENTRY$ 등에 저장된 정보를 사용하는 것으로 보인다.

 

SQL> create table psk.table1 (col1 varchar2(20))
partition by list (col1)
(partition dgomez_table1_p1 VALUES ('guatemala'),
 partition dgomez_table1_p2 VALUES ('brasil'),
 partition dgomez_table1_p3 VALUES ('colombia'));

Statement Processed.

SQL> insert into psk.table1 values ('guatemala');
SQL> insert into psk.table1 values ('brasil');
SQL> insert into psk.table1 values ('colombia');
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('PSK','TABLE1');

PL/SQL executed.


SQL> select TABLE_NAME, PARTITION_NAME,NUM_ROWS from dba_tab_partitions where TABLE_OWNER='PSK' and table_name='TABLE1';

TABLE_NAME                             PARTITION_NAME                            NUM_ROWS         
-------------------------------------- ----------------------------------------- ---------------- 
TABLE1                                 DGOMEZ_TABLE1_P1                                         1
TABLE1                                 DGOMEZ_TABLE1_P2                                         1
TABLE1                                 DGOMEZ_TABLE1_P3                                         1

3 rows selected.

SQL> create index psk.index1 on psk.table1 (col1) global;

Statement Processed.

SQL> select * from psk.table1 where col1='brasil';

COL1                 
-------------------- 
brasil              

1 rows selected.

SQL> select index_name, status,orphaned_entries from dba_indexes where owner='PSK' and index_name='INDEX1';

INDEX_NAME                        STATUS       ORPHANED_ENTRIES                 
--------------------------------- ------------ -------------------------------- 
INDEX1                            VALID        NO                              

1 rows selected.


-- 파티션 DROP
alter table psk.table1 drop partition dgomez_table1_p1 update indexes;

SQL> select * from SYS.INDEX_ORPHANED_ENTRY_V$;

INDEX_OWNER                     INDEX_NAME                         INDEX_SUBNAME                INDEX_OBJECT_ID                TABLE_OWNER               TABLE_NAME          TABLE_SUBNAME           TABLE_OBJECT_ID                TYPE     
------------------------------- ---------------------------------- ---------------------------- ------------------------------ ------------------------- ------------------- ----------------------- ------------------------------ -------- 
PSK                             INDEX1                                                                                   87760 PSK                       TABLE1                                                               87753 O       

1 rows selected.


SQL> select * from index_orphaned_entry$;

INDEXOBJ#          TABPARTDOBJ#             HIDDEN       
------------------ ------------------------ ------------ 
             87760                    87754 O           

1 rows selected.


-- SQL 실행계획, 필터 부분 추가됨
select * from psk.table1 where col1='guatemala';
 
Execution Plan
-----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=9)
   1    0   INDEX (RANGE SCAN) OF 'PSK.INDEX1' (INDEX) (Cost=1 Card=1 Bytes=9)
-----------------------------------------------------------

Predicate information (identified by operation id):
-----------------------------------------------------------
   1 - access("COL1"='guatemala')
   1 - filter(TBL$OR$IDX$PART$NUM("PSK"."TABLE1",0,8,0,"TABLE1".ROWID)=1)
-----------------------------------------------------------

-- 추가 파티션 DROP
SQL> alter table psk.table1 drop partition dgomez_table1_p2 update indexes;

Statement Processed.

SQL> select * from SYS.INDEX_ORPHANED_ENTRY_V$;

INDEX_OWNER             INDEX_NAME          INDEX_SUBNAME               INDEX_OBJECT_ID                TABLE_OWNER                    TABLE_NAME                     TABLE_SUBNAME             TABLE_OBJECT_ID                TYPE     
----------------------- ------------------- --------------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------- ------------------------------ -------- 
PSK                     INDEX1                                                                   87760 PSK                            TABLE1                                                                            87753 O       
PSK                     INDEX1                                                                   87760 PSK                            TABLE1                                                                            87753 O       

2 rows selected.

SQL> select * from index_orphaned_entry$;

INDEXOBJ#          TABPARTDOBJ#             HIDDEN       
------------------ ------------------------ ------------ 
             87760                    87754 O           
             87760                    87755 O           

2 rows selected.

 

참조 : https://blog.toadworld.com/2017/04/07/oracle-database-12c-deferred-global-index