Oracle 75일차

by

[HASH PARTITION 실습]

1. 대상 테이블 선정

select owner, segment_name, partition_name, tablespace_name, bytes/1024/1024
from dba_segments
where owner in ('SCOTT','HR','SH','OE')
and segment_type='TABLE'
order by 5 desc;

select * from SH.CUSTOMERS;

2. 대상 선정
TABLE : SH.CUSTOMERS
PARTITION KEY : CUST_ID
PARTITION 수 : 4

select * from SH.CUSTOMERS;
SELECT * FROM DBA_DATA_FILES;

3. 새 TBS 생성 (각 파티션을 위한 저장공간 설계)

CREATE TABLESPACE TS_PART1 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TS_PART01.DBF' SIZE 10M;
CREATE TABLESPACE TS_PART2 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TS_PART02.DBF' SIZE 10M;
CREATE TABLESPACE TS_PART3 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TS_PART03.DBF' SIZE 10M;
CREATE TABLESPACE TS_PART4 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TS_PART04.DBF' SIZE 10M;

-- SH 유저에게 새로 생긴 TBS에 대한 QUOTA 부여
ALTER USER SH QUOTA UNLIMITED ON TS_PART1;
ALTER USER SH QUOTA UNLIMITED ON TS_PART2;
ALTER USER SH QUOTA UNLIMITED ON TS_PART3;
ALTER USER SH QUOTA UNLIMITED ON TS_PART4;

4. PARTITION TABLE 생성

CREATE TABLE SH.CUSTOMERS_PT
(
    CUST_ID                 NUMBER NOT NULL,
    CUST_FIRST_NAME         VARCHAR2(20) NOT NULL,
    CUST_LAST_NAME          VARCHAR2(40) NOT NULL,
    CUST_GENDER             CHAR(1) NOT NULL,
    CUST_YEAR_OF_BIRTH      NUMBER(4) NOT NULL,
    CUST_MARITAL_STATUS     VARCHAR2(20),
    CUST_STREET_ADDRESS     VARCHAR2(40) NOT NULL,
    CUST_POSTAL_CODE        VARCHAR2(10) NOT NULL,
    CUST_CITY               VARCHAR2(30) NOT NULL,
    CUST_CITY_ID            NUMBER NOT NULL,
    CUST_STATE_PROVINCE     VARCHAR2(40) NOT NULL,
    CUST_STATE_PROVINCE_ID  NUMBER NOT NULL,
    COUNTRY_ID              NUMBER NOT NULL,
    CUST_MAIN_PHONE_NUMBER  VARCHAR2(25) NOT NULL,
    CUST_INCOME_LEVEL       VARCHAR2(30),
    CUST_CREDIT_LIMIT       NUMBER,
    CUST_EMAIL              VARCHAR2(30),
    CUST_TOTAL              VARCHAR2(14) NOT NULL,
    CUST_TOTAL_ID           NUMBER NOT NULL,
    CUST_SRC_ID             NUMBER,
    CUST_EFF_FROM           DATE,
    CUST_EFF_TO             DATE,
    CUST_VALID              VARCHAR2(1)
)
PARTITION BY HASH(CUST_ID) (
PARTITION P1 TABLESPACE TS_PART1,
PARTITION P2 TABLESPACE TS_PART2,
PARTITION P3 TABLESPACE TS_PART3,
PARTITION P4 TABLESPACE TS_PART4);

-- 기존 데이터 적재
INSERT INTO SH.CUSTOMERS_PT SELECT * FROM SH.CUSTOMERS;
COMMIT;

5. NON PARTITION TABLE 생성

CREATE TABLE SH.CUSTOMERS_OR
AS
SELECT * FROM SH.CUSTOMERS;

6. 실행 계획 비교

-- PARTITION TABLE
SELECT *
FROM SH.CUSTOMERS_PT
WHERE CUST_ID = 5000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));

-- NON PARTITION TABLE
SELECT *
FROM SH.CUSTOMERS_OR
WHERE CUST_ID = 5000;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));

-- 비교 결과 COST와 BUFFERS 가 103대 405, 375대 1459로 PARTITION 된 게 월등히 좋았다.

7. 특정 파티션 데이터 조회

SELECT *
FROM SH.CUSTOMERS_PT PARTITION(P1)
WHERE CUST_ID = 5000;
-- 조회 안 됨.

SELECT *
FROM SH.CUSTOMERS_PT PARTITION(P2)
WHERE CUST_ID = 5000;
-- 조회 됨. 즉 2번 파티션에 5000번인 애가 존재함.

[LIST PARTITION 실습]

1. 대상 선정
TABLE : SCOTT.DELIVERY
PARTITION KEY : 업종
PARTITION 수 : 4

SELECT * FROM SCOTT.DELIVERY

-- 업종 별 분포 확인
SELECT 업종, COUNT(*)
FROM SCOTT.DELIVERY
GROUP BY 업종;

2. 새 TBS 생성

CREATE TABLESPACE TBS1 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TBS01.DBF' SIZE 10M;
CREATE TABLESPACE TBS2 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TBS02.DBF' SIZE 10M;
CREATE TABLESPACE TBS3 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TBS03.DBF' SIZE 10M;
CREATE TABLESPACE TBS4 DATAFILE 'D:\APP\ITWILL\ORADATA\ORCL\TBS04.DBF' SIZE 10M;

-- SCOTT 유저에게 새로 생긴 TBS에 대한 QUOTA 부여
ALTER USER SCOTT QUOTA UNLIMITED ON TS_PART1;
ALTER USER SCOTT QUOTA UNLIMITED ON TS_PART2;
ALTER USER SCOTT QUOTA UNLIMITED ON TS_PART3;
ALTER USER SCOTT QUOTA UNLIMITED ON TS_PART4;

3. PARTITION TABLE 생성

CREATE TABLE SCOTT.DELIVERY_PT
(
    "일자"      VARCHAR2(8),
    "시간대"    VARCHAR2(2),
    "업종"      VARCHAR2(100),
    "시도"      VARCHAR2(50),
    "시군구"    VARCHAR2(50),
    "읍면동"    VARCHAR2(50),
    "통화건수"  NUMBER
)
PARTITION BY LIST(업종) (
PARTITION P1 VALUES('피자') TABLESPACE TBS1,
PARTITION P2 VALUES('음식점-족발/보쌈전문') TABLESPACE TBS2,
PARTITION P3 VALUES('음식점-중국음식') TABLESPACE TBS3,
PARTITION P4 VALUES('치킨') TABLESPACE TBS4);

INSERT INTO SCOTT.DELIVERY_PT SELECT * FROM SCOTT.DELIVERY;
COMMIT;

4. NON PARTITION TABLE 생성

CREATE TABLE SCOTT.DELIVERY_OR
AS
SELECT * FROM SCOTT.DELIVERY;

5. 실행 계획 비교

-- PARTITION
SELECT *
FROM SCOTT.DELIVERY_PT
WHERE 업종='치킨';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- COST : 104, BUFFERS : 685

-- NON PARTITION
SELECT *
FROM SCOTT.DELIVERY_OR
WHERE 업종='치킨';
select * from table(dbms_xplan.display_cursor(null,null,'allstats last advanced'));
-- COST : 263, BUFFERS : 1323

[파티션 테이블 관리]

1. 파티션 테이블 조회

SELECT *
FROM DBA_SEGMENTS
WHERE OWNER IN ('SCOTT','HR','SH','OE')
AND PARTITION_NAME IS NOT NULL;

SELECT *
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER IN ('SCOTT','HR','SH','OE')
ORDER BY 1,2,4;

2. 파티션 추가

ALTER TABLE OE.ORDERS_PT ADD PARTITION P2009 VALUES LESS THAN(TO_DATE('2010/01/01','YYYY/MM/DD'));
-- 에러 발생) ORA-14074
-- MAXVALUE 파티션(PMAX)의 범위와 새로 만들 파티션(P2009)의 범위가 중복되므로 생성 불가
-- 따라서 MAXVALUE 파티션을 SPLIT을 해야 됨.

3. 파티션 분리

ALTER TABLE OE.ORDERS_PT SPLIT PARTITION PMAX AT (TO_DATE('2010/01/01','YYYY/MM/DD')) INTO (PARTITION P2009, PARTITION PMAX);

-- 조회
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'ORDERS_PT'
ORDER BY 3;

4. 파티션 삭제

ALTER TABLE OE.ORDERS_PT DROP PARTITION P2004;

-- 조회
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'ORDERS_PT'
ORDER BY 3;

Leave a Comment