🗓 22-08-29

SQLP 이론 복습

(1) 고도화

SQLP 실기 풀이

(1) [SQLP실기풀이]6장 고급SQL튜닝(6) 고급 SQL 활용 55번
(2) [SQLP실기풀이]6장 고급SQL튜닝(6) 고급 SQL 활용 56번
(3) [SQLP실기풀이]6장 고급SQL튜닝 (6) 고급 SQL 활용 59번

SQLP 필기 풀이

(1) [SQLD필기풀이]1장 SQL기본 1~64번
p.40 ~ p.74
(2) [SQLP필기풀이]6장 고급SQL튜닝(5) 대용량 배치 프로그램 튜닝

👀 8/29 복기

[1] 데이터 복제 + group by + rollup구현(decode)

55번

• 꼭 그룹바이절에 있는 컬럼명만을 select로 가져와야 하는 건 아니다.
  • 그룹핑해서 하나의 행으로 담아내서 출력할수만 있다면 다 되는듯 ?

  SELECT DEPTNO , DECODE(LVL, 1, '부서계' ,2 ,EMPNO) AS 사원번호
  , SUM(SAL) 급여합 , ROUND(AVG(SAL)) 급여평균
  FROM EMP , (
      SELECT LEVEL AS LVL
    FROM DUAL
    WHERE CONNECT BY LEVEL <=2
      ) T1
  GROUP BY DEPNO , LVL , DECODE(LVL, 2, EMPNO)
  ORDER BY 1, 2;

[2] 아우터조인 누락 + group by로 한 행으로 만들기

57번

• 아우터 조인을 누락하지 말자
• 여러 행을 한 행으로 만들기 위해서는 group by와 case when (또는 DECODE) 문을 적절히 활용하자

  SELECT 고객번호 , MIN(A.고객명) 고객명
  , MIN(DECODE(B.연락처구분코드 , 'HOM', B.연락처번호 )) AS 집 
  , MIN(DECODE(B.연락처구분코드 , 'OFC', B.연락처번호 )) AS 사무실
  , MIN(DECODE(B.연락처구분코드 , 'MBL', B.연락처번호 )) AS 휴대폰 
  FROM 고객 A , 고객연락처 B
  WHERE A.고객구분코드 = 'VIP'
  AND A.고객번호 = B.고객번호(+)
  GROUP BY 고객번호;

[3] 59번

59번

• 트랜잭션 고립성 설정하는 명령어

	set transaction isolation level serializable;

[4] 배치 프로그램 특징

배치 프로그램 특징

• 27번 : 배치 프로그램은 항상 전체 처리속도 최적화에 목표를 두고 튜닝해야 한다.

[5] 병렬처리 특징

병렬 처리 특징

• 29번 : 병렬 처리에서 QC는 다른 병렬 서버들이 작업 완료하기를 기다리면서 대기 중인 병렬 프로세스를 찾아 자원을 OS에 반환하지는 않는다.
• 29번 : 병렬 처리에 사용한 병렬 프로세스는 모든 처리를 종료(SELECT 문장은 커서를 닫거나 결과 집합을 모두 Fetch)한 후에 일괄 해제하고 자원을 OS(또는 서버 풀)에 반환한다.
• 37번 : 병렬로 처리하는 중간 단계에 PARALLEL_TO_SERIAL 오퍼레이션이 나타나면 병목 구간으로 작용한다.
• 37번 : 병렬로 처리하는 과정에 큰 테이블을 단일 프로세스로 읽으면 병목 구간이 될 수 있다.(큰 테이블을 PARALLEL_FROM_SERIAL 방식으로 읽는지 확인)
• 49번 : 파티션 인덱스가 아니면, Index Range Scan, Index Full Scan은 병렬 처리가 불가능하다.
  • ⭐️Index Range Scan, Index Full Scan은 파티션 Granule로 스캔되므로 병렬처리할 때 파티션 인덱스이어야 병렬 처리가 가능하고 파티션 인덱스가 아니면 병렬 처리가 불가능하다.⭐️
• 51번 : Unique 처리를 하는데 이 과정에서 병목현상이 생긴다.
  👉 ROWNUM이 아닌 ROW_NUMBER()을 사용해 데이터 크기로 정렬해 일련번호를 부여하면 QC가 Unique 처리를 하는 과정을 생략할 수 있다.(다만 데이터 재분배는 필요하다.)

[6] 데이터 재분배 특징

데이터 재분배 특징

33번

• P - P(PARALLELTO_PARALLEL) 오퍼레이션은 데이터를 재분배(redistribution)하는 오퍼레이션이다.
• 데이터 재분배 과정에 테이블 큐(Queue)를 사용한다.
• ⭐️병렬 프로세스 간 통신이 발생하므로 Inter-Operation Parallelism에 속
  한다.⭐️
• 서버 프로세스가 병렬도(DOP)의 2배수로 생성된다.

38번

• 병렬 Order By 및 병렬 Group By
• Broadcast 방식의 데이터 분배
• Partial Partition Wise 조인

[7] FULL PARTITION WISE JOIN 특징

FULL PARTITION WISE JOIN 특징

38번

• ⭐️Full Partition Wise 조인은 병렬 프로세스를 2배수로 할당하지 않는다.
  같은 기준으로 파티션된 두 테이블을 조인할 때 사용하는 병렬 조인 방식이다.
  양쪽 테이블을 같은 기준으로 파티션하기 위해 데이터를 재분배할 필요가 없다.
  즉 , 하나의 서버집합만 필요하다.⭐️

45번

• ⭐️HASH JOIN 실행계획 위에 PX PARTITION RANGE ALL이 있는 경우에는 이미 조인 후에 파티셔닝을 한 것으로 FULL PARTITION WISE JOIN이 일어난것이다.⭐️
• FULL PARTITION WISE JOIN의 힌트는 PQ_DISTRIBUTE(T1, NONE , NONE)이다.
• FULL PARTITION WISE JOIN은 파티션 개수보다 작거나 같은 개수의 DOP를 지정해야 한다.

[8] PARTIAL PARTITION WISE JOIN 특징

PARTIAL PARTITION WISE JOIN 특징

47번

• PARTIAL PARTITION WISE JOIN의 힌트는 PQ_DISTRIBUTE(T1, PARTITION , NONE) 형태 이다.

[8] 파티션 Granule

파티션 Granule

34 , 35번

• 파티션 되어있는 인덱스를 병렬로 스캔하는 것은 파티션 기반 Granule이다.
• 파티션 기반 Granule에서는 파티션 개수보다 많은 병렬도를 지정하면 서버 리소스를 낭비하게 된다.
• ⭐️블록 기반 Granule은 파티션 여부, 파티션 개수와 무관하게 병렬도를 지정할 수 있다.⭐️
• ⭐️병렬 Full Table Scan은 블록 기반 Granule이므로 파티션 개수보다 큰 병렬도를 지정해도 상관없다.⭐️

[9] 병렬처리 힌트

병렬처리 힌트

41번 , 43번

• 작동 순서
  • 상품 테이블 Full Scan
    • 블록 단위 Granule 2개
    • 브로드캐스트 방식으로 데이터 재분배
    • LEADING(A) FULL(A) PARALLEL(A 2)
  • 상품 테이블의 데이터를 브로드 캐스트 방식으로 두번째 서버 집합이 받음
    • PQ_DISTRIBUTE(B,BROADCAST,NONE)
  • 주문상품 테이블 Full Scan
    • 블록 단위 Granule 2개
    • FULL(B) PARALLEL(B 2)
  • 각 서버집합에서 상품과 주문 테이블 해시 조인
    • USE_HASH(B)
  • 조인된 결과를 QC로 보냄

답 :

/*+ LEADING(A) USE HASH(B)
FULL(A) FULL(B) PARALLEL(A 2) PARALLEL(B 2)
PO_DISTRIBUTE(B, BROADCAST, NONE) */

52번

• update 구문 : update Table1 set COl1 = 1 WHERE COL2='b';
• update 병렬 구문 : 52번

ALTER SESSION ENABLE PARALLEL DML;

SQL > MERGE INTO 주문 T1
USING ( SELECT 고객번호 , 주문순번
		,ROW_NUMBER() OVER ( ORDER BY 고객번호 , 주문순번 ) AS 주문일련번호
		FROM 주문
		WHERE 주문일자 = TO_CHAR(SYSDATE, 'YYYYMMDD') ) T2
ON ( T1.주문일자 = TO_CHAR(SYSDATE, 'YYYYMMDD')
	AND T1.고객번호 = T2.고객번호
    AND T1.주문순번 = T2.주문순번 )
WHEN MATCHED THEN UPDATE
	SET T1.주문일련번호 = T2.주문일련번호;

[SQLD]

p.40

• DCL : GRANT , REVOKE
• TCL : COMMIT , ROLLBACK (DCL로 분류하기도한다.)

p.42

CONSTRAINT 문법

CREATE TABLE T1 ( .. , CONSTRAINT T1_PK PRIMARY KEY (PROD_ID)); 

p.43

ALTER TABLE 문법 (sql server)

• 여러 컬럼 동시 수정 불가능
• 괄호 안에서 수정하는 거 불가능 (따로따로해야함)
• not null은 변화여부 상관없이 무조건 지정해야함

ALTER TABLE T1 **ALTER COLUMM** 분류명 VARCHAR(30) NOT NULL;
ALTER TABLE T1 **ALTER COLUMM** 등록일자 DATE NOT NULL;

p.47

ALTER TABLE T1 DROP COLUMN COL1; (oracle)

p.48

테이블명 변경 : RENAME T1 TO T2;

p.49

(1) 참조 동작

Delete

1. Cascade : 같이 삭제
2. Set null
3. Set Default
4. Restrict : 자식에 없을 때만 삭제 가능
5. No Action

insert

1. Automatic : 마스터에 자동 생성
2. Set null
3. Set Default
4. Dependent : 마스터에 있을 때만 insert 가능
5. No Action

(2) INSERT 문법

• INSERT INTO T1 VALUES( a,b,c).. : 빠짐없이 넣어야함
• INSERT INTO T1 (COL1,COL2) VALUES (a,b); : 선택해서 넣을 수 있음

p.50

22번

• delete 참조 동작으로 set null을 설정했지만 해당 컬럼이 not null이면 삭제가 되지 않는다.

p.54

SAVEPOINT P1; ROLLBACK TO P1; (oracle)

SAVE TRANSACTION P1; ROLLBACK TRANSACTION P1;(sql server)

p.56

37번

• primary key는 모든 컬럼에 입력되어있어야한다.
• sql server는 ''로 입력된 값은 is null 로 비교할 수없다. ( = '' 로 비교함)

p.59

40번

• 1:M 관계의 두 테이블을 조인할 경우 M쪽의 다중행이 출력되므로 단일행 함수는 사용할 수 없다.
  => 사용할 수 있다. M쪽 행에 하나씩 단일행 함수를 적용해주면 된다.

p.61

단일행함수 문법

• COALESCE : 커미션을 1차 선택값으로, 급여를 2차 선택값으로 선택하되 두 칼럼 모두 NULL인 경우는 NULL로 표시 / 인수 숫자 한정없음

SELECT ENAME, COMM, SAL, COALESCE(COMM, SAL) COAL FROM EMP;

• SUBSTR : 첫번째 글자의 index는 1이다.

SELECT SUBSTR('안녕하세요',3) FROM DUAL; -- 하세요
SELECT SUBSTR('안녕하세요',3,2) FROM DUAL; -- 하세

• NULLIF : COL1 이 'A'이면 COL1을 널값으로 표시한다.

SELECT NULLIF(COL1,'A')  FROM TBL_A;

p.63

45번

• sql server에서는 ''값이 널값이 아니다.따라서 카운트하면 얘도 같이 카운팅된다.
• sql server에서는 nvl을 isnull로 쓴다.