[SQLP필기풀이]6장 고급SQL튜닝(4) 파티셔닝

Yu River·2022년 8월 5일
SQLPSQLP필기풀이
0

SQLP필기연습

목록 보기
21/35

✍️ 1번 : 파티셔닝을 통해 얻을 수 있는 이점

다음 중 파티셔닝을 통해 얻을 수 있는 이점과 가장 거리가 먼 것.

  1. 성능 향상 👉 ⭕️
  2. 경합 분산 👉 ⭕️
  3. 가용성 향상 👉 ⭕️
  4. 저장 공간 절약 👉 ❌

🍋 기출 포인트

  1. 테이블을 파티셔닝하면 저장 공간 측면에서는 오히려 효율성이 떨어진다.여유 공간을 세그먼트 단위로 관리하기 때문이다.
  2. 테이블을 파티셔닝하면 성능 향상 및 경합 분산에 도움이 되고, 백업 및 복구,변경 및 삭제 등을 파티션 단위로 빠르게 처리할 수 있어 가용성이 향상된다.

✍️ 2번 : Range 파티셔닝

Range 파티션에 대한 설명으로 가장 적절한 것

  1. 과거 파티션에 대항 변경이나 백업 또는 복구를 진행하는 중에도 최신 파티션을 통한 변경, 삭제는 정상적으로 진행할 수 있다. 👉 ⭕️
  2. 파티션 기준 컬럼은 NUMBER 또는 DATE 형이어야 한다. 👉 ❌
  3. 파티션 기준 컬럼은 하나만 선택할 수 있다. 👉 ❌
  4. 입력 공간을 찾지 못해 에러가 발생하는 현상을 방지하려면 DEFAULT 파티션을 추가해야 한다. 👉 ❌

🍋 기출 포인트

  1. Range 파티셔닝은 파티션 기준으로 여러 컬럼을 선택할 수 있다.
  2. 입력 공간을 찾지 못해 에러가 발생하는 현상을 방지하려면 MAXVALUE 파티션을 추가해야 한다.

✍️ 3번 : 파티셔닝 구문

주문 테이블을 반기별로 파티셔닝하고자 할 때 아래에 들어갈 구문을 완성시켜라

- 주문 테이블에 입력하려고 백업해 둔 임시 테이블에는 2020년 1월부터 2021년 10월까지의 주문 데
이터가 입력돼 있음
- 예상치 못한 주문일시 입력되더라도 에러가 발생하지 않아야 함
- 파티션 명명 규칙은 자유롭게 지정

CREATE TABLE 주문 (
주문번호 NUMBER
, 주문일시 DATE
, 고객 ID VARCHAR2(5)
, 주문금액 NUMBER )
??

답

PARTITION BY RANGE (주문일시) (
	PARTITION P1 VALUES LESS THAN (TO_DATE('20200701','YYYYMMDD')) ,
    PARTITION P2 VALUES LESS THAN (TO_DATE('20210101','YYYYMMDD')) ,
    PARTITION P3 VALUES LESS THAN (TO_DATE('20210701','YYYYMMDD')) ,
    PARTITION P4 VALUES LESS THAN (TO_DATE('20220101','YYYYMMDD')) ,
    PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
);

🍒 문제 해설

✅ RANGE 파티셔닝 구문

    PARTITION BY RANGE (컬럼명) (
        PARTITION P1 VALUES LESS THAN (컬럼 범위1) ,
        PARTITION P2 VALUES LESS THAN (컬럼 범위2) ,
        PARTITION P3 VALUES LESS THAN (컬럼 범위3) ,
        PARTITION P4 VALUES LESS THAN (컬럼 범위4) , ....
        PARTITION P_MAX VALUES LESS THAN (MAXVALUE)
    );

✍️ 4번 : List 파티션

List 파티션에 대한 설명으로 가장 부적절한 것

  1. 사용자가 지정한 불연속적인 값의 목록으로 데이터를 분할하고자 할 때 사용한다. 👉 ⭕️
  2. 데이터를 시도별로 파티셔닝했다면, 부산 지역에서 대량 변경이나 백업 또는 복구를 진행하는동안 서울 지역에서는 조회, 변경, 삭제를 정상적으로 진행할 수 있다. 👉 ⭕️
  3. 입력 공간을 찾지 못해 에러가 발생하는 현상을 방지하려면 DEFAULT 파티션을 추가해야 한다. 👉 ⭕️
  4. 파티션 기준으로 여러 컬럼을 선택할 수 있다. 👉 ❌

🍋 기출 포인트

  1. List 파티션은 단일 컬럼으로만 파티션할 수 있다.

✍️ 5번 : Hash 파티션

Hash 파티션에 대한 설명으로 가장 부적절한 것

  1. 파티션 기준으로 여러 컬럼을 선택할 수 있다. 👉 ⭕️
  2. 입력 공간을 찾지 못해 에러가 발생하는 현상은 발생하지 않는다. 👉 ⭕️
  3. 파티션 기준 컬럼의 특정 값에 데이터가 많이 몰려 있을 때 이를 분산하도록 옵션을 지정할 수 있다.
    👉 ❌

🍋 기출 포인트

  1. ⭐️파티션 기준을 지정할 때 컬럼명만 설정할 수 있다.⭐️
  2. ⭐️Hash 파티션은 값의 분포가 고르지 않을 때 특정 파티션에 데이터가 올리는 현상이 생길 수 있다.⭐️
  3. Hash 파티션은 이를 해결하는 옵션을 제공하지 않으므로 데이터 분포가 고른 컬럼을
    파티션 키로 선정해야 한다.

🍒 문제 해설

  1. Range, List 파티션은 파티션 기준을 사용자가 직접 지정한다. 그래서 특정 파티션에 데이터가 몰리지 않도록 구성할 수 있다.
  2. Hash 파티션은 DBMS가 정한 해시 알고리즘에 따라 임의로 데이터를 분할한다.
  3. 즉, 파티션 기준을 지정할 때 컬럼명만 설정하는 것이다.

✍️ 6번 : 각 파티션의 장단점

각 파티션의 장단점에 대한 설명으로 가장 부적절한 것

  1. Range 파티션은 (Interval 파티셔닝 기능을 사용하지 않는 한) 주기적으로 신규 파티션을 가하는 관리적인 부담이 있다. 👉 ⭕️
  2. Hash 파티션은 특정 파티션에 데이터가 몰리는 현상이 생길 수 있다. 👉 ⭕️
  3. Hash 파티션에 대한 파티션 Pruning은 '=' 조건 또는 IN-List 조건일 때만 작동한다. 👉 ⭕️
  4. List 파티션에 대한 파티션 Pruning이 BETWEEN 조건일 때는 작동하지 않는다. 👉 ❌

🍋 기출 포인트

  1. List 파티션에 대한 파티션 Pruning은 BETWEEN 조건일 때도 작동한다.

✍️ 7번 : 파티션 세그먼트 갯수 (서브파티션 포함)

읽게 되는 파티션 세그먼트 갯수

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
SUBPARTITION BY HASH(고객ID) SUBPARTITIONS 8
(PARTITION P2020_Q1 VALUES LESS THAN("20200401")
PARTITION P2020_Q2 VALUES LESS THAN("20200701")
PARTITION P2020_Q3 VALUES LESS THAN("20201001")
PARTITION P2020_Q4 VALUES LESS THAN('20210101')
PETITION P2022_Q1 VALUES LESS THAN('20210401')
PARTITION P9999_MX VALUES LESS THAN (MAXVALUE));

SELECT * FROM 주문
WHERE 주문일자 BETWEEN '20200701' AND '20200930';

[내 풀이]

  • P2020_Q2 세그먼트를 먼저 읽게된다.
  • 한 세그먼트 당 8개의 서브 파티션으로 또 나뉘어 진다.
  • 따라서 8개의 세그먼트를 읽는다.

답 : 8

✍️ 8번 : 파티션 세그먼트 갯수 (서브파티션 포함)

읽게 되는 파티션 세그먼트 갯수

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
SUBPARTITION BY HASH(고객ID) SUBPARTITIONS 8
(PARTITION P2020_Q1 VALUES LESS THAN("20200401")
PARTITION P2020_Q2 VALUES LESS THAN("20200701")
PARTITION P2020_Q3 VALUES LESS THAN("20201001")
PARTITION P2020_Q4 VALUES LESS THAN('20210101')
PETITION P2022_Q1 VALUES LESS THAN('20210401')
PARTITION P9999_MX VALUES LESS THAN (MAXVALUE));

SELECT * FROM 주문
WHERE 고객ID =:CUST_ID ;

[내 풀이]

  • 파티션 기준이 조건절에 없으므로 모든 세그먼트를 읽게된다. => 6개
  • 한 세그먼트 당 8개의 서브 파티션으로 또 나뉘어 진다.이 때 고객 ID 기준으로 서브 파티션이 나뉘어진다.
  • 따라서 세그먼트 진입 > CUST_ID로 된 서브 세그먼트 진입으로 총 6개의 세그먼트를 읽는다.

답 : 6

✍️ 9번 : 파티션 세그먼트 갯수 (서브파티션 포함)

읽게 되는 파티션 세그먼트 갯수

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
SUBPARTITION BY HASH(고객ID) SUBPARTITIONS 8
(PARTITION P2020_Q1 VALUES LESS THAN("20200401")
PARTITION P2020_Q2 VALUES LESS THAN("20200701")
PARTITION P2020_Q3 VALUES LESS THAN("20201001")
PARTITION P2020_Q4 VALUES LESS THAN('20210101')
PETITION P2022_Q1 VALUES LESS THAN('20210401')
PARTITION P9999_MX VALUES LESS THAN (MAXVALUE));

SELECT * FROM 주문
WHERE 고객ID =:CUST_ID
AND 주문일자 BETWEEN '20200701' AND '20201231';
  • P2020_Q2,P2020_Q3 세그먼트를 읽게된다. => 2개
  • 한 세그먼트 당 8개의 서브 파티션으로 또 나뉘어 진다.이 때 고객 ID 기준으로 서브 파티션이 나뉘어진다.
  • 따라서 세그먼트 진입 > CUST_ID로 된 서브 세그먼트 진입으로 총 2개의 세그먼트를 읽는다.

답 : 2

✍️ 10번 : 파티션 Pruning

✅ 파티션 Pruning

  • 파티셔닝을 통해 SQL 성능을 향상해 주는 핵심 원리이다.
  • 파티션 Pruning은 SQL 하드파싱이나 실행 시점에 SQL 조건절을 분석해서 읽지 않아도 되는 특이 세그먼트를 액세스 대상에서 제외하는 기능이다.

✍️ 11번 : 파티션 Pruning 종류

아래 실행계획에 나타난 파티션 Pruning 종류

SELECT /*+ LEADING(A) USE_NL(B) */ *
FROM A, B
WHERE A.NO = B.NO;

답 : 동적 파티션 Pruning

✅ 정적(Static) 파티션 Pruning

  • 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동한다.
  • 액세스할 파티션이 쿼리 최적화 시점에 미리 결정된다.
  • 실행계획의 Pstart(partition start)와 Pstop(partition stop) 컬럼에는 액세스할 파티션 번호가 출력된다.

✅ 동적(Dynamic) 파티션 Pruning

  • 파티션 키 컬럼을 바인드 변수로 조회하며 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다.
  • ⭐️실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 실행계획의 Pstart와 Pstop 컬럼에는 "KEY'라고 표시된다.⭐️
  • ⭐️NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.⭐️

🍋 기출 포인트

  1. ⭐️파티션 키 컬럼에 바인드 변수를 사용하거나 NL 조인의 Inner 테이블이 파티셔닝 돼
    있을 때 동적 파티션 Pruning이 작동한다.⭐️
  2. ⭐️실행계획의 Pstart와 Pstop 컬럼에 'KEY'라고 표시되었으므로 동적 파티션 Pruning에 해당한다.⭐️

✍️ 12번 : 파티션 기능과 성능

파티션 기능과 성능에 대한 설명으로 가장 부적절한 것

  1. 가급적 실행계획에 'PARTITION RANGE ALL' 오퍼레이션이 나타나지 않도록 SQL을 작성해야 한다. 👉 ⭕️
  2. 파티션 키 컬럼을 가공하지 않는 것이 좋다. 👉 ⭕️
  3. 정적 Pruning과 동적 Pruning 간에는 성능 차이는 거의 없다. 👉 ⭕️
  4. 파티션 키가 문자형 일자 컬럼일 때, LIKE 조건으로 검색하든 BETWEEN 조건으로 검색하든 성능 차이는 거의 없다. 👉 ❌

🍋 기출 포인트

  1. 파티션 키 컬럼에 LIKE 조건을 사용하면 불필요한 파티션을 읽는 현상이 생길 수 있으므로 읽어야 할 범위를 정확히 명시하는 BETWEEN 조건을 사용하는 것이 좋다.

🍒 문제 해설

  1. 인덱스 컬럼을 가공하면 인덱스 Range Scan이 불가능한 것처럼 파티션 컬럼을 가공하면 파티션 Pruning이 불가능하다.
  2. 동적 Pruning을 하더라도 실행할 때마다 딕셔너리를 창조하면서 읽어야 할 파티션을 결정하는 것은 아니므로 정적 Pruning에 비해 성능 차이는 거의 없다.

✍️ 13번 : 파티션 실행계획

아래 실행계획에서 ㉠과 ㉡에 들어갈 숫자를 적으시오.

CREATE TABLE 주문 (주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), … )
PARTITION BY RANGE (주문일자)
( PARTITION P01 VALUES LESS THAN('20200401')
, PARTITION P02 VALUES LESS THAN('20200701')
, PARTITION P03 VALUES LESS THAN('20201001')
, PARTITION P04 VALUES LESS THAN('20210101')
, PARTITION P05 VALUES LESS THAN('20210401')
, PARTITION PMX VALUES LESS THAN ( MAXVALUE ) 
);

SELECT COUNT(*) FROM 주문
WHERE 주문일자 BETWEEN '20200701' AND '20201231';

[내 풀이]

  • p03 , p04 세그먼트를 읽는다.
  • 정적 파티셔닝이 일어난다. 따라서 파티션 번호가 PStart, Pstop에 적힐 것이다.

답 : 3 , 4

✍️ 14번 : 파티션 세그먼트 갯수

읽게 되는 파티션 세그먼트 갯수

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
SUBPARTITION BY HASH(고객ID) SUBPARTITIONS 8
(PARTITION P2020_Q1 VALUES LESS THAN("20200401")
PARTITION P2020_Q2 VALUES LESS THAN("20200701")
PARTITION P2020_Q3 VALUES LESS THAN("20201001")
PARTITION P2020_Q4 VALUES LESS THAN('20210101')
PETITION P2022_Q1 VALUES LESS THAN('20210401')
PARTITION P9999_MX VALUES LESS THAN (MAXVALUE));

SELECT * FROM 주문
WHERE 주문일자 BETWEEN 20201001 AND 20201231;

[내 풀이]

  • 조건절이 String형이었다면 P2020_Q4 세그먼트를 읽게된다. => 2개
  • 하지만 조건절에 있는 변수 타입은 숫자이고 우선순위는 숫자 , String형이므로 결국 파티션 Pruning은 일어나지 않는다.
    • 아래와 같이 자동 형변환이 발생한다.
      			WHERE TO_NUMBER(주문일자) BETWEEN 20261001 AND 28201231
  • 따라서 모든 세그먼트를 읽게된다.

답 : 6

✍️ 15번 : 조회되는 파티셔닝 적기

조회할 때 읽게 되는 파티션명을 나열하시오.

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
(PARTITION P1 VALUES LESS THAN("20210201")
, PARTITION P2 VALUES LESS THAN("20210301")
, PARTITION P3 VALUES LESS THAN("20210401")
, PARTITION P4 VALUES LESS THAN("20210501")
, PARTITION P5 VALUES LESS THAN("20210601")
PARTITION PMX VALUES LESS THAN (MAXVALUE));

SELECT * FROM 주문
WHERE 주문일자 LIKE '20203%' ;

[내 풀이]

  • LIKE '20203%' 에는 '20200300' 따위의 데이터도 스캔하므로 따라서 파티션 P2도 읽히게 된다.
  • 따라서 읽히게 되는 파티션은 P2 , P3이다.

답 : P2 , P3

✍️ 16번 : Local 파티션 인덱스

Local 파티션 인덱스의 장점이 아닌 것

  1. 파티션 Pruning에 의해 단일 파티션만 액세스할 때, 비파티션 인덱스보다 블록 I/O가 적게 발생한다. 👉 ⭕️
  2. 테이블 파티션을 재구성할 때 인덱스 unusable 상태로 변하지 않는다. 👉 ⭕️
  3. Global 인덱스보다 관리의 편의성이 높다. 👉 ⭕️
  4. 인덱스 키와 파티션 키를 독립적으로 자유롭게 선택할 수 있다. 👉 ❌

🍋 기출 포인트

  1. ⭐️테이블 파티션을 재구성(ADD/DROP/SPLIT/EXCHANGE 등)할 때 비파티션 인덱스와 Global 파티션 인덱스는 Unusable 상태로 변한다.⭐️
  2. ⭐️반면, Local 파티션 인덱스는 Unusable 상태로 변하지 않으며, 파티션도 자동으로 재구성되므로 관리의 편의성이 좋다.⭐️
  3. Local 인덱스는 테이블 파티션별로 인덱스를 생성하므로 비파티션 인덱스보다 크기가 작다.루트에서 리프까지의 Depth가 낮기 때문에 인덱스를 탐색할 때 더 적은 블록을 읽는다.

✍️ 17번 : 인덱스의 파티션 유형

주문 X1 인덱스의 파티션 유형을 고르시오.

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일자 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일자)
(PARTITION P2020_Q1 VALUES LESS THAN("20200401")
, PARTITION P2020_Q2 VALUES LESS THAN("20200701")
, PARTITION P2020_Q3 VALUES LESS THAN("20201001")
, PARTITION P2020_Q4 VALUES LESS THAN('20210101')
, PETITION P2022_Q1 VALUES LESS THAN('20210401')
, PARTITION P9999_MX VALUES LESS THAN (MAXVALUE));

CREATE INDEX 주문_X1 ON 주문 (고객 ID) LOCAL ;
  1. Local NonPrefixed 파티션 인덱스 👉 ⭕️
  2. Local Prefixed 파티션 인덱스 👉 ❌
  3. Global Prefixed 파티션 인덱스 👉 ❌
  4. Global NonPrefixed 파티션 인덱스 👉 ❌

🍒 문제 해설

✅ Local 파티션 인덱스

  • Local 파티션 인덱스는 테이블 파티션 속성을 그대로 상속받는다.
  • 따라서 테이블 파티션 키가 주문일시면 인덱스 파티션 키도 주문일시가 된다.
  • Local 파티션 인덱스를 'Local 인덱스'라고 줄여서 부르기도 한다.
  • ⭐️Local 파티션 인덱스는 테이블과 정확히 1:1 대응 관계를 갖도록 오라클이 파티션을 자동으로 관리해 준다.⭐️
  • ⭐️테이블 파티션 구성을 변경(add, drop, exchange 등)하더라도 인덱스를 재생성할 필요가 없다.⭐️
  • 변경작업이 순식간에 끝나므로 피크(peak) 시간대만 피하면 서비스를 중단하지 않고도 작업할 수 있다.
  • Local 파티션 인덱스의 장점은 이처럼 관리 편의성에 있다.

✍️ 18번 : 인덱스의 파티션 유형

주문 X1 인덱스의 파티션 유형을 고르시오.

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일시 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일시)
(PARTITION Q1 VALUES LESS THAN (TO DATE('20200401", "YYYYMMDD"))
, PARTITION Q2 VALUES LESS THAN (TO DATE('20200701", "YYYYMMDD"))
, PARTITION Q3 VALUES LESS THAN (TO DATE('20201001", "YYYYMMDD"))
, PARTITION Q4 VALUES LESS THAN (TO DATE('20210101", "YYYYMMDD"))
, PARTITION QMAX VALUES LESS THAN ( MAXVALUE ) );

CREATE INDEX 주문_X1 ON 주문(주문일시, 고객ID) GLOBAL
PARTITION BY RANGE (주문일시)
(PARTITION P2020 VALUES LESS THAN( TO DATE('20200701','YYYMMDD'))
, PARTITION P2021 VALUES LESS THAN( TO DATE('20210101','YYYMMDD'))
, PARTITION P9999 VALUES LESS THAN( MAXVALUE));
  1. Global Prefixed 파티션 인덱스 👉 ⭕️
  2. Local Prefixed 파티션 인덱스 👉 ❌
  3. Local NonPrefixed 파티션 인덱스 👉 ❌
  4. Global NonPrefixed 파티션 인덱스 👉 ❌

🍋 기출 포인트

  1. 인덱스 파티션 키가 인덱스 선두 컬럼이므로 Prefixed 파티션 인덱스다.

🍒 문제 해설

  1. Global NonPrefixed 파티션 인덱스는 이론상으로만 존재하며, 실제 생성은 불가능하다.

✅ Global 파티션 인덱스

  • Global 파티션 인덱스는 파티션을 테이블과 다르게 구성한 인덱스다.
  • 구체적으로, 파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른 경우다.
  • 비파티션 테이블이어도 인덱스는 파티셔닝할 수 있다.
  • ⭐️글로벌 파티션 인덱스는 테이블 파티션 구성을 변경(DROP, EXCHANGE, SPLIT 등)하는 순간 Unusable 상태로 바뀌므로 곧바로 인덱스를 재생성해 줘야 한다.⭐️
  • 그동안 해당 테이블을 사용하는 서비스를 중단해야 한다.

✍️ 19번 : 인덱스의 파티션 유형

주문 X1 인덱스의 파티션 유형을 고르시오

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일시 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일시)
(PARTITION Q1 VALUES LESS THAN (TO DATE('20200401", "YYYYMMDD"))
, PARTITION Q2 VALUES LESS THAN (TO DATE('20200701", "YYYYMMDD"))
, PARTITION Q3 VALUES LESS THAN (TO DATE('20201001", "YYYYMMDD"))
, PARTITION Q4 VALUES LESS THAN (TO DATE('20210101", "YYYYMMDD"))
, PARTITION QMAX VALUES LESS THAN ( MAXVALUE ) );

CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일시 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일시) GLOBAL
(PARTITION Q1 VALUES LESS THAN (TO DATE('20200401", "YYYYMMDD"))
, PARTITION Q2 VALUES LESS THAN (TO DATE('20200701", "YYYYMMDD"))
, PARTITION Q3 VALUES LESS THAN (TO DATE('20201001", "YYYYMMDD"))
, PARTITION Q4 VALUES LESS THAN (TO DATE('20210101", "YYYYMMDD"))
, PARTITION QMAX VALUES LESS THAN ( MAXVALUE ) );
  1. Global Prefixed 파티션 인덱스 👉 ⭕️
  2. Local Prefixed 파티션 인덱스 👉 ❌
  3. Local NonPrefixed 파티션 인덱스 👉 ❌
  4. Global NonPrefixed 파티션 인덱스 👉 ❌

🍋 기출 포인트

  1. ⭐️인덱스를 테이블과 정확히 1:1 관계가 되도록 파티션하더라도 로컬 파티션은 아니다. 오라클이 인덱스 파티션을 자동으로 관리해 주지 않기 때문이다.⭐️
  2. 인덱스 파티션 키가 인덱스 선두 컬럼이므로 Prifixed 파티션 인덱스다.

🍒 문제 해설

  1. Global 파티션 인덱스는 Prefixed만 지원한다. 만약 주문_X1 인덱스를 고객ID + 주문일시」 순으로 구성하거나 고객ID 단일 컬럼으로 구성하려고 한다면, ORA-14838 에러가 발생할 것이다.

✍️ 20번 : 인덱스의 파티션 유형

아래 주문_PK 인덱스의 파티션 유형을 고르시오.

  CREATE TABLE 주문 ( 주문번호 NUMBER, 주문일시 VARCHAR2(8), 고객ID VARCHAR2(5), - )
PARTITION BY RANGE (주문일시)
(PARTITION Q1 VALUES LESS THAN (TO DATE('20200401", "YYYYMMDD"))
, PARTITION Q2 VALUES LESS THAN (TO DATE('20200701", "YYYYMMDD"))
, PARTITION Q3 VALUES LESS THAN (TO DATE('20201001", "YYYYMMDD"))
, PARTITION Q4 VALUES LESS THAN (TO DATE('20210101", "YYYYMMDD"))
, PARTITION QMAX VALUES LESS THAN ( MAXVALUE ) );

CREATE UNIQUE INDEX 주문_PK ON 주문 (주문번호);
ALTER TABLE 주문 ADD CONSTRAINT 주문_PK PRIMARY KEY(주문번호) USING INDEX 주문_PK;
  1. 비파티션(NonPartitioned) 인덱스 👉 ⭕️
  2. Local Prefixed 파티션 인덱스 👉 ❌
  3. Local NonPrefixed 파티션 인덱스 👉 ❌
  4. Global Prefixed 파티션 인덱스 👉 ❌

🍒 문제 해설

  1. "Unique 인덱스를 파티셔닝하려면, 파티션 키가 모두 인덱스 구성 컬럼이어야 한다"는 제약이 존재한다.
  2. Unique 인덱스인 주문_PK를 파티셔닝하려면 구성 컬럼인 주문번호로 파티셔닝하는 수밖에 없다.

✍️ 21번 : 가장 적합한 인덱스 파티션 유형

아래 튜닝 대상 SOL을 위한 최적 인덱스를 구성하고자 할 때 가장 적합한 파티션 유형을 고르시오.

[테이블 구성 |
CREATE TABLE 거래
( 거래일자 VARCHAR2(8)
, 계좌번호 NUMBER
, 주문매체코드 VARCHAR2(2)
, 거래유형코드 VARCHAR2(4)
, 거래량 NUMBER
, 거래금액 NUMBER )

PARTITION BY RANGE (거래일자)
(PARTITION P2021_M01 VALUES LESS THAN ('20210201')
, PARTITION P2021_M02 VALUES LESS THAN ('20210301')
, ...
, ... 
, PARTITION P9999_MX VALUES LESS THAN(MAXVALUE ) );

[튜닝 대상 SQL]
SELECT SUM(거래량), SUM(거래금액)
FROM 거래
WHERE 계좌번호 = :ACNT_NO
AND 거래일자 >= TO_CHAR(ADD_MONTHS(SYSDATE, -2), 'YYYYMMDD');

[내 풀이]

  • 거래일자 조건절을 파티셔닝 조건절로 두고
  • 인덱스의 선두컬럼은 계좌번호로 두도록 하는 인덱스를 만든다. (인덱스 구성은 계좌번호+거래일자)
  • 즉, 로컬 NonPrefixed 인덱스 파티셔닝을 하면 될듯하다 !

답 : Local NonPrefixed 파티션 인덱스

✍️ 22번 : 가장 적합한 인덱스 파티션 유형

아래 튜닝 대상 SOL을 위한 최적 인덱스를 구성하고자 할 때 가장 적합한 파티션 유형을 고르시오.

[테이블 구성 |
CREATE TABLE 거래
( 거래일자 VARCHAR2(8)
, 계좌번호 NUMBER
, 주문매체코드 VARCHAR2(2)
, 거래유형코드 VARCHAR2(4)
, 거래량 NUMBER
, 거래금액 NUMBER )

PARTITION BY RANGE (거래일자)
(PARTITION P2021_M01 VALUES LESS THAN ('20210201')
, PARTITION P2021_M02 VALUES LESS THAN ('20210301')
, ...
, ... 
, PARTITION P9999_MX VALUES LESS THAN(MAXVALUE ) );

[튜닝 대상 SQL]
SELECT SUM(거래량), SUM(거래금액)
FROM 거래
WHERE 계좌번호 = :ACNT_NO;

[내 풀이]

  • 인덱스의 선두컬럼은 계좌번호로 두도록 하는 인덱스를 만든다.
  • 인덱스는 굳이 파티셔닝을 할 필요는 없으니 비파티셔닝 인덱스로 하면 될듯 !

답 : 비파티션(NonPartitioned) 인덱스

✍️ 23번 : 가장 적합한 인덱스 파티션 유형

아래 SQL 튜닝하기 위해 거래 테이블에 인덱스를 생성하고자 할 때 성능 측면에서 가장 최적인 것

을 고르시오

[테이블 구성 |
CREATE TABLE 거래
( 입금일자 VARCHAR2(8)
, 거래일시 DATE
, 계좌번호 NUMBER
, 주문매체코드 VARCHAR2(2)
, 거래유형코드 VARCHAR2(4)
, 거래량 NUMBER
, 거래금액 NUMBER )

PARTITION BY RANGE (거래일시)
(PARTITION Q1 VALUES LESS THAN (TO DATE('20200201", "YYYYMMDD"))
, PARTITION Q2 VALUES LESS THAN (TO DATE('20200301", "YYYYMMDD"))
, ...
, ... 
, PARTITION P9999_MX VALUES LESS THAN(MAXVALUE ) );

CREATE UNIQUE INDEX 거래_PK ON 거래 (계좌번호, 거래일시) LOCAL;

ALTER TABLE 거래 ADD
CONSTRAINT 거래_PK PRIMARY KEY(계좌번호, 거래일시) USING INDEX 거래_PK;

[튜닝 대상 SQL ]
SELECT /*+ ORDERED USE_NL(B) */ *
FROM 계좌 A, 거래 B
WHERE A. 지점코드 = :BRCH_CD
AND B. 계좌번호 = A. 계좌번호
AND B. 입금일자 = :PAY_DT ;

[내 풀이]

  • 인덱스의 구성은 계좌번호로 두도록 하는 인덱스를 만든다.
  • 입금일자와 거래일시가 비슷하지 않을까 ? 입금일자로 파티셔닝이 가능한 인덱스를 만들어야할듯 > 근데 오라클에선 Global NonPrefixed 인덱스가 지원되지 않는다.
  • 따라서 비파티셔닝 인덱스가 맞는듯?

🍒 문제 해설

  1. ⭐️인덱스를 파티셔닝하지 않으면 계좌 테이블에서 읽은 계좌번호별로 인덱스를 한 번만 스캔하므로 성능 측면에서 가장 최적이다.⭐️
  2. Local NonPrefixed 파티션 인덱스를 생성하려면 인덱스 키를 계좌번호 + 입금일자로
    구성해야 하는데, 파티션 키인 거래일시가 조건절에 없으므로 전체 인덱스 파티션을 스캔하게 된다.

답 : 비파티션(NonPartitioned) 인덱스

profile
Yu River
도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’
이전 포스트

[SQLP필기문제]6장 고급SQL튜닝(4) 파티셔닝

0개의 댓글