[DB] GROUP BY, SUB QUERY, 옵티마이저, 힌트

GROUP BY

• 그룹화 - 지정한 컬럼값이 같을 것을 갖는 행에 대해 집계 함수를 실행
  • GROUP BY: 공통적인 걸 묶음
    • GROUP BY 1 등 숫자를 적을경우 컬럼들의 인덱스로 판단 1번째 컬럼기준 집합
    • SELECT에서는 GROUP BY로 묶을 것들을 적어야함
    • 집계함수는 WHERE 절에서는 쓸 수 없고 HAVING절에서 사용..(SELECT도 가능)
      - WHERE 절에서 가능한 조건 다 처리한 후에 HAVING 사용 ->효율적
      - 코드

              sql
              PLAYER 테이블에서 평균 키보다 작은 선수 검색 시
              SELECT * FROM PLAYER
              WHERE HEIGHT < (SELECT AVG(HEIGHT) FROM PLAYER); ->SUB 쿼리문 사용하여 WHERE에 적용
              
              
              
              포지션별 평균키와 전체평균키
              SELECT "POSIION", AVG(HEIGHT),(SELECT AVG(HEIGHT) FROM PLAYER) FROM PLAYER
              GROUP BY "POSITION";
              
              -- 깔끔하게 정리하기 CASE문 변경
              SELECT
              AVG(CASE "POSITION" WHEN 'GK' THEN HEIGHT END) AS GK,
              AVG(CASE "POSITION" WHEN 'DF' THEN HEIGHT END) AS DF,
              AVG(CASE "POSITION" WHEN 'FW' THEN HEIGHT END) AS FW,
              AVG(CASE "POSITION" WHEN 'MF' THEN HEIGHT END) AS MF,
              AVG(HEIGHT) AS "전체 평균"
              FROM PLAYER;
              
              ROUND(AVG(CASE "POSITION" WHEN 'GK' THEN HEIGHT END),2) AS GK ->소숫점두째자리까지만 나오도록.. ROUND
              

      from 테이블이름
      [WHERE] 그룹화하기 이전의 조건식
      GROUP BY 그룹화에 사용할 컬럼명   ->뭉칠 중심이 될 컬럼
      [HAVING] 그룹화 후에 그룹화 결과값을 사용하는 조건식
      [ORDER BY] 정렬 컬럼

    • 순서 중요!! []는 생략가능
    • DISTINCT 중복제거

      SELECT DISTINCT  CATEGORY
      FROM  TBL_PRODUCT
      ORDER  BY CATEGORY ASC;

SUB QUERY

• SELECT 조회 결과(여러개의 행)가 다른 DML 명령어에 쓰일 수 있음

• 조건이 2개일때 ()안에 한개의 조건을 먼저 넣고, FROM의 테이블로 만듬. 그 이후 WHERE 절 사용

  • FROM절: IN LINE VIEW
  • SELECT절: SCALAR
  • WHERE절: SUB QUERY

• SELECT 서브 쿼리는 JOIN으로 대체할 수 있음 (때에 따라..)

-서브쿼리 - 실행할때마다 SELECT 조회부터 시작하기에 검색 횟수가 많음
SELECT TP.*
FROM TBL_PRODUCT tp
WHERE PCODE IN	                                          --2) 조건식 연산
--('CJBAb12g','APLE5kg','DOWON123a');
(SELECT PCODE FROM TBL_BUY tb WHERE CUSTOMID = 'twice');	--1) 조건식 연산

• WHERE, SELECT와 함께 쓰이는 서브쿼리 코드

  1) WHERE에서 쓰는 서브쿼리: TWICE가 구매한 상품 정보
  SELECT TP.*                         --> 검색하고서 조건에서 걸러냄
  FROM TBL_BUY tb , TBL_PRODUCT tp
  WHERE TP.PCODE =TB.PCODE        --일치한 것만 찾아냄으로써 중복을 없앰 --1) 크로스 연산
  AND TB.CUSTOMID = 'twice';	                                          --2) 조건식 연산

  2) SELECT와 함께 쓰이는 서브쿼리
  SELECT 컬럼1, 컬럼2 FROM (SELECT...)
  WHERE 컬럼명 1= 값(SELECT 결과)

TP. ->테이블. 가능

옵티마이저와 힌트

• OPTIMIZER ->EXECUTION PLAN에서 볼 수 있음
  • COST : 예상 수행 시간, 쿼리 수행 시간(클수록 안좋음)
  • CARDINALITY: 실행 결과의 건수
• SQL 을 가장 빠르고 효율적으로 수행할 최적의 처리경로(최저비용)를 생성해주는 DBMS 내부의 핵심 엔진.
  사용자가 쿼리문(SQL)으로 결과를 요청하면, 이를 생성하는 데 필요한 처리경로는 DBMS에 내장된 옵티마이저가 자동으로 생성
  옵티마이저가 생성한 SQL처리 경로를 실행계획(EXECUTION PLAN)이라고 함
• 최적화 과정
  • 사용자가 작성한 쿼리 수행을 위해, 실행될만한 계획을 찾음
  • 데이터 딕셔너리에 미리 수집해놓은 오브젝트 통계 및 시스템 통계 정보를 이용해 각 실행 계획의 예상 비용 산정
  • 각 실행계획을 비교, 최저 비용을 갖는 하나를 선택하여 실행
• 종류
  1. 규칙기반 옵티마이저(RBO), 휴리스틱(HEURISTIC) 옵티마이저
     미리정해진 규칙에 따라 실행, ORCLE 10G 버전 부터는 RBO에 대한 지원 중단
  2. 비용기반 옵티마이저(CBO)
     비용이 가장 낮은 실행계획을 선택

PARSER OPTIMIZER ROW_SOURCE SQL_ENGINE
GENERATOR

문법오류검사, 총비용계산 실행가능 코드로 변경 실행
코드로 변경 실행계획 생성

• SQL 실행순서
  • FROM- WHERE - GROUP BY- HAVING -SELECT - ORDER BY
• 힌트(HINT): /+HINT/
  • SELECT문에 실행하고 싶은 계획을 전달할 때 사용되는 문법
  • 잘못 작성되어도 실행할때는 무시되며 별도의 오류는 발생X
  • /+로 시작, /로 마침. 또한 뒤에 컬럼명을 작성할 때 , 를 사용하지 않음 (/+HINT/ PLAYER)
  • 코드

    SELECT /*+ INDEX(테이블명 PK명)*/ 컬럼명1, 컬럼명2 ... FROM 테이블명
    -> PK에는 자동으로 인덱스가 생성되어 있음
    ->일반적인 컬럼은 ORDER BY로 정렬
    
    SELECT /*+ INDEX_DESC(PLAYER PLAYER_PK) */* FROM PLAYER;