따봉 구걸남's 블로그

오라클 서브파티션 테스트 

 - range-list 구조의 복합파티션 관리하기

 - 템플릿을 등록하고 파티션 추가시 템플릿대로 추가되는지 확인 

DROP TABLE TB_TEST_CMP;

CREATE TABLE TB_TEST_CMP
(
    CLS_YM VARCHAR2(6),
    COCD1  VARCHAR2(3),
    COCD2  VARCHAR2(3),
    VAL    NUMBER
)
PARTITION BY RANGE (CLS_YM)
SUBPARTITION BY LIST (COCD1, COCD2)
SUBPARTITION TEMPLATE
(
    SUBPARTITION L100_200 VALUES ('100', '200'),
    SUBPARTITION L100_400 VALUES ('100', '400'),
    SUBPARTITION L_DEF VALUES (DEFAULT)
)
(
    PARTITION PT_202601 VALUES LESS THAN ('202602'),
    PARTITION PT_202602 VALUES LESS THAN ('202603'),
    PARTITION PT_202603 VALUES LESS THAN ('202604'),
    PARTITION PT_MAX    VALUES LESS THAN (MAXVALUE)
);

-- PT_202603 파티션에 수동으로 서브파티션 추가
ALTER TABLE TB_TEST_CMP MODIFY PARTITION PT_202603 ADD SUBPARTITION L500_600 VALUES ('500', '600');
--> SQL Error [14621] [72000]: ORA-14621: DEFAULT 하위 분할 영역이 존재하는 경우 하위 분할 영역을 추가할 수 없음

 
-- 디폴트 서브파티션을 쪼개는 방식으로 추가 
ALTER TABLE TB_TEST_CMP SPLIT SUBPARTITION PT_202603_L_DEF INTO
(
    SUBPARTITION PT_202603_L500_600 VALUES ('500', '600'),
    SUBPARTITION PT_202603_L700_800 VALUES ('700', '800'),
    SUBPARTITION PT_202603_L_DEF   
);

서브파티션이 변경이 되었으니 새로 템플릿을 지정하자

-- 템플릿 재지정 
ALTER TABLE TB_TEST_CMP SET SUBPARTITION TEMPLATE();

ALTER TABLE TB_TEST_CMP SET SUBPARTITION TEMPLATE
(
    SUBPARTITION L100_200 VALUES ('100', '200'),
    SUBPARTITION L100_400 VALUES ('100', '400'),
    SUBPARTITION L500_600 VALUES ('500', '600'),  -- 새로 추가된 서브파티션 1
    SUBPARTITION L700_800 VALUES ('700', '800'),  -- 새로 추가된 서브파티션 2
    SUBPARTITION L_DEF    VALUES (DEFAULT)
);

-- 템플릿 확인 
SELECT *
  FROM DBA_SUBPARTITION_TEMPLATES
 WHERE table_name ='TB_TEST_CMP'
;

-- PT_MAX 파티션을 SPLIT 하여 PT_202604 생성
ALTER TABLE TB_TEST_CMP SPLIT PARTITION PT_MAX AT ('202605')
INTO (PARTITION PT_202604, PARTITION PT_MAX);

-- maxvalue 파티션을 분할하는 경우 maxvalue 파티션 구조를 따라와서 템플릿과 다르게 생성됨 
SELECT table_owner, table_name, partition_name, composite, subpartition_count, high_value
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'TB_TEST_CMP'
;

-- 서브파티션 생성 결과 확인
SELECT PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE, subpartition_position
FROM DBA_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'TB_TEST_CMP'
ORDER BY PARTITION_NAME, SUBPARTITION_NAME;



-- maxvalue 파티션 제거하고 add partition 

SELECT * FROM TB_TEST_CMP partition(PT_MAX);

ALTER TABLE TB_TEST_CMP DROP PARTITION PT_202604;
ALTER TABLE TB_TEST_CMP DROP PARTITION PT_MAX;


ALTER TABLE TB_TEST_CMP ADD 
    PARTITION PT_202604 VALUES LESS THAN ('202605'),
    PARTITION PT_202605 VALUES LESS THAN ('202606'),
    PARTITION PT_202606 VALUES LESS THAN ('202607'),
    PARTITION PT_MAX    VALUES LESS THAN (MAXVALUE)
;

-- add partition 은 템플릿대로 생성이 됨.
SELECT table_owner, table_name, partition_name, composite, subpartition_count, high_value
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = 'TB_TEST_CMP'
;

 - maxvalue 파티션이 있는경우 split 방식으로 파티션이 추가됨. 템플릿이 아닌 split 대상과 구조가 동일

 - default 서브파티션이 있는경우 서브파티션추가는 default 를 분할하는 방식으로 추가됨. 

 - 템플릿으로 추가시 서브파티션 명은 "파티션명_"+"템플릿의서브파티션명" 으로 지정됨. 

조인을 통한  업데이트 작업시 보통 서브쿼리를 활용하게 되는데 

EMP 와 DEPT 라는 테이블을 예시로 들면 아래와 같은데.. 

UPDATE emp e
SET e.dname = (
  SELECT d.dname
  FROM dept d
  WHERE d.deptno = e.deptno
)
WHERE EXISTS (
  SELECT 1
  FROM dept d
  WHERE d.deptno = e.deptno
);

이 방식의 단점은 SET 절의 서브쿼리가 업데이트할 행마다 반복 실행된다는 점이다. 

단 건의 업데이트는 비효율이 크지 않지만 대량 업데이트 일수록 단점이 커진다. 

오라클에서는 이를 수정가능 조인뷰로 처리할수 있고 아래와 같다. 

UPDATE (
  SELECT e.dname AS e_dname,
         d.dname AS d_dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.deptno IS NOT NULL
)
SET e_dname = d_dname; 

주의할 점은 1:M 관계에서 M 에 해당하는 컬럼만 수정이 가능한 제약이 있다. 

위의 경우에서는 dept 에 속한 컬럼을 수정하려고 하면 에러가 날 것이다. 

UPDATE (
  SELECT e.empno,,
 d.dname
  FROM emp e
  JOIN dept d ON e.deptno = d.deptno
  WHERE e.empno = 7788
)
SET dname = 'RESEARCH';

 => 에러발생 

ora-01779 cannot modify a column which maps to a non key-preserved table

 # 타 DBMS 에서는.. 

- PostgreSQL: 조인이 포함된 뷰는 기본적으로 수정 불가  (단, INSTEAD OF 트리거를 사용하면 우회 가능)
- MySQL: 일부 조건에서 조인 뷰 업데이트가 가능, 오라클만큼 유연하지는 않으며 제약이 많다.
- SQL Server: 조인 뷰 업데이트는 제한적, 대부분 INSTEAD OF 트리거를 사용

성능뷰에서 SQL 수집하기 

 -- 셀프로 선정 v$sqlarea

SELECT *
FROM (
    SELECT
        parsing_schema_name,
        sql_id,
        plan_hash_value,
        executions,
        buffer_gets,       -- logical reads
        disk_reads,        -- physical reads
        rows_processed,
        cpu_time,          -- total CPU time in microseconds
        elapsed_time,      -- total elapsed time in microseconds
        ROUND(cpu_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_cpu_sec,
        ROUND(elapsed_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_elapsed_sec,
        SUBSTR(sql_text, 1, 100) AS sql_text_snippet
    FROM v$sqlarea
    WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM')
      AND executions > 0
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 20;

-- sql full text 확인
SET LONG 100000
SET PAGESIZE 500
SET LINESIZE 150

--SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, 100000, 1) AS full_sql_text
SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, DBMS_LOB.GETLENGTH(SQL_FULLTEXT), 1)
-- SELECT SQL_FULLTEXT
FROM v$sql
WHERE sql_id = 'gngtvs38t0060';

 -- 바인드 값
SELECT sql_id, name, position, datatype_string, value_string, was_captured, last_captured
  FROM v$sql_bind_capture
 WHERE sql_id = '91gau055wpf2p'
 ORDER BY position;

-- ACS 확인
 SELECT sql_id,
              sql_fulltext,
             IS_BIND_SENSITIVE,
             IS_BIND_AWARE,
             LAST_LOAD_TIME,  
             SQL_PROFILE,
             SQL_PLAN_BASELINE,
             LAST_ACTIVE_TIME
   FROM v$sql
  WHERE sql_id =''