5-1 Data Warehouse Management - Partitioned tables and indexes using appropriate methods and keys

2023. 4. 8. 19:57

References: Oracle Docs ( Database Concepts)

SELECT partition_name, table_name, tablespace_name, subpartition_count FROM dba_tab_partitions

난이도: ★☆☆☆☆

문제 1: Range Partitioned Table (10분)

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 TABLE range_part_2 (id NUMBER, name VARCHAR2(20), surname VARCHAR2(20), month NUMBER, day NUMBER)
PARTITION BY RANGE(month, day)
(PARTITION part_jan VALUES LESS THAN (2,1),
 PARTITION part_feb VALUES LESS THAN (3,1),
 PARTITION part_max VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

난이도: ★☆☆☆☆

문제 2: List Partitioned Table (10분)

CREATE TABLE list_part
(id NUMBER, name VARCHAR2(10), city VARCHAR2(20))
PARTITION BY LIST(city)
(PARTITION part_osaka VALUES ('OSAKA'),
 PARTITION part_tokyo VALUES ('TOKYO'),
 PARTITION part_nagoya VALUES ('NAGOYA'),
 PARTITION part_default VALUES (DEFAULT)
);

난이도: ★★☆☆☆

문제 3: Hash Partitioned Table (10분)

CREATE TABLE hash_part
PARTITION BY HASH(object_id)
PARTITIONS 8
AS
SELECT * FROM dba_objects WHERE rownum <= 1000;

SELECT	table_name, partition_name
FROM	dba_tab_partitions
WHERE	table_name = 'HASH_PART';

SELECT count(*) FROM hash_part PARTITION(SYS_P325);

CREATE TABLESPACE tbs_part1 DATAFILE '/tmp/tbs_part1.dbf' SIZE 50M;
CREATE TABLESPACE tbs_part2 DATAFILE '/tmp/tbs_part2.dbf' SIZE 50M;
CREATE TABLESPACE tbs_part3 DATAFILE '/tmp/tbs_part3.dbf' SIZE 50M;
CREATE TABLESPACE tbs_part4 DATAFILE '/tmp/tbs_part4.dbf' SIZE 50M;

CREATE TABLE tbl_hash_part1
PARTITION BY HASH (object_id)
PARTITIONS 8
store in (tbs_part1, tbs_part2, tbs_part3, tbs_part4)
AS
SELECT * FROM dba_objects;

난이도: ★★☆☆☆

문제 4: Interval Partitioned Table (daily, monthly) (15분)

CREATE TABLE interval_part
(id NUMBER, name VARCHAR2(20), surname VARCHAR2(20), ddate DATE)
PARTITION BY RANGE(ddate) INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION part1 VALUES LESS THAN (to_date('20230401','yyyymmdd'))
);

CREATE TABLE interval_part_02
(id NUMBER, name VARCHAR2(20), surname VARCHAR2(20), ddate DATE)
PARTITION BY RANGE(ddate) INTERVAL(NUMTODSINTERVAL(1,'DAY'))
(PARTITION part1 VALUES LESS THAN (to_date('20230401','yyyymmdd'))
);

난이도: ★★★☆☆

문제 5: Reference Partitioned View (10분)

CREATE TABLE ref_part (id NUMBER PRIMARY KEY, name VARCHAR2(20), surname VARCHAR2(20))
PARTITION BY RANGE (id)
(PARTITION p1 VALUES LESS THAN (10),
 PARTITION p2 VALUES LESS THAN (20)
);

CREATE TABLE ref_part_02 (id NUMBER PRIMARY KEY, name VARCHAR2(20), CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES ref_part (id))
PARTITION BY REFERENCE (fk_id);

난이도: ★★☆☆☆

문제 6: Range-List Partitioning (10분)

CREATE TABLE range_list_part
PARTITION BY RANGE (object_id)
SUBPARTITION BY LIST (object_type)
(PARTITION part1 VALUES LESS THAN (100)
	(SUBPARTITION part1_sub1 VALUES ('INDEX'),
     SUBPARTITION part1_sub2 VALUES ('TABLE')),
 PARTITION part2 VALUES LESS THAN (200)
 	(SUBPARTITION part2_sub1 VALUES ('INDEX'),
     SUBPARTITION part2_sub2 VALUES ('TABLE')),
 PARTITION part3 VALUES LESS THAN (MAXVALUE)
 )
 AS
 SELECT * FROM dba_objects WHERE object_type IN ('INDEX','TABLE');
 
 
SELECT count(1) FROM range_list_part SUBPARTITION (part1_sub2);
SELECT count(1) FROM range_list_part SUBPARTITION (part2_sub1);

난이도: ★★☆☆☆

문제 7: Range-Hash Partitioning (10분)

CREATE TABLE range_hash_part
PARTITION BY RANGE (object_id)
SUBPARTITION BY HASH (namespace)
SUBPARTITIONS 4
(PARTITION part1 VALUES LESS THAN (100),
 PARTITION part2 VALUES LESS THAN (200),
 PARTITION part3 VALUES LESS THAN (MAXVALUE))
AS
SELECT * FROM dba_objects WHERE rownum <= 1000;

난이도: ★★☆☆☆

문제 8: Range-Range Partitioning (10분)

CREATE TABLE range_range_part
PARTITION BY RANGE (object_id)
SUBPARTITION BY RANGE (namespace)
(PARTITION part1 VALUES LESS THAN (100)
	(SUBPARTITION part1_sub1 VALUES LESS THAN (5),
	 SUBPARTITION part1_sub2 VALUES LESS THAN (MAXVALUE)),
 PARTITION part2 VALUES LESS THAN (MAXVALUE)
	(SUBPARTITION part2_sub1 VALUES LESS THAN (5),
	 SUBPARTITION part2_sub2 VALUES LESS THAN (MAXVALUE))
)
AS
SELECT * FROM dba_objects WHERE rownum <= 300;



SELECT	table_name, partition_name, subpartition_count
FROM	dba_tab_partitions
WHERE	table_name = 'RANGE_RANGE_PART';

난이도: ★★☆☆☆

문제 9: List-Range Partitioning (10분)

CREATE TABLE list_range_part
PARTITION BY LIST (object_type)
SUBPARTITION BY RANGE (object_id)
(PARTITION part1 VALUES ('INDEX','TABLE')
	(SUBPARTITION part1_sub1 VALUES LESS THAN (100),
	 SUBPARTITION part1_sub2 VALUES LESS THAN (MAXVALUE)),
 PARTITION part2 VALUES ('SYNONYM')
	(SUBPARTITION part2_sub1 VALUES LESS THAN (100),
	 SUBPARTITION part2_sub2 VALUES LESS THAN (MAXVALUE))
)
AS
SELECT * FROM dba_objects WHERE object_type IN ('INDEX','TABLE','SYNONYM');

난이도: ★★☆☆☆

문제 10: List-List Partitioning (10분)

CREATE TABLE list_list_part
PARTITION BY LIST (object_type)
SUBPARTITION BY LIST (status)
(PARTITION part1 VALUES ('INDEX')
	(SUBPARTITION part1_sub1 VALUES ('VALID'),
	 SUBPARTITION part1_sub2 VALUES ('INVALID')),
 PARTITION part2 VALUES ('TABLE')
	(SUBPARTITION part2_sub1 VALUES ('VALID'),
	 SUBPARTITION part2_sub2 VALUES ('INVALID'))
)
AS
SELECT * FROM dba_objects WHERE object_type IN ('INDEX','TABLE');

난이도: ★★☆☆☆

문제 11: Hash-Hash Partitioning (10분)

/* SUBPARTITIONS 구문을 PARTITIONS보다 먼저 사용해야 한다. */

CREATE TABLE hash_hash_part
PARTITION BY HASH (object_id)
SUBPARTITION BY HASH (namespace)
SUBPARTITIONS 4
PARTITIONS 4
AS
SELECT * FROM dba_objects WHERE rownum <= 200;

난이도: ★★☆☆☆

문제 12: Interval-List Partitioning (10분)

CREATE TABLE interval_list_part
PARTITION BY RANGE (created) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST (object_type)
(PARTITION part1 VALUES LESS THAN (to_date('20230101','yyyymmdd'))
	(SUBPARTITION part1_table VALUES ('TABLE'),
	 SUBPARTITION part1_index VALUES ('INDEX'))
)
AS
SELECT * FROM dba_objects WHERE object_type IN ('TABLE','INDEX');

난이도: ★★☆☆☆

문제 13: Interval-Range Partitioning (10분)

CREATE TABLE interval_range_part
PARTITION BY RANGE (created) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE (object_id)
(PARTITION part1 VALUES LESS THAN (to_date('20230101','yyyymmdd'))
	(SUBPARTITION part1_table VALUES LESS THAN (10),
	 SUBPARTITION part1_index VALUES LESS THAN (MAXVALUE))
)
AS
SELECT * FROM dba_objects;

난이도: ★★☆☆☆

문제 14: Interval-Hash Partitioning (10분)

CREATE TABLE interval_hash_part
PARTITION BY RANGE (created) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (object_id)
SUBPARTITIONS 4
(PARTITION part1 VALUES LESS THAN (to_date('20230101','yyyymmdd'))
)
AS
SELECT * FROM dba_objects;

난이도: ★★★☆☆

문제 15: Virtual Column based Partitioning (10분)

CREATE TABLE virt_part (id NUMBER, price NUMBER, quantity NUMBER, total_value AS (price * quantity) VIRTUAL)
PARTITION BY RANGE (total_value)
(PARTITION part1 VALUES LESS THAN (1000),
 PARTITION part2 VALUES LESS THAN (2000)
);


INSERT INTO virt_part (id, price, quantity) VALUES (1,100,8);
INSERT INTO virt_part (id, price, quantity) VALUES (2,300,6);

SELECT * FROM virt_part PARTITION (part1);
SELECT * FROM virt_part PARTITION (part2);

저작자표시

'Oracle > OCM Journey' 카테고리의 다른 글

5-3 Data Warehouse Management - Maintain indexes on a partitioned table (0)	2023.04.10
5-2 Data Warehouse Management - Partition Maintenance Operations (0)	2023.04.10
4-9 Database Management - SQL*Loader (0)	2023.04.07
4-8 Database Management - Configure and use parallel execution for queries (0)	2023.04.07
4-7 Database Management - Data Pump to and from remote databases (0)	2023.04.07

Ryuhouse

5-1 Data Warehouse Management - Partitioned tables and indexes using appropriate methods and keys

'Oracle > OCM Journey' 카테고리의 다른 글

If you like this post, please give me a ❤️...!

✰Popular Posts✰

✰Recent Posts✰

❤ Seoul, Daejeon, Tokyo, Fukuoka

Site developed by Ryu Hyunwoo

티스토리툴바