5-3 Data Warehouse Management - Maintain indexes on a partitioned table

/* Local index is the preferred index to be used with the partitioned table which inherits the partitioning criteria from the underlying table. Global partitioned indexes is partitioned independently with the key that is different from table’s partitioning key. */

 

 

난이도: ★☆☆☆☆

문제 1: LOCAL 파티션 인덱스를 생성하라. (2분)

 

CREATE TABLE range_part (id NUMBER, name VARCHAR2(20), surname VARCHAR2(20), ddate DATE)
PARTITION BY RANGE(ddate)
(PARTITION part_2022 VALUES LESS THAN (to_date('01-JAN-2022','dd-MON-yyyy')),
 PARTITION part_2023 VALUES LESS THAN (to_date('01-JAN-2023','dd-MON-yyyy')),
 PARTITION part_MAX VALUES LESS THAN (MAXVALUE)
);


CREATE INDEX idx_range_part ON range_part(ddate) LOCAL;
CREATE INDEX idx_range_part2 ON range_part(id) LOCAL;

SELECT count(1) FROM dba_ind_partitions WHERE index_name='IDX_RANGE_PART';

 

 

 

 

 

난이도: ★★☆☆☆

문제 2:  GLOBAL 파티션 인덱스를 생성하라. (2분)

 

CREATE TABLE tbl_part_range_global_idx
PARTITION BY RANGE (object_id)
(
PARTITION part1 VALUES LESS THAN (1000),
PARTITION part2 VALUES LESS THAN (4000),
PARTITION part3 VALUES LESS THAN (maxvalue)
)
AS
SELECT * FROM dba_objects;


CREATE INDEX idx_part_range_global_1 ON tbl_part_range_global_idx (object_id)
GLOBAL PARTITION BY RANGE (object_id)
(PARTITION part1 VALUES LESS THAN (100),
 PARTITION part2 VALUES LESS THAN (200),
 PARTITION part3 VALUES LESS THAN (MAXVALUE));

SELECT count(*) FROM dba_ind_partitions WHERE index_name='IDX_PART_RANGE_GLOBAL_1';

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 3: 인덱스 파티션을 Rebuilding하여라. (2분)

 

ALTER INDEX idx_part_range_global_1 REBUILD PARTITION part1;

ALTER INDEX ... MODIFY PARTITION ... REBUILD UNUSABLE LOCAL INDEXES;