LITTLE BY LITTLE

SQLD 2과목 (2. SQL활용, 3.SQL최적화 원리) 본문

SQL/SQLD

SQLD 2과목 (2. SQL활용, 3.SQL최적화 원리)

위나 2022. 9. 3. 12:16

2-2. SQL 활용

2-2-1. 표준 JOIN

  1. SQL에서 Union은 Union, Intersect는 Intersection, Except는 Difference(차집합), <oracle>에서는 Minus로 표현, Cross join은 Product를 의미
  2. 순수 관계연산자 natural join은 다양한 join으로 기능 구현이 가능하고, divide는 현재 사용하지 않는다.
  3. 3개이상 JOIN하기
    1. Where 사용하고 AND AND AND...(WHERE절 이용)
    2. Join 사용하고 ON ON ON ... (FROM JOIN)
  4. 위에서 2번 방식인, FROM 절에 JOIN 하는 방법 (6가지)
    1. Inner Join 
      1. 내부조인, Equi 조인, 동등조인이라 한다.
      2. = 연산자로 조인
      3. Join 조건에서 '동일한 값이 있는 행만' 반환
      4. Cross Join, Outer Join과 함께 사용 X
      5. Using 조건절이나, On 조건절 필수 사용
      6. 중복 테이블의 경우, 별개의 컬럼으로 표시됨
      7. 내부 조인 기본 형태 => From ~ Where / From A Inner Join B On ~ / From A Join B On ~
    2. Natural Join
      1. 두 테이블간 동일한 이름을 갖는 모든 컬럼에 대해 Equi Join 수행
      2. Using이나 On, Where절에서 Join 조건을 정의하지 X
      3. Alias나 접두사 붙일 수 X
    3. Using 조건절
      1. From절에 이용해서 같은 이름 컬럼 중 원하는 컬럼만 Equal Join할 수 있다.
      2. <sql server>에는 지원 X
      3. Alias나 접두사 붙일 수 X
      4. Join에 사용되는 컬럼을 1개만 표시
      5. ex. Using Dept no; <= ON Dept_Dept no = Dept_temp.deptno; (using절에서 on처럼 컬럼 여러개 쓰지X
    4. On 조건절
      1. 컬럼명이 달라도 조인할 수 있다.
      2. Alias나 접두사는 "반드시" 붙여야한다.
    5. Cross Join (=카타시안 곱=Cross Product)
      1. Join하려할 때 적절한 Join 컬럼이 없는 경우 사용
      2. 생길 수 있는 모든 데이터 조합 출력
      3. 결과는 양쪽 집합의 M*N 건의 데이터 조합 발생
      4. 테이블 두 개를 쉼표로 이어서 JOIN 한다.
    6. Outer Join
      1. Join 조건에서 동일한 값이 없는 행도(=Null 값도) 출력한다.
      2. Using 조건절이나, On 조건절 필수 사용
      3. in, on 연산자 사용시 에러 발생
      4. 표시가 누락된 컬럼이 있을 경우 outer join 에러 발생
      5. Full Outer Join은 미지원, 주로 Standard Join을 사용함
      6. Left Outer Join (Right Outer Join)
        1. 좌측 테이블에서 먼저 데이터를 읽고, 우측 테이블에서 Join 대상을 읽음
        2. 좌측 Table 기준이며, 'Outer' 키워드 생략 가능
      7. Full Outer Join
        1. 조인되는 모든 테이블의 데이터를 읽어 Join
        2. Left, Right 조인 결과의 합집합

2-2-2. 집합 연산자(Set_Operator)

  1. 2개 이상의 Table에서 Join 사용 없이 연관된 데이터를 조회하는 방법
  2. 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만든다.
  3. 제약 조건 
    1. Select절 컬럼 수가 동일해야한다.
    2. Select절의 동일 위치 데이터 Type이 상호호환 가능해야 한다. (동일할 필요는 없다.)
  4.  종류
    1. Union 중복 제거 (합집합)
    2. Union All 전부 표시 (합집합)
    3. Intersect 중복 제거 (교집합)
    4. Except 중복 제거 (차집합)
  5. 집합 연산자 자리는 Select ~ From ~ Where ~ Group By ~ Having ~ <집합 연산자> ~ Select ~ Order By

2-2-3. 계층형 질의와 셀프 조인

  1. 셀프 조인
    1. 두 컬럼 다 Alias를 반드시 사용해야 식별 가능하다. 
  2. 계층형 질의
    1. Table에 계층형 Data가 존재하는 경우의 데이터를 조회하기 위해 사용한다. 계층형 데이터란, 동일한 테이블에 계층적으로 상/하위 데이터가 포함된 것(ex.관리자와 하위사원)
    2. Entity를 순환관계 데이터 모델로 설계할 경우, 계층형 Data가 된다. 
  3. <oracle> 계층형 질의
    1. 계층형 질의에서 사용되는 가상컬럼
    2. Level : 루트 데이터를 1로 시작하여, Leaf까지 하위로 갈수록 1씩 증가한다.
    3. Connect_By_IsLeaf : 리프 데이터라면 1을, 아니면 0 리턴
    4. Connect_By_IsCycle : 자식을 갖는데, 해당 data가 조상 데이터면 1을, 아니면 0 리턴
    5. ex. Select ~, [Level, Connect_By_IsLeaf] From 테이블명 [Where 조건절] Start With 조건절 Connect by [No Cycle] Prior Conditions [Order Sibilings By Column 1 ...] 
      1. Prior 자식=부모 => 순방향 전개 => Top-Down => 부모->자식 방향 => 리프 = 1
      2. Prior 부모=자식 => 역방향 전개 => Bottom-Up => 자식->부모 방향 => 루트 = 1
      3. Order Sibilings by : 형제 Node의 위치를바꾸는 옵션, 즉 형제 Node 사이의 정렬
      4. No Cycle : 이미 나타난 동일한 데이터가 다시 나타나는 것을 "Cycle 형성"이라 하는데, 이럴 경우 런타임 오류가 발생하기 때문에, 'No Cycle'을 추가해주는 것, 사이클 발생 이후 데이터는 전개되지 않는다.
      5. Sys_Connect_By_Path = 컬럼명/경로 분리자 : 현재 전개할 데이터의 루트 데이터 표시 

2-2-4. 서브쿼리

  1. 단일행, 복수행 비교 연산자와 함께 사용 가능
  2. 서브 쿼리에서는 Order By사용 불가(<-> Order By절에는 서브쿼리 사용 가능)
  3. 서브쿼리는 메인 쿼리의 테이블 칼럼 사용 가능 (<-> 메인쿼리에서는 서브쿼리의 칼럼 사용x [당연..])
  4. Select, From, Where, Update문의 Set, Insert문의 Values에 서브쿼리 사용 가능 (Delete에서만 X)
  5. 서브쿼리 안에 있는 테이블(=조인할 테이블)의 기본키가 2개이면, Where절에서 조인(=)도 기본 키 2개 다 해주어야한다. => 비선호 컨텐츠 table [ 고객ID[FK] , 컨텐츠ID[FK] / 등록일자 ] => Select X.컨텐츠ID From 비선호컨텐츠X Where X.고객id = B.고객id (Inner Join 1) And X.컨텐츠id = B.컨텐츠id (2)
  6. Uncorrelated 서브쿼리 (비연관 서브쿼리)
    1. 서브쿼리가 메인쿼리 테이블의 칼럼을 갖고있지 않은 형태
    2. 서브쿼리에서 조건이 맞는지 확인할 때 사용
  7.  서브쿼리 분류 (반환되는 데이터 형태에 따라서)
    1. 단일행 서브쿼리 : 결과가 1건 이하, =, < , > 등과 함께 사용, 다중행 연산자 in, exists, all , any도 사용 가능
    2. 다중행 서브쿼리 : 결과가 여러개, 단일연산자 사용 X, Where Row num=1 => 서브쿼리 결과가 중복이 있어서 에러가 뜰 경우, 중복을 없애고 하나만 가져오게 하는 방법
    3. 다중 컬럼 : 여러 컬럼 반환, 조건절에 여러 컬럼 동시 비교 가능, 서브쿼리와 메인쿼리에서 비교하고자 하는 컬럼의 개수, 위치가 동일해야함
  8. 단일행 연관 서브쿼리(=스칼라 서브쿼리)는 JOIN해서 써도 결과가 같다.
  9. 인라인 뷰 : 안에 있는 서브쿼리로 원하는 내용 추출, 새로운 TABLE 만듦
    1. 서브쿼리로 만든 가상 테이블
    2. SQL 실행시에만 임시적으로 생성되는 동적 뷰
    3. DB에 저장되지 않음, 일회성임
    4. 동적 뷰라고도 한다. (<-> 정적뷰는 일반 뷰)
  10. Select 절 속 Where절 속에 또 Where절이 있는 경우 => "중첩 서브쿼리", 단일/다중행 서브쿼리와 구분하기
    1. 중첩 서브쿼리도 join으로 같은 결과 출력 가능, SELECT에 두 테이블 다 써주고, where 쓰고 AND 쓰면 됨
  11. Having 절에서 서브쿼리 사용
    1. 그룹함수와 함께 사용될 때, 그룹핑된 결과에 대해 부가조건을 걸기 위해 사용
  12. Update 문의 Set절에서 서브쿼리 사용
    1. 서브쿼리를 사용한 변경 작업시, 서브쿼리 결과가 Null을 반환하면 해당 컬럼 결과가 Null이 될 수 있기 때문에 주의
  13. Not Exists와 서브쿼리 예시
    1. Where문에 Not Exists는 서브쿼리 테이블의 결과물을 제외한 나머지를 메인쿼리에 출력
    2. 메인쿼리에서 서브쿼리의 결과물이랑 겹치는 값은 제외함 (차집합)
  14. 뷰 (View=가상테이블=Stored Query)
    1. 실제로 데이터를 갖고 있지 않음, 뷰의 정의만을 가짐
    2. Create View Employee as Select Emp.* , Dept, Dname From Emp, Dept Where Emp.Dept No = Dept.DeptNo (Select절의 결과물로 가상테이블을 만든다.)
    3. 인라인 뷰와 같은 기능을 한다. 일반 테이블처럼 사용
    4. 뷰의 특징 -
      1. 독립성 : 테이블구조가 변경되어도, 뷰 사용 프로그램은 변경할 필요가 없다.
      2. 편리성 : 질의 단순화 기능으로 자주 사용하는 sql문에 유용하다.
      3. 보안성 : 이용자가 접근 가능한 정보를 제한할 수 있다.
  15. 그룹 함수
    1. 전체 집계와 소계를 함께 구한다. 
    2. Group By 절 안에 다음 집계함수를 사용한다.
  16.  Rollup 함수 : A를 기준으로 소계,합계를 생성한다. (따라서 rollup(a,b)와 rollup(b,a)는 다름)
    1. Group by A + Roll up (B)
      1. Group by절에 Dname을 Rollup함수전에 기입하는 경우, Dname으로 전체 행을 분할
      2. 기존 Rollup함수 사용시, 전체 행에 대하여 집계를 구하지만, Groupby절에 칼럼 기입시, 전체행 기준이 아니라, 그룹으로 나누어진 이후 Rollup함수가 작동된다. (즉, 전체행의 집계를 실행하지 X)
  17. Cube 함수 : 가능한 모든 조합의 소계,합계를 생성한다. (시스템에 무리를 줄 수 있다.)
  18. Grouping Sets 함수 : 보고싶은 것만 소계를 생성한다. (Grouping 함수와 다름 주의)
    1. 빈 괄호 입력시 전체총합 출력
  19. Grouping 함수 : Rollup에 의해 집계가 일어나는 경우 1, 일어나지 않는 경우 0을 표기하는 함수
  20. 윈도우 함수 : 전체 테이블 중 원하는 일부만 작은 테이블로 만들어서 분석한다.
  21. Select Window 계열 Over 
    1. Select Window함수() Over
      1. 행 분할 Partition By (=group by 역할)
      2. 행 정렬 Order By
      3. 행 지정 Rows, Range (=where 역할)
      4. Window_Function over (<Partition by칼럼> <Order By절> <Windowing절>) 
        1. 순서 지켜야함
        2. 하나 빠지거나 그런건 상관 없음
        3. Partition by 대신 From 절 이후 Group by로 묶어도 된다.
        4. ex. Select Job, Sum(sal) Over (Partition by Job Order By Sal Desc Rows Unbounded Preceding) As Sum_sal
  22. 순위함수 Rank, Dense Rank, Row_Number
    1. window 함수의 마지막에 <windowing 절> 부분에 해당한다.
    2. rows의 행 호칭 방식
      1. unbounded preceding 맨 위의 행 ~ current
      2. preceding 이전 행
      3. current row 현재 행 ~ 맨 아래
      4. following current ~ 다음 행
      5. unbounded following 맨 아래 행
    3. rows between A and B
      1. Select Job, Ename, Sal, Sum(sal) over (Order By Sal Rows Between 1 preceding And 1 following 한칸 위 행 ~ 한칸 뒤 행, 즉 세 칸씩 sum되는 것) As cume_sal From Emp;
    4. Rows가 행의 위치 기준이라면, Range는 칼럼의 값 기준 연산에 참여할 행을 선택한다.
      1. range ex. Sum(sal) Over (Order By Sal Range 150 preceding 현재까지 포함해서 -150 선택) As cume_sal
      2. unbounded preceding => 무한한, 작은 값을 모두 선택
      3. Rank :
  23. 윈도우 집계함수 Sum, Max, Min, Avg, Count
  24. 행 순서 함수 First Value, Last Value, LAG, LEAD
    1. Lead함수(<->Lag함수) 다음 행을 가져옴, lag는 이전의 행을 뒤늦게 가져오는 느낌
      1. 파라미터 늘려서 쓸 수 있음
  25. 비율 함수 Ratio_To_Report, Percent_Rank, Cume_Dist, N Tile
    1. Ratio_To_Report : 윈도우가 적용되는 공간에서 전체 값의 합에서 해당 행이 가지고 있는 값의 비율을 나타냄
    2. Percent_Rank <sql server>지원X : 파티션 별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여, '행의 순서 백분율'을 구한다. 
  26. DCL 데이터 조작어, Grant & Revoke
    1. DCL은 "유저"를 생성하고, 권한을 제어하는 명령어이다. 즉, 유저가 사용하는 모든 DDL문장은 그에 해당하는 적절한 권한이 있어야지만 가능하다. 
    2. <oracle> 기본 유저 종류 :  Scott.Tiger(테스트용 샘플유저), Sys(DBA Role을 부여받은 유저), System(모든 권한을 부여받은 DBA유저, 설치시 passward 설정)
    3. 권한 부여 - DBA권한을 가진 System유저로 먼저, 그리고 다른 유저에게 권한을 주는 방식으로 진행
    4. 순서 
      1. 샘플 유저 생성 (create user)
      2. => 로그인하기 (create user, identified by 비밀번호)
      3. => 세션권한 부여[로그인 권한] (create session to 유저)
    5. <sql server> 
      1. 여기서는 샘플 유저 생성 이전에, 먼저 로그인을 생성해야한다.
      2. 순서 
        1. 로그인 생성 Create Login 로그인명 With Password = '비번', Default_Database = 최초 접속할 DB명
        2. 유저 생성을 위해서, 생성하고자 하는 DB에 이동시킨 후 처리해야함
    6. Object에 대한 권한 부여
      1. 오브젝트는 그저 명령어일 뿐이다. (<->Role)
      2. 오라클, SQL SERVER 둘다 소유자에게 권한을 부여받아야 한다는 사실은 동일하지만, <sql server>에서는 유저는 단지 '스키마'에 대한 권한만을 가진다는 점이 다르다. (즉, object는 유저가 아니라 스키마가 소유함)
    7. Role을 이용한 권한 부여
      1. DBA의 역할은 각 유저별로 어떤 권한이 있는지 관리하는 것
      2. DBA는 Role 생성 , Role에 각종 권한 부여, 다른 Role/유저에게 부여
      3. Role은 "권한 그룹"이다. 즉, 다양한 권한을 그룹으로 묶어 관리한다. 즉, 사용자와 권한 사이에서 중개역할 수행
      4. 시스템 권한, 오브젝트 권한 모두 부여 가능
      5. 유저에게 직접 부여될 수도 있고, 다른 Role에 포함되어 유저에게 부여될 수도 있음
      6. <oracle>에서 role의 종류 : connect(로그인권한), resource(오브젝트[=리소스] 생성 권한)
    8. 유저 삭제 명령어와 권한 
      1. drop user 유저명 cascade => 해당 유저가 생성한 오브젝트를 먼저 삭제한 후에, 유저 삭제

2-2-8. 절차형 SQL

  1. 절차형 SQL
    1. 분기, 반복이 가능한 모듈화된 프로그램
    2. DBMS에서 직접 실행
    3. Procedure / User Defined Function / Trigger
    4. <oracle> PL/SQL 엔진이 프로시저 내부의 절차적 코드 처리
      1. PL/SQL은 SQL문장을 BLOCK으로 묶고, 한번에 BLOCK 전부를 서버로 보내기 때문에 통신량이 줄어들고, 성능이 좋아짐
      2. 예시
        1. Create or Replace Procedure 프로시저명 (저장될 데이터&타입 지정)
        2. Is Begin .. Select ~ From ~ Where (변수 /상수 정의)
        3. Execption when ~ Insert Into ~
        4. Commit; End;
      3. Execute 프로시저명 ('20200908'); => 실행시 파라미터가 있지만,
      4. Drop Procedure 프로시저명 => 삭제의 경우 파라미터가 없다.
    5. <sql server> T-SQL
      1. 예시
        1. Declare 선언부 (변수, 상수) ... dtype 선언
        2. Begin 실행부 ... 처리하고자하는 sql문/로직 정의
        3. Error 처리 ... 예외 처리부
        4. End;
        5. Create Procedure @ parameter1 dtype ... With As ... Begin... Error처리..End;
        6. Drop Procedure 스키마명, 프로시저명;
    6. 연속적 실행, 조건에 따른 분기 처리를 이용해 특정 기능을 수행하는 '저장 모듈'을 생성할 수 있다..
  2. 프로시저
    1. 주로 DML을 이용해서 주기적으로 진행해야하는 작업을 저장한다.
    2. 별도의 호출을 통해 실행한다.
    3. Create Or Replace Procedure문
    4. 기존에 같은 이름의 프로시저가 있을시, 무시하고 새로운 내용으로 덮어씀
    5. 프로지서 실행 : Execute, Exce, Call
    6. 프로시저 삭제 : Drop
    7. 프로시저에서 DDL을 사용하기 위해서는, Execute immediate 'DDL'문을 사용
  3. 트랜잭션 처리 (<-> 사용자 정의함수는 결과를 리턴한다.)
    1. 작업 결과를 저장한다. 실제로 반영하거나 취소하는 영역
    2. 4가지 영역 중 네번째이다. 조건/반복 영역 => sql이용 데이터관리 영역 => 예외처리 영역 => 트랜잭션 처리영역
  4. 사용자 정의 함수
    1. 프로시저와 구조가 비슷함
    2. 함수 호출시 특정 값을 돌려받을 수 있다. (리턴값, 반환값)
    3. 작업 결과를 호출한 쿼리문에 돌려준다. (<-> 프로시저는 DB에 저장)
    4. 일반적 집계함수처럼 호출 가능
    5. 예시
      1. Declare Create Or Replace Function Is Begin ( Control, SQL, Exception, Return) END
      2. Select 나이계산('19860908') From 사원; Update 사원 Set 나이 = 나이계산('19860908') Where 사번=123;
    6. <sql server>과 <oracle>에서의 사용자 정의함수 차이점 = > scl server은 create function, @ 기호 있음, returns 다음 declare @ 선언함
  5. 트리거 : 삽입,삭제, 수정으로 DB에 변화 발생시, 자동 호출됨
    1. Insert, Update, Delete 등 조작어(dml)에 의해 자동 수행됨
    2. 자동으로 실행되기 때문에, 리턴 값& 매개변수 & 커밋 다 없음 (즉, TCL로 트랜재션 제어하지 X)
    3. Declare 영역을 '특정 시점(before)'과 '특정 시점후'로 지정
    4. 'For Each Row' 지정 가능 (변화가 있는 행들에 각각 적용)
    5. <oracle> Create Or Replace Trigger 트리거 선언 => 변수 선언 => 레코드 선언시 트리거 발생 
    6. Old : Insert = Null, New : Insert = 입력된 값
    7. 프로시저와 트리거 비교
      1. 프로시저                    트리거
      2. Create procedure / Create Trigger
      3. Execute(Exec,Cali) / 생성 후 자동 실행
      4. Commit,Rollback O / Commit, Rollback X
      5. 매개변수 O / 매개변수 X

2-3. 최적화 기본 원리

2-3-1. 옵티마이저와 실행계획

 

  1. 옵티마이저
    1. 최적의 실행계획을 짠다. 가끔 실수로 잘못된 계획을 짤 수도 있다.
    2. 오라클 힌트를 사용하여, 올바른 실행계획으로 도움줄 수 있도록 해야함
    3. 동일 sql문에 대해 계획이 달라도 결과는 같음
  2. 실행 계획 구성 요소
    1. join 순서
    2. join 기법
    3. access 기법
    4. 최적화 정보 - cost, card, bytes
    5. 연산 - operator
    6. 질의처리 예상 비용 - 시간은 알 수 없다.
  3. 옵티마이저 종류
    1. 로지컬 : Query Transformtaion 수행하면서 여러 형태로 변환 (결과는 동일)
    2. 피지컬 : 가장 저렴한 쿼리를 고름
      1. Cost Optimizer
      2. Plan Optimizer
  4. 옵티마이저가 최적의 실행 방법을 결정하는 방식
    1. 규칙 기반 optimizer : 규칙 우선순위 기반으로 실행계획 생성
    2. 행에 대한 고유주소 엑세스 방식(single row by rowid) : 인덱스 이용 (전체 테이블에 access하는 것보다 좋음)
      1. 이용 가능한 인덱스가 있으면, 항상 인덱스를 사용하는 규칙 계획 생성
      2. 조인 순서 결정시, 조인 칼럼 인덱스 존재유무가 판단 기준
        1. 양쪽에 존재 = > 우선 순위 높은 테이블 먼저
        2. 한쪽만 존재 = > 인덱스 없는 테이블 먼저
        3. 둘 다 존재하지 않음 = > From절 뒤 나열된 순서로 테이블 먼저
        4. 우선순위가 동일함 = >  From 절 뒤 나열된 순서의 역순으로 테이블 먼저
    3. 비용 기반 optimizer : 비용(=예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획 선택
      1. 비용에 따라  full scan이 유리할 수도 있음
      2. 규칙 기반 optimizer의 단점 극복을 위해 출현
      3. 다양한 객체 통계정보, 시스템 통계정보 이용
        1. 질의 변환기 : sql문을 용이한 형태로 변환
        2. 대안 규칙 생성기 : 동일한 결과를 생성하는 대안 계획 생성
        3. 비용 예측기 : 생성된 대안 계획의 비용 예측 (=> 모든게 정확해야함 : 계산식, 예측, 분포도 등등)
  5. 실행 계획 분석 : 각 작업 바로 밑에 나타난 작업을 시작으로, 들여쓰기 깊이가 같은 지역을 찾자 (각 작업에 id가 부여되고, 들여쓰기가 된다.)
    1. ***해석은 가장 왼쪽의 밑에서부터 시작
    2. ex. Index Range Scan => Table access by index range id => Index Unique Scan => Nested loops
    3. Nested loops => Table Access Full => Filter
    4. 즉, 메인쿼리 => 서브쿼리 순서
  6. 실행 계획 주의사항 : 들여쓰기가 항상 우선순위가 아니다. ex. Index Unique scan이 들여쓰기가 더 되어있지만, Table Acess Full이 먼저 실행된다.
  7. SQL 처리 흐름도 (Access Flow Diagram)
    1. sql 내부적 처리절차를 시각적으로 표현한 것 (실행 시간 알 수 X)
    2. 인덱스 스캔, 테이블 전체 스캔과 같은 엑세스 기법 표현(엑세스 방법)
    3. 성능적 측면도 표현 가능

 

2-3-2. 인덱스 기본

  1. 인덱스는 일종의 '오브젝트'이다. 인덱스가 생성되면, 매핑된 또다른 테이블(인덱스)가 생성된다. => 생성된 테이블은 인덱스 칼럼을 기준으로 sorting되어 저장되기 때문에, 검색시 매우 빠르다.
  2. 주로 where절, order by 절에서 자주 쓰이는 칼럼을 인덱스로 지정한다.
  3. select 속도가 증가하는 반면, insert와 update는 속도가 저하된다.(update에서 몇개만 바꿔주면 부하가 없을 수 있음)
  4. 블록
    1. 인덱스는 해당 테이블의 블록에 주소를 가지고 있다.
    2. 데이터가 저장되는 최소 단위
    3. 테이블의 데이터들이 'Row행' 단위로 저장되어 있다
  5. 랜덤 엑세스
    1. 인덱스를 스캔하여 테이블로 데이터를 찾아가는 방식
    2. 데이터가너무 많을때에는 인덱스스캔보다 full scan이 낫다
  6. 인덱스 종류
    1. 단일 인덱스
    2. 결합 인덱스
      1. 순서가 매우 중요하다.
      2. 인덱스 생성이 아래 컬럼을 더 앞 순서에 배치한다.
      3. 동등 조건(=)으로 많이 쓰는 컬럼이 앞에 오도록
      4. 범위지정 컬럼(between)이 그 다음 오고
      5. 'id'와 같이 분별력 높은 컬럼이 그 다음
  7. 동등 조건이 미치는 영향
    1. 인덱스가 (col1, col2)일 때 => ~ Where col2='a' => A만을 추출
    2. 인덱스가 (col1, col2)일 때 => Where col2='a' => 범위를 줄이는데 사용
  8. 인덱스 스캔 방식 종류
    1. index full scan
    2. index range scan
    3. index skip scan
    4. index fast full scan
    5. 인덱스 생성 
      1. Create index 인덱스명 On 테이블명 (인덱싱할 컬럼) 
    6. 인덱스 명시적으로 사용하기
      1. select /*+Index (테이블명 인덱스명) */ * From 테이블명 => 의미:select시 이 인덱스를 사용하여라.
    7. 인덱스 삭제 : drop index 인덱스명
    8. 트리 기반 인덱스(=B-tree 인덱스) 
      1. RDBM에서 가장 일반적이다.
      2. '='로 검색하는 일치 검색(EXACT MATCH), 'Between', '>'로 검색하는 범위검색(range) 둘 다 적합하다.
      3. 각각의 노드를 '블록'이라고 칭한다. (루트블록, 브랜치 블록, 리프 블록) 
        1. 브랜치 블록은 하위 단계의 블록을 가리키는 '포인터'를 가리킨다.
        2. 리프 블록은 인덱스를 구성하는 컬럼의 데이터이자, 해당 데이터에 대한 행의 위치를 가리키는 '레코드 식별자'이며, 양방향 링크(double link)를 가진다. 
        3. 여기에서는 B+tree를 구분하지 않음
    9. 클러스터형 인덱스 <sql server>
      1. 저장 구조에 따라서 클러스터형 VS 비클러스터형 인덱스로 구분한다.
      2. <oracle>의 IoT와 유사하다.
      3. 인덱스의 리프페이지 = 데이터페이지이다. 즉, 테이블 탐색에 필요한 레코드 식별자가 리프페이지에 없다. 
        1. 리프 페이지의 모든 로우(=데이터)는 인덱스 키 컬럼 순으로 물리적 정렬되며, 한가지 순서로만 가능
    10. 비트맵 인덱스
      1. 질의 시스템 구현시, 모두 알 수 없는 경우인 DW 및 AD-HOC 질의환경을 위해 설계
      2. 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장
    11. Full scan 전체 테이블 스캔 : 시간이 오래걸림, <oracle>에서는 테이블의 고수위마크 아래 모든 블록을 읽음
      1. 옵티마이저가 Full scan을 선택하는 경우
        1. sql문에 조건이 존재하지 X
        2. sql문의 주어진 조건에 사용가능한 인덱스가 존재하지 X
        3. 옵티마이저의 취사선택 (데이터가 많을 때)
        4. 그 밖에 병렬처리 방식으로 처리하는 경우
        5. Full Table 스캔 힌트를 사용한 경우
    12.  인덱스 스캔
      1. 인덱스의 리프블록이 인덱스 구성 컬럼 + 레코드 식별자로 구성되어 있어, 검색시 인덱스 리프블록을 읽으면 이 두 값을 알 수 있다.
      2. 인덱스는 인덱스 구성 컬럼의 순서로 정렬된다. ex. 인덱스 구성 컬럼이 A+B라면, A컬럼으로 먼저 정렬, A값이 동일하면 B컬럼으로 정렬
    13.  인덱스 유일 스캔 => 유일인덱스 사용, 중복 허락 X
      1. 모두 '='으로 값이 주어지면 결과는 최대 1건
    14. 인덱스 범위 스캔 => 인덱스 이용, 한건 이상의 데이터 추출, 유일 인덱스로 값을 못구하면, 비유일 인덱스를 이용하는 모든 엑세스 방식은 이거 사용
    15. 인덱스 역순 범위 스캔 => 리프 블록의 양방향 링크 이용, 내림차순으로 데이터를 읽어 최댓값 쉽게 찾을수 O
    16. Full스캔과 인덱스 스캔의 차이
      1. 인덱스 스캔은 레코드 식별자 이용
      2. 인덱스 스캔은 정확한 위치를 알고 있고, full스캔은 여러 블록씩 읽음
      3. 인덱스 스캔은 한번의 i/o요쳉어 한 블록씩, full스캔은 전체 데이터 or 테이블 대부분 찾을 때 유리
      4. 옵티마이저는 인덱스가 존재하더라도, 경우에 따라 full scan 방식 선택 가능

2-3-3. 조인 수행 원리

  1. 조인 종류 : NL Join / Sort Merge Join / Hash Join
  2. Nested Loop Join (NL Join)
    1. OLTP 환경
    2. 인덱스 유무에 영향
    3. OUTER TABLE 중요 
    4. 중첩 for문과 같은 원리 : ex. 그룹과 멤버 2개의 테이블이 있다고 가정했을 때, 그룹을 outer table(=driving table), 멤버를 innter table
      1. 두 테이블 모두 scan해서 데이터를 찾는다.
      2. 비효율적이고 오래걸린다.
      3. 조인 조건의 인덱스 유무에 영향을 받는다. (<->soft merge)
      4. 유니크 인덱스 활용
      5. OLTP 환경(=ONLINE 환경)의 쿼리에 적절하다.
      6. 조인 컬럼에 적당한 인덱스가 없어서 자연 조인(Natural Join)이 효율적일 때 유리하다. 
      7. Outer Table(=Driving Table)의 성능에 중요한 요인
      8. 1:M에서 1에 해당하는 테이블이 소량의 데이터를 가진 경우에 사용하면 성능이 좋아짐
    5. NL Join 실행 순서
      1. 선행 테이블에서 조건에 맞는 값 찾기
      2. 선행 테이블의 조인키를 가지고 후행 테이블 조인 키 확인
      3. 후행 테이블의 인덱스에 선행 테이블의 조인 키 존재 확인
      4. 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블에 엑세스하여 버퍼에 저장
      5. 앞의 작업을 선행 테이블에서 만족하는 키 값이 없을 때까지 반복 수행
      6. Nested Loops Anti Join : 서브 쿼리 앞이 Not Exists일 경우 안티
      7. Nested Loops Semi Join : 서브쿼리 앞이 Exists일 경우 세미
    6. Sort Merge Join
      1. PGA 영역
      2. Non-Equi Join
      3. 인덱스 유무에 영향 X
      4. OUTER TABLE 중요 X
      5. NL Join과 비슷하게 중첩 for문과 같은 원리인데, 차이점은 두 테이블을 "조인 컬럼 기준으로 데이터를 정렬시킨 후에" join하는 방식이다.
      6. 넓은 범위의 데이터를 처리할 때 주로 이용한다.
      7. 비동등 조인에 대해서 "도" 조인 가능
      8. inner table에 적절한 인덱스가 없어서 NL Join을 쓰기에 너무 비효율적일 경우 사용한다.
      9. Range Scan 쿼리에서 적절하다. (정렬되어 있으므로)
      10. Table Random Acceess가 발생하지 않는다. => 경합이 발생하지 않아 성능에 유리함
      11. PGA 영역에서 Sorting이 수행한다. => 경합이 발생하지 않아 성능에 유리함
      12. Sort Merge Join의 실행 순서
        1. 선행 테이블에서 조건에 맞는행 찾기
        2. 선행 테이블의 조인 키 기준 정렬 작업 수행
        3. 1,2번 작업 반복, 모든 행을 찾아 정렬
        4. 후행 테이블에서도 같은 작업 진행
        5. 정렬된 결과를 이용하여 조인을 수행하고, 결과값을 추출 버퍼에 저장
    7. Hash Join
      1. DW 환경
      2. Equi Join
      3. 정렬작업 필요 X (대량 데이터 배치 작업에 유리)
      4. Outer Table 중요
      5. NL join의 랜덤 엑세스 문제점을 해결하기 위해 등장함
      6. 정렬 작업 필요가 없어서 정렬이 부담되는 대량 배치 작업에 유리하다
      7. Outer Table(=Driving Table)이 성능에 매우 중요한 요인이다.
      8. 배치에서 쓰면 좋은 수행 원리이다.
      9. 대용량 테이블을 Join할 때 사용하면 좋다.
      10. 인덱스가 존재하지 않는 경우에도 사용할 수 있다.
      11. 동등조인에서 만 가능하다.
      12. Dw 환경 등에서 데이터를 집계하는 업무에 많이 사용한다.
      13. 해쉬 테이블의 key컬럼에 중복값이 없을 수록 성능에 유리하다.
      14. PGA 영역에서 수행되어 매우 빠르다.
      15. Table Random Access가 발생하지 않음
      16. Hash 영역에 들어갈 테이블 사이즈가 충분히 작아야 성능 유리
      17. 수행 빈도가 높은 OLTP환경에서 수행하면, 오히려 CPU/메모리 사용량이 증가한다.
      18. 그룹, 멤버 테이블에서 멤버 테이블의 데이터가 너무 크다면, Group 테이블을 Build Input으로 삼아서 Hash 영역에 저장해둔다.
        1. 그룹 해쉬 영역에 있으면서 멤버가 조인되는 원리이다.
      19. Hash Join의 실행 순서
        1. 선행 테이블에서 조건에 만족하는 행 찾기
        2. 선행 테이블의 조인 키를 기준으로, 해쉬함수를 적용하여 해쉬 테이블 생성
        3. 1,2번 작업 반복, 선행 테이블의 모든 조건에 맞는 행을 찾아 해쉬테이블 완성
        4. 후행 테이블에서 조건에 만족하는 행을 찾음
        5. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음
        6. 같은 버킷에 해당하면 조인에 성공, 추출 버퍼에 저장
        7. 후행 테이블 조건만큼 반복 수행하여 완료

 

2-2. SQL 활용 문제풀이 오답

  1. 순수 관계연산자에 해당하지 않는 것은? update (select, join, devide[현재 사용되지는X]) 
  2. 추천컨텐츠 엔티티에서 추천해야하는 추천 대상일자에 해당하는 일자에에만 추천하고, 비선호 컨텐츠 엔티티에 등록된 데이터에 대해서는 추천을 수행하지 않아야한다.
    1. (O) Where Not Exists (Select X.컨텐츠ID From 비선호 컨텐츠 X Where X.고객ID = B.고객ID AND X.컨텐츠ID = B.컨텐츠ID);  (A가 고객, B가 추천컨텐츠임)
      1. (X) Where Not Exists (Select X.컨텐츠ID From 비선호 컨텐츠 X Where X.고객ID = B.고객ID); 위에서 추가된 부분을 안써주면(AND절) 단 하나의 컨텐츠라도 비선호로 등록한 고객에 대해서 "모든" 컨텐츠가 추천에서 베재됨
    2. (O) Select ~From ~ On (A.고객 ID = B,고객ID) Inner Join 컨텐츠 C On (B.컨텐츠ID = C.컨텐츠ID) Left Outer Join 비선호컨텐츠 D (B가 추천컨텐츠, C가 컨텐츠명) ~And D.컨텐츠ID Is Null
      1. (X) Select ~ From ~ On (A.고객ID = #custId# And A.고객ID = B.고객ID) Inner Join 컨텐츠 C On (B.컨텐츠ID = C.컨텐츠ID) Right Outer Join 비선호컨텐츠 D ~And D.컨텐츠ID Is Not Null;
      2. 비선호 컨텐츠 D를 Join할 때, Left Outer Join을 해야지 앞서 Inner Join했던 A,B,C가 모두 들어옴
      3. 그리고 AND절에 컨텐츠ID가 NULL이 아닌 것을 가져왔는데, 위에서 옳은 쿼리문 은D(비추천 컨텐츠) ID가 Null인것을 가져와야 선호컨텐츠가 잘 조인 됨. 아래처럼 비선호테이블에 존재하는 ID가 선호 컨텐츠와 겹치면 X
      4. 즉, 비선호컨텐츠의 컨텐츠ID가 Null이 아니어야한다는 조건이 있어야, 비선호 컨텐츠로 등록하지 않은 컨텐츠는 (여기서 Null) 추천 컨텐츠에만 등록됨
    3. "제품" [제품 코드 / 제품명, 제품유형코드, 단위 ] ---< "생산 제품" [라인 번호(FK) 제품 코드(FK) ] >---"생산 라인" [라인 번호 / 최초 가동 일자]
      1. 제품, 생산제품, 생산라인 엔티티를 Inner Join하기 위해서 생산제품 엔티티는 Where절에 최소 2번이 나타나야한다.
      2. 제품과 생산라인 엔티티를 Join할 시 적절한 Join 조건이 없으므로 카타시안 곱이 발생한다.
      3. 제품과 생산라인 엔티티에는 생산제품과 대응되지 않는 레코드가 있다.
      4. 특정 생산라인 번호에서 생산되는 제품의 제품명을 알기 위해서는 제품, 생산제품 2개 엔티티의 Inner Join이 필요하다. (오답 : 생산라인까지 3개 엔티티의 Inner Join이 필요하다.)
        1. 생산 제품 엔티티에는 라인번호가 있고, 제품 엔티티에는 제품명이 있으므로
        2. 생산 라인 엔티티의 라인번호, 최초 가동일자는 알 필요 X
      5. 고객이 서비스를 사용한 시간에 따라 차등 단가를 적용하려고 한다. 시간대별 사용량 테이블을 기반으로 사용금액을 추출하는 SQL로 적절한 것은?
        1. Select A.고객ID, A.고객명, SUM(B.사용량 * C.단가) As 사용금액 From 고객 A Inner Join 시간대별 사용량 B On (A.고객ID = B.고객ID) Inner Join 시간대구간 C On B.사용시간대 Between C.시작시간대 And C.종료시간대 ..
        2. Inner Join을 연속으로 쓸 수 없음, 위처럼 한번 쓰고 on 이런식으로 이어야함 ex. 고객 A Inner Join 시간대별사용량 B Inner Join 시간대구간 C
        3. Between Join 이런건 없음
      6. 다음 중 팀 테이블과 구성 테이블의 관계를 이용해서 소속 팀이 가지고있는 전용구장의 정보를 팀의 정보와 함께 출력하는 SQL을 작성할 때 결과가 다른 것은?
        1. Select T.(접두사 다 X)Region_Name, T.Team_Name, T.Stadium_ID, S.Stadium_Name From Team.T Inner Join Stadium S Using (T.Stadium_Id = S.Stadium_Id Stadium_ID); (X) 
          1. Using절은 <sql server>에서 사용 불가, alias/접두사 사용 불가, Join컬럼 1개만 표시해야함
          2. ON절은 컬럼명 달라도 괜찮음, Alias, 접두사 사용 가능
      7. 카타시안 곱을 위한 SQL문장이다. 같은 결과를 출력하는 두번째 문장의 빈칸 작성하기
        1. Select Ename, Dname From Emp, Dept Order By Ename;
        2. Select Ename, Dname From Emp ( Cross Join ) Dept Order By Ename;
        3. Cross Join은 테이블간 Join조건이 없는 경우 생길 수 있는 모든 데이터의 조합
      8. Left Outer Join은 B의 Join칼럼에서 같은 값이 없는 경우 B테이블에서 가져오는 칼럼들은 Null 값으로 채운다.
        1. 즉, left outer join 결과를 고르라는 문제에서 데이터는 모두 다 출력되야하고, 대신 join되지 않은 부분이 모두 NULL처리 되어있어야함. 
        2. Left Outer Join 단말기 B On (A.고객번호 In (11000, 12000) And A.단말기ID = B.단말기ID) => ON~에 해당하는 데이터 이외에 다 Null 처리
        3. 만약 위와 다르게 On절이 아니라, Where 절에 In~ 이런식으로 되어있으면 모든 데이터가 아니라 해당되는 데이터만 출력되는게 맞음 
      9. Select A.ID, B.ID From Tbl1 A 다음 1,2,3번 입력시 같은 결과 출력 
        1. Full Outer Join Tbl2 B On A.Id = B.Id
        2. Left Outer Join Tbl2 B On A.Id = B.Id Union Select A.Id, B.Id From Tbl A Right Outer Join Tbl2 B On A.Id = B.Id
        3. Tbl2 B Where A.Id = B.Id Union All Select A.Id, Null From Tbl1 A Where Not Exists (Select 1 From Tbl2 Where A.Id = B.Id) Union All Select Null, B.Id From Tbl2 B Where Not Exists (Select 1 From Tbl1 A Where B.Id = A.Id)
      10. Left, Full, Right 외부 조인(Outer Join)하면 생성되는 결과 건수로 가장 적절한 것은?
        1. 주키와 외래키는 영향을 미치지 않는다.
        2. Left 하면 왼쪽 테이블은 전부출력, 오른쪽은 겹치는 것만 출력 (나머지도 마찬가지)
      11. 신규 부서의 경우 일시적으로 사원이 없는 경우도 있다고 가정하고, Dept와 Emp를 조인하되, 사원이 없는 부서 정보도 같이 출력 (사원이 없는부서도=부서 Dept기준 Left Join) 하도록할 때, 빈칸 안에 들어갈 내용은?
        1. Select E.Name, D.Deptno, D.Dname From Dept.D ( Left (Outer) Join ) Emp E On D.Deptno = E.Deptno;
      12. 보기는 게시판별 게시글의 개수를 조회하는 SQL문장이다. (게시글이 없는 게시판도 조회되어야한다) <oracle>을 기준으로 작성된 sql문장을 <sql server>에서도 동일한 결과를 얻을 수 있는 Ansi 표준구문으로 변경하여라.
        1. Select A.게시판Id, A.게시판명, Count(B.게시글Id) As Cnt From 게시판 A, 게시글 B
        2. Where A.게시판Id = B.게시판Id(+) And B.삭제여부(+) = 'N' And A.사용여부 = 'Y' ...
        3. ↓
        4. Select A.게시판Id, A.게시판명, Count(B.게시글Id) As Cnt From 게시판 A Left Outer Join 게시글 B
        5. On (A.게시판Id = B.게시판Id And B.삭제여부 = 'N') Where A.사용여부 = 'Y'
        6. 다 똑같은데 카타시안 곱에서 Left Outer Join으로 바꾼 차이밖에 없다.
      13. Exept문과 같은 결과를 출력하려면 Not Exists를 쓰거나, Not In을 쓰면 되는데, WHERE ~ AND절로 이어야지 Not in, Not Exists구문을 두세번 쓰면 결과가 달라진다.
      14. Intersect 교집합과 같은 결과를 출력하려면 
        1. Not Exists + Minus조합 (이용된 적이 있는 서비스를 minus한 값이 Not exists한 where절을 select하면 이용된적이 있었던 서비스가 된다. (이중 부정,,)
      15. Union All을 사용하는 경우, 칼럼의 Alias는 1번째 SQL 모듈 기준으로 표시되며, 정렬 기준은 마지막 SQL모듈에 표시하면 된다. (예를들어 select ~ union all ~ select ~ 이렇게 되어있었다면 첫번째 select로 출력됨)
      16. Union all 수행 후 Union이 수행되었다면, (union은 중복된 행을 하나의 행으로 출력하고, union all은 중복된 행을 모두 출력) => 나중에 수행된 union기준으로, 중복이 제거된다. (하나로 출력)
      17. "회원 기본정보" [회원 ID] --- "회원 상세정보" [회원 ID(FK)] 
        1. 회원 ID컬럼을 대상으로 (회원기본정보 Union All 회원상세정보) 연산 수행시, 결과 건수는 회원기본정보의 전체건수의 2배이다.
        2. 회원 ID컬럼을 대상으로 (회원기본정보 Intersect 회원상세정보) 연산 수행시, 결과 건수와 두 테이블을 회원ID로 Join연산을 수행한 결과의 건수는 동일하다. (Union도 마찬가지)
      18. Select C3 From Tab1 Start With C2 Is Null Connect By Prior C1=C2 Order Siblings By C3 Desc
        1. 결과는 C3가 출력되는데, 
        2. ***부모 , 자식 판단 기준은 "Null 존재여부"** => C2에 Null이 존재하기 때문에 "부모'이다.
        3. 근데 Prior C1=C2니까 자식=부모라서 순방향(Top-Down)전개
      19. 평가 대상 상품에 대한 품질 평가 항목별 최종 평가 결과를 추출하는 SQL문장으로 옳은 것은? (단, 기대수준에 미치지 못할경우에만 재평가 수행)
        1. 3개의 테이블을 조인한다. Select ... From 평가결과 A, 평가 대상 상품 B, 품질 평가 항목 C 
        2. Where A.상품ID = B.상품ID
        3. And A.평가항목ID = C.평가항목ID
        4. And A.평가회차 = (Select Max(X.평가회차) From 평가결과X Where X.상품ID = B.상품ID And X.평가항목ID = C.평가항목ID) => 연관 서브쿼리로 '특정 상품'에 대한 평가항목별 최종평가 회차를 선택
        5. 오답 : Select Max(평가회차) As 평가회차 From 평가결과 D <= 인라인뷰 사용, 평가 상품 대상이아니라, 전체 DATA 대상이라서 오답
        6. 또 MAX를 여러개 즉, 다른 속성에 대해서도 MAX를 구한 보기 오답
      20. 가장 최근에 변경된 데이터를 기준으로, 보기와 같은 결과 출력하는 SQL문장 고르기, 결과는 부서 임시 테이블에서 담당자가 바뀐 데이터만 담당자를 바꿔서 출력
        1. 답 : Update 부서 A Set 담당자 = (Select B.담당자 From 부서임시B Where B.부서코드 = A.부서코드 And B.변경일자 = (Select Max(C.변경일자) From 부서임시 C Where C.부서코드 = B.부서코드)) Where 부서코드 In (Select 부서코드 From 부서임시);
        2. select절은 담당자 이름을 부서 임시 B에서만 찾도록 설정
        3. 연관 서브쿼리는 가장 최근 데이터를 반영하기 위해서, 변경일자의 Max값(=최신 값)을 select
        4. 오답 : Update 부서 A Set 담당자 = (Select C.부서코드 ...부서코드는 업데이트 할 필요가 없음
        5. 오답 : 정답인 문장과 다 같은데 맨 뒤에 WHERE절이 없다.
          1. Where절이 없어서 부서 테이블의 모든 부서에 대해서 Update가 수행된다. 즉, 임시테이블에 없는 부서는 다 Null이 되어버림.
      21. 뷰는 단지 정의만을 가지고 있으며, 실행 시점에 질의를 재작성하여 수행한다. 
        1. 보안을 강화하기 위한 목적으로 활용할 수도 있으며,
        2. 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.
      22. Select Case When Grouping (A.서비스 Id) = 0 Then A.서비스ID) = 0 Then A.서비스Id Else '합계' End As 서비스Id, Case When Grouping(B.가입일자) = 0 Then NVL(B.가입일자, '-') Else '소계' End As 가입일자, Count(B.회원번호) As 가입건수 From 서비스 A Outer Join 서비스가입 B On (A.서비스ID = B.서비스ID And B.가입일자 Between '2013-01-01' And '2013-01-31') Group By Rollup (A.서비스ID, B.가입일자);
        1. Grouping => Null이 아닐 때 합계 및 소계를 리턴 (즉, 데이터 값이 Null인건 출력되지 X)
        2. Left Outer Join => 서비스 테이블에 있는 모든 데이터가 추출되어야 하므로, 서비스 테이블에만 있었던 서비스 ID=004가 추출되어야함 주의
        3. Roll up => Roll up (A기준 소계,합계 출력) 출력, 즉, Rollup(A,B) = (A,Null) , (B,Null) , (A, B)
      23. Select (Case Grouping(B.지역ID) When 1 Then '지역전체' Else Min(B.지역명) End) As 지역명, (Case Grouping(To_Char(A.이용일시, 'YYYY.MM')) When 1 Then '월별합계' Else To Char(A.이용일시, 'YYYY.MM')) When 1 Then '월별합계' Else To_Char(A.이용일시, 'YYYY.MM')... Group By Rollup(B.지역ID, To_char(A.이용일시, 'YYYY.MM')) 
        1. Grouping은 Null일 때 = 소계일 때 = 1이다. 
          1. Grouping(A,B) = (0,0)
          2. Grouping(A,Null) = (0,1)
          3. Grouping(Null,B) = (1,0) 
          4. 오답에서 Select (Case Grouping When 0 Then '지역전체' 이렇게 나와있는 부분이, 조건의 표현이 잘못된 부분. 1일 때 소계..
        2.  Cube, Grouping Sets 이용한 보기들 다 오답, 각 지역별 월별 합계와 (지역 전체+월별합계)만 나와있어서 이용월에 따른 지역전체의 합계가 없음 => 지역 테이블 기준 Roll up
      24. 일반 그룹 함수를 사용하여 Cube, Grouping Sets와 같은 그룹 함수와 동일한 결과를 추출할 수 있으나, Rollup 그룹함수와 동일한 결과는 추출할 수 없다.있다.(X)
      25. Cube 그룹함수는 인자로 주어진 컬럼의 결합 가능한 모든 조합에 대해서 집계를 수행하므로 다른 그룹함수에 비해서 시스템에 대한 부하가 크다.
      26. '설비'와 '에너지 사용' 테이블이 있을 때, 설비 ID 소계 / 에너지사용 소계 / 전체 소계 모두 있으면 답으로
        1. Cube (A.설비 ID, B.에너지코드)
        2. Grouping Sets (A.설비ID), (B.에너지코드), (A.설비ID, B.에너지코드),() ) 
        3. 이 답이 될 수 있다.
      27. Group By Grouping Sets(자재번호, (발주처ID, 발주일자)) 이렇게 입력시
        1. 발주처 ID와 발주일자가 묶여있으므로 발주처 ID와 발주일자가 모두 "전체"로 고정되어있는 상태에서 자재번호가 1,2,3으로 바뀔 때의 소계가 출력됨
      28.  Select 상품ID, 월, SUM(매출액) As 매출액 From 월별매출 Where 월 Between '2014.10' And '2014.12' Group By Grouping Sets(상품ID, 월))
        1. 원하는 것만 보는 Grouping Sets에서는 () 빈 괄호가 전체 합계이고, 저렇게 두 개를 괄호에 입력시, 상품 id 각각 + 월 각각의 소계가 계산된다. (ex. p001 / 2014.10 / xxxx , p001/ 2014.11, xxxx ....)
      29. 윈도우 함수 처리로 인해 결과 건수가 줄어든다.(X) => 윈도우 함수는 결과에 대한 함수처리이기 때문에, 결과건수는 줄어들지 않는다.
      30. 윈도우 함수의 적용 범위는 Partition을 넘을 수 없다.(O)
      31. Rank() Over (Order By 매출액 Desc) , Order By RNK; => Rank()는 중복 있을시 다음 순서로 넘어감. 3,3,5
      32. 게임 상품별로 고객 목록을 추출하기 위해서는 Over절에 'Partition By 게임상품ID'를 적용하여 게임상품별 활동점수로 순위가 추출될 수 있도록 해야한다. 
        1. Dense_Rank는 중복있어도 중간 순위를 비우지 않음 
      33. Select 추천경로, 추천인, 피추천인, 추천점수 From (Select 추천경로, 추천인, 피추천인, 추천점수, Row_Number() Over (Partition By 추천경로 Order By 추천점수 Desc) " 각경로별로 추천 점수가 높은 순서로 배열한 뒤" As Rnum From 추천내역) Where RNum = 1; 순번이 1인 것만 출력(즉, Max만)
      34. 유사개수 컬럼은 상품전체의(X) 상품분류코드별 평균상품가격을 서로 비교하여 10000~ +10000 사이에 존재하는 상품 분류코드의 개수를 구한 것이다. <= Group By 상품분류코드
        1. Window Function을 Group By 절과 함께 사용시, Group by가 먼저 실행되고 윈도우 함수가 실행됨
        2. Window function의 Order By절에 Avg 집계함수를 사용해서 오류가 발생한다.(X)
      35. Case When Start_Val = Lag(End_Value) Over .. Then 1 Else 0 Flag1 / Case When End_Val = Lead(Start_Value) Over ... Then 1 Else 0 Flag 2 / Select Where Flag1 = 0 Or Flag2 = 0
        1. lag는 end_val 컬럼에서 하나씩 앞에 값을 가져오고 (따라서 첫번째 값은 null이 됨) , lead는 start_val에서 하나씩 뒤에 값을 가져옴
        2. 그 값들이 각각 start_val, end_val과 같으면 1을, 다르면 0 을 부여해서 둘 중 하나라도 0이있는 행만 출력하는 sql문
      36. dbms에서 생성된 user와 다양한 권한들 사이에서 중개 역할을 할 수 있도록 dbms에서는 role을 제공한다. 이러한 role을 dbms user에게 부여하기 위해서는 grant 명령을 사용하며, role을 회수하기 위해서는 revoke 명령을 사용한다.
      37. 권한 부여 dcl => Grant Select, Update On A_user, TB_A To B_user
      38. 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행하는 "role" 제공
      39. 사용자 Lee가 릴레이션 R을 생성한 후, 아래와 같은 SQL문을 실행하였다. 그 이후에 실행 가능한 SQL은?
        1. Lee : Grant Select, Insert, Delete On R To Kim With Grant Option;
        2. Kim : Grant Select, Insert, Delete On R To Park;
        3. Revoke Delete On R From Kim;
        4. Revoke Insert On R From Kim CasCade;
        5. 결과 => insert권한을 cascade했으므로, 권한이 있었던 kim과 park까지 권한이 취소됨.
        6. 보기
        7. Park : Select * From R Where A=400; (O)
        8. Park : Insert Into R Values(400,600); (X) 
        9. Park : Delete From R Where B=800; (O)
        10. Kim : Insert Into R Values(500,600); (X)
      40. PL/SQL 특징 <oracle> 
        1. Block 구조, 기능별 모듈화 가능
        2. 변수, 상수 선언해서 문장 간 값을 교환함
        3. if, loop 등의 절차형 언어 사용 => 절차적 프로그램 가능
        4. dbms 정의 에러나 사용자 정의 에러 정의해서 사용 가능
        5. oracle과 pl/slq을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음
        6. 응용 프로그램의 성능을 향상시킨다.
        7. 한번에 block 전부를 서버로 보내기 때문에, 통신량을 줄일 수 있다.
        8. procedure, user defined function, trigger 객체를 pl/sql로 작성할 수 있다. 
        9. pl/sql로 작성된 procedure, user defined function은 전체가 하나의 트랜잭션으로 처리되어야.. 분할가능
        10. 또한 프로시저 내에서 또 다른 프로시저를 호출한 경우, 호출 트랜잭션과 별도로 'pragma autonomous_trainsaction'을 선언하여 자율 트랜잭션 처리도 가능
        11. 변수,상수 등을 사용하여 일반 sql문장을 실행할 때 where절의 조건 등으로 대입할 수 있다.
      41. rollback이 불가능하도록 삭제하려고 한다. 다음 중 빈칸에 들어갈 내용은? <PL/SQL>
        1. Execute Immediate 'Truncate Table Dept';
        2. 오답 : truncate table dept;
      42. 절차형 sqp모듈에 대한 설명으로 부적절한 것은?
        1. 저장형 프로시저는 sql을 로직과 함께 데이터베이스 내에 저장해놓은 명령문의 집합
        2. 저장형 함수(사용자 정의 함수)는 단독적으로 실행되기보다는 다른 sql문을 통하여 호출되고 그 결과를 리턴하는 sql의 보조적인 역할을 함
        3. 데이터의 무결성과 일관성을 위해서 사용자 정의함수 트리거를 사용한다.
      43. 트리거는 tcl을 이용하여 트랜잭션을 제어할 수 없다. 프로시저와 달리 commit, rollback과 같은 tcl을 사용X / 트리거는 데이터베이스에 로그인하는 작업에도 정의할 수 있다. 
      44. 특정 테이블에 Insert, Update, Delete와 같은 dml문이 수행되었을 때, 데이터베이스에서 자동으로 동작되도록 작성한 저장프로그램은 "트리거 "이다. (테이블,뷰,db작업을 대상으로 정의 가능)

2-3. SQL 최적화 원리 문제풀이 오답

  1. 테이블 및 인덱스 등의 통계정보를 활용하여 sql문을 실행하는데 소요될 처리 시간, CPU, I/O자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저를 "비용 기반 옵티마이저(Cost-Based Optimizer [CBO])"라 한다.
  2. 실행 계획을 통해서 알 수 있는 정보로 가장 부적절한 것은?
    1. 엑세스 기법
    2. 질의 처리 예상 비용
    3. 조인 순서
    4. 실제 처리 건수 (=예상 정보) : 실행계획이 아닌, 트레이스 정보를 통해서 알 수 있다.
  3. 실행계획을 읽는 순서는 안에서 밖으로, 위에서 아래로
    1. Nested Loops
    2.   Hash Join
    3.     Table Access (Full) Tab1
    4.     Table Access (Full) Tab 2
    5.   Table Access ( By Row ID) Tab3
    6.       Index (Unique Scan) Pk_Tab3
    7. 순서 : 3. Table Access => 4. Table Access => 2. Hash Join => 6. Index (Unique Scan) => 5. Table Access By Row Id => 1. Nested Loops
  4. 실행계획에 대한 설명으로 부적절한 것은?
    1. 실행계획은 sql처리를 위한 실행절차와 방법을 표현한 것
    2. 실행계획은 조인 방법, 조인 순서, 액세스 기법등이 표현됨
    3. 동일 sql문에 대해서 실행계획이 다르면 결과도 달라질 수 있다. 없다.
    4. cbo실행 계획에는 단계별 예상비용 및 건수 표시
  5. sql 처리흐름도 => 실행시간 알 수 X / 엑세스 기법이 표현됨 / 실행계획과 관련 / 내부적 처리절차 시각화
  6. sql 처리흐름도 => 성능적인 측면의 표현은 고려하지 않는다.한다.
  7. 규칙 기반 옵티마이저에서 제일 높은 우선순위는 행에 대한 고유 주소를 사용하는 방법이다.
  8. sql 처리흐름도는 인덱스 스캔 및 전체 테이블 스캔 등의 엑세스 기법을 표현할 수 있다.
  9. 인덱스 범위 스캔은 항상 여러건의 결과가 반환된다.(X)
  10. 기본 인덱스에 널 값들이 나타날 수 없다. 기본 인덱스는 'Unique & Not Null'조건을 가진다. 
  11. 보조 인덱스는 Unique 아닐 시 중복 데이터 입력이 가능하다.
  12. 자주 변경되는 속성은 Update, Delete 속성에 악영향
  13. 관계 데이터베이스인덱스에 대한 설명으로 부적절한 것은?
    1. 테이블의 전체 데이터를 읽는 경우는 인덱스가 거의 불필요
    2. 인덱스는 조회, 삽입, 삭제, 갱신 연산 속도 향상시킴. (X, 인덱스는 "조회"만을 위한 오브젝트로, 조회빼고 성능↓)
    3. B트리는 관계형 데이터베이스의 주요 인덱스 구조
    4. 대량의 데이터 삽입시, 모든 인덱스를 생성하고 데이터를 입력하는 것이 좋다.(=FULL SCAN이 낫다.)
  14. 브랜치 블록과 리프 블록으로 구성, 브랜치블록은 분기를 목적으로하고 ,리프블록은 인덱스를 구성하는 컬럼의 값응로 정렬된다. 일반적으로 OLTP 시스템환경에서 많이 사용 => B-TREE 인덱스
  15. 인덱스의 리프 페이지가 곧 데이터 페이지이며, 모든 데이터는 인덱스 키 컬럼 순으로 물리적으로 정렬되어 저장 => Clustered 인덱스
  16.  알 수 없는 경우인 DW 및 AD-HOC 질의 환경을 위해서 설계되었으며, 하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하고 있는 구조이다. => 비트맵 인덱스
  17. 인덱스는 인덱스 구성 칼럼으로 항상 오름차순으로 정렬된다(X)
  18. 비용 기반 옵티마이저는 인덱스 스캔이항상 유리하다고 판단한다.(X)
  19. 규칙 기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려 한다.
  20. 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.
  21. <INDEX 생성> Create Index IDX_EMP_01 On EMP (Regist_date, Deptno); / 실행 => deptno는 동등조건으로 찾고, regist_date는 범위 조건으로 찾음
    1. 실행된 sql에 대해서 인덱스 비효율이 있어, 인덱스의 컬럼을 deptno + regist_date순으로 변경할 필요가 있다.(동등조건 다음 범위조건이니까 O)
    2. idx_emp_01 인덱스를 이용하여 deptno = 47 조건을 효율적으로 탐색할 수 없다. 그 이유는 Register_Date 조건은 범위 조건, Dept_No이 후행컬럼이라서 효율적인 조건 검색을 할 수 없다.
    3. regist_date 컬럼에 대한 조건을 범위 검색이 아닌, 동등검색 조건으로 변경하면 idx_emp_01 인덱스를 효율적으로 활용할 수 있다.
    4. idx_emp_01 인덱스는 (=B-TREE 인덱스) 일반적으로 테이블 내의 데이터 중 10% 이하의 데이터를 검색할 때 유리하다.
  22. 인덱스는 Insert, Update, Delete등의 DML 처리 성능을 저하시킬 수 있다.
  23. B-트리 인덱스는 '일치 및 범위 검색에 적절'한 구조이다.
  24. 테이블 전체 스캔은 많은 양을 읽을 때 유리하다.
  25. 인덱스는 insert와 delete작업과 다르게, update 작업에서는 부하가 없을 수도 있다.<= 인덱스를 구성하는 컬럼 데이터의 데이터가 update 될 때에는 인덱스에 의한 부하가 발생하지 않는다. 
  26. <sql server.의 클러스터형 인덱스는 <oracle>의 IOT와 매우 흡사하다.
  27. 인덱스를 활용하여 데이터르르 조회할 때 인덱스를 구성하는 컬럼들의 순서는 SQL실행 성능과 관계있다.
  28. <oracle>의 규칙 기반 옵티마이저에서 가장 우선순위가 높은 규칙은 'signle row by rowid'엑세스 기법이다.
  29. 비용 기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통계 정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 SQL의 실행계획이 달라질 수 있다. 
  30. 다양한 JOIN 기법 중 NL Join은 OLTP의 목록처리 업무에 많이 사용하며, Hash Join은 DW등에서 데이터를 집계하는 업무에 많이 사용된다.
  31. Nested Loop Join 
    1. 조인 컬럼에 적당한 인ㄷ게스가 있어서 자연조인이 효율적일 때 유용
    2. Driving table의 조인 데이터 양이 큰 영향을 주는 조인 방식
    3. soft merge join하기에 두 테이블이 너무커서, 소트 부하가 심할 때 유용(x) =>그건 Hash Join
    4. 유니크 인덱스를 활용하여 수행 시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용
  32. 해싱 기법을 이용하여 조인을 하는 해시 조인은 한 쪽 테이블이 주 메모리의 가용메모리에 담길 정도로 충분히 작고, 해시 키 속성에 중복값이 적을 때 효과적이다. 더 효과적일 수 있는 조건으로 부적절한 것은?
    1. 조인 컬럼에 적당한 인덱스가 없어 자연조인이 비효율적일 때
    2. 자연조인시 드라이빙 집합 쪽으로 조인 엑세스량이 많아 random 엑세스 부하가 심할 때
    3. 소트 머지 조인을 하기에는 두 테이블이 너무 커서 소트 부하가 심할 때
    4. 유니크 인덱스를 활용하여 수행시간이 적게걸리는 소량테이블을 조회하는 경우(X) => NL조인이 적합
  33. NL Join은 선택도가 낮은(결과 행의 수가 적은) 테이블이 선행 테이블로 선택되는 것이 일반적으로 유리하다.
저작자표시

'SQL > SQLD' 카테고리의 다른 글

SQLD 2과목 (1. SQL기본)  (0) 2022.08.30
SQLD 1과목  (0) 2022.08.29
Day15 SQLD  (0) 2022.08.28
Day14 SQLD  (0) 2022.08.25
Day13 SQLD  (0) 2022.08.24
'SQL/SQLD' Related Articles more
Comments