DBA Hub

📋Steps in this guide1/2

Asynchronous (Delayed) Global Index Maintenance for DROP and TRUNCATE Partition in Oracle Database 12c Release 1

Oracle 12c can optimize the performance of some DROP PARTITION and TRUNCATE PARTITION commands by deferring the associated index maintenance, while leaving the global indexes in a valid state.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Setup

The following code creates and populates a partitioned table with global indexes.

Code/Command (click line numbers to comment):

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
-- Create a partitioned table with some global indexes.
DROP TABLE t1 PURGE;

CREATE TABLE t1
(id            NUMBER,
 description   VARCHAR2(50),
 created_date  DATE)
PARTITION BY RANGE (created_date)
(PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users);

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

CREATE INDEX t1_idx ON t1 (created_date);


-- Populate it so segments are created.
INSERT /*+ APPEND */ INTO t1
SELECT level,
       'Description for ' || level,
       CASE
         WHEN MOD(level,2) = 0 THEN TO_DATE('01/07/2014', 'DD/MM/YYYY')
         ELSE TO_DATE('01/07/2015', 'DD/MM/YYYY')
       END
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 't1');


-- Check the indexes.
COLUMN table_name FORMAT A20
COLUMN index_name FORMAT A20

SElECT table_name,
       index_name,
       status
FROM   user_indexes
ORDER BY 1,2;

TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- --------
T1                   T1_IDX               VALID
T1                   T1_PK                VALID

SQL>
2

Asynchronous Global Index Maintenance

When combined with the clause the and commands now result in metadata-only index maintenance. This functionality is only available for heap tables and is not supported on tables with object types, domain indexes or those owned by SYS. The actual index maintenance is performed at a later time, triggered by one of the following. - The job is scheduled to run at 02:00 every day. - The job can be run manually using the procedure. - Run the procedure. - Run the command. If we just dropped or truncated a partition, the global indexes would be marked as invalid. Prior to 12c, using the clause would cause them to be rebuilt as part of the operation, making the whole operation slower. In the following example we truncate a partition and check the status of the indexes. The new column in the view shows the index maintenance has not been done yet. If we manually trigger the index maintenance, we can see the change reflected in the column. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

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
-- Truncate a partition.
ALTER TABLE t1 TRUNCATE PARTITION part_2014 DROP STORAGE UPDATE INDEXES;
-- ALTER TABLE t1 DROP PARTITION part_2014 UPDATE INDEXES;


-- Check the status of the indexes.
SElECT table_name,
       index_name,
       status
FROM   user_indexes
ORDER BY 1,2;

TABLE_NAME           INDEX_NAME           STATUS
-------------------- -------------------- --------
T1                   T1_IDX               VALID
T1                   T1_PK                VALID

SQL>

-- Check if index maintenance is needed.
SELECT index_name,
       orphaned_entries
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           ORP
-------------------- ---
T1_IDX               YES
T1_PK                YES

SQL>

-- Manually trigger the index maintenance.
EXEC DBMS_PART.cleanup_gidx(USER, 't1');


-- Check if index maintenance is needed.
SELECT index_name,
       orphaned_entries
FROM   user_indexes
ORDER BY 1;

INDEX_NAME           ORP
-------------------- ---
T1_IDX               NO
T1_PK                NO

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!