SQLD2
🔥SUMMARY
2장. SQL 기본 및 활용
데이터 유형(SQL Server)
-Varchar : 가변 길이 문자형 (Oracle 에서는 Varchar2) -Char : 고정 길이 문자형 » 서로 길이가 다를 경우에도 같은 내용으로 판단(짦은 쪽에 스페이스 추가)
- Numberic : 숫자형 데이터 (Oracle 에서는 Number)
- Date : 날짜 데이터
*오라클 데이터베이스에서 날짜 조회 시, TO_CHAR(날짜) 사용.
*ORDER BY 절 안에 NULLS LAST 는 NULL 값을 마지막에 정렬 시킬 수 있음. *AND 200 BETWEEN Col1 AND Col2 » Col1 <= 200 <= Col2 *NOT IN 안에 NULL이 들어가면 아무것도 출력되지 않음. » 이때 COUNT는 0 출력
LIKE 연산
- LIKE 연산으로 문자 검색 시, ESCAPE(특수문자) 명령어 사용 가능
인덱스의 특징
-순차 인덱스, 결합 인덱스, 비트맵, 클러스터, 해시 인덱스 가 있음. -varchar, char, date, number 모두 인덱스 생성 가능 -파티션 테이블은 파티션 키에 대한 인덱스 생성 가능 (=글로벌 파티션 인덱스)
테이블의 모든 데이터 삭제
1) Delete From 테이블명; -테이블의 모든 데이터 삭제 -데이터 삭제되도 데이터의 용량은 감소안함. 2) Truncate Table 테이블명; -테이블의 모든 데이터 삭제 (특정 행 삭제x) -데이터 삭제되면, 데이터 용량 초기화 ** 테이블의 구조를 포함한 데이블 자체가 삭제되려면 ,Drop 을 사용해야 함.
트랜잭션 특징
- 원자성, 일관성, 고립성, 지속성
Commit과 Rollback 의 장점
- 데이터 무결성 보장, 논리적 연관된 작업 그룹핑, 영구적인 변경 전 데이터 변경 사항 확인 가능
SavePoint 커밋 위치
- SavePoint가 같은 이름으로 중복될 경우, 맨 뒤에 있는 SavePoint는 커밋 가능.
Equl Join 성립 조건
-기본키, 외래키 관계에 의해서
- 조인 칼럼이 1:1로 맵핑이 가능할 경우,
Non-Equl 조인
: >, <, >=, <= 사용
상호 연관 서브쿼리(Correlated 서브쿼리)
-서브쿼리가 메인쿼리의 행 수 만큼 실행되는 쿼리 » 속도 느림. -메인쿼리 값을 서브쿼리가 사용ㅇ하고, 서브쿼리의 값을 받아서 메인쿼리가 계산되는 쿼리
서브쿼리가 메인쿼리의 절이 됨.
계층적 분류를 포함하고 있는 데이터 집계에 적합한 GROUP함수
: ROULLUP, CUBE
절차형 SQL을 이용하여 주로 생성하는 것
: Procedure, Trigger, User Defined Function
PL/SQL
-내부에서 테이블 생성 가능 (임시 테이블로 사용함.) -조건문 IF ~ WHEN ~ ELSEIF ~ END IF 와 CASE ~ WHEN 사용 -변수에 값 대입 시 “ := “ 사용
PL/SQL 의 사용 순서
- ( “< 커서명 >” 으로 사용)
- 커서 선언 -> 커서 열기(Open) -> 커서 인출(Fetch) -> 커서 닫기(Close)
절차형 SQL(PL/SQL 또는 T-SQL)
: 복잡한 로직을 처리할 수 있는 강력한 도구
- 프로시저(Procedure), 함수(Function), 트리거(Trigger), 패키지(Package) 등으로 구성됨.
1) 프로시저(Procedure) » 복잡하거나 여러 쿼리를 실행해야 하는 경우 유용 -SQL문을 하나의 단위로 묶어서 실행하는 명령어 2) 함수(Function) » 특정 작업을 수행하고 결과 반환 -값을 반환하는 SQL 코드 블록 3) 트리거(Trigger) » 이벤트 발생 시 실행 -특정 이벤트(INSERT 등) 발생 시, 자동으로 실행되는 SQL 코드 4) 패키지(Package) » 코드의 재사용성 및 유지보수성 높임 -관련된 프로시저 및 함수들을 하나의 단위로 묶은 객체 5) 에러 핸들링(Exception Handling) -Exceotuib 블록을 사용하여 예외처리 및 오류 대응 조치 취하기 6) 배치 처리(Batch Processing) -많은 데이터 한번에 처리하는 배치 작업
프로시저와 트리거
-공통점 : 생성하면 소스코드와 실행 코드가 생성됨. 1) 프로시저 -생성방법 : Create Procedure -실행방법 : Execute -Commit, Rollback 실행O 2) 트리거 » 동시성 제어 문제 발생 -생성방법 : Create Trigger -실행방법 : 자동실행됨. -Commit, Rollback 실행X
PL/SQL 의 특징
-Block 구조로 되어 있어 각 기능별로 모듈화 가능 -변수, 상수 등을 선언하여 SQL 문장 간 값을 교환함 -IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함. -DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능 -Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있음 -응용 프로그램의 성능을 향상시킴 -여러 SQL 문장을 Block으로 묶고 한번에 Block 전부를 서버로 보내기 때문에 통신량을 줄 일 수 있음. -PL/SQL 기본 구조 : BEGIN ~ END 필수 // DECLARE, EXCEPTION은 선택 사항
데이터베이스의 동시성 제어를 위한 방법
: lock
빌트인 함수(Built-in Function)
: 별도의 정의없이 자동으로 생성되는 함수
유저 정의 함수(User Defined Function)
: 사용자 정의 함수» 프로시저와 함수로 구분됨.
옵티마이저(Optimizer)
-정의: 쿼리 실행 시, 여러 실행 계획을 분석하고 가장 효율적인 방법을 선택하는 것 -목적: 쿼리의 실행 시간 최소화 -대부분 비용 기반 옵티마이저 방식 사용
옵티마이저 종류
1) 규칙 기반 옵티마이저(Rule-based Optimizer, RBO) : 설정된 규칙 기반 실행 -적절한 인덱스가 존재하면, 테이블 스캔보다 인덱스 사용이 우선적. -인덱스는 내림차순으로 생성 및 정렬됨. (인덱스 범위 스캔은 결과 건수만큼 반환됨.) -가장 낮은 우선 순위는 전체 테이블 스캔 임. 2) 비용 기반 옵티마이저(Cost-based Optimizer, CBO) : 가장 많이 실행 -각 실행 계획의 예상 비용 계산 후, 가장 적은 비용을 가진 실행 계획 선택 -비용적인 측면에서는 전체 테이블 스캔이 유리할 수 있음 -디스크 I/O 와 CPU 사용량 등을 고려하여 비용 계산 -인덱스 및 테이블 스캔 등 상관없이 비용 기준으로 실행 3) 혼합형 옵티마이저(Hybrid Oprimizer) : RBO + CBO
실행 계획
-SQL문의 처리를 위한 절차와 방법이 표현됨 -실행 계획은 액세스 기법, 조인 순서, 조인 방법 등으로 구성됨 -최적화 정보는 실행 계획의 단계별 예상 비용을 표시한 것 -실행 계획이 달라도 결과는 동일
실행 계획 내 확인할 수 있는 컬럼
-Cardinality : 실행 결과로 조회되는 행의 수
*인덱스는 변형이 일어나면 실행되지 않음.
카텐시안 곱 발생 조건
1) 조인 조건이 없을 때 (Where 이나 on 없을 경우 발생) -ex) SELECT * FROM tableA,tableB; 2) 조인 조건이 잘못 설정되었을 때 -ex) SELECT *FROM tableA JOIN tableB ON tableA.id = tableB.other_column; 3) 크로스 조인(CROSS JOIN) 사용 시 -ex) SELECT * FROM tableA CROSS JOIN tableB;
Intersect
: 양쪽 모두 포함된 행을 검색 (중복된 행을 하나의 행으로 표시) » 교집합
차수(Degree)
- 결과 릴레이션의 칼럼 수(+) ex. 학생 테이블에서 해당되는 컬럼 수 5개 + 학과 테이블에서 해당되는 컬럼 수 3개=8
카디널리티(Cardinality)
- 선택된 행들의 개수(*) ex. 학생 테이블에서 선택된 컬럼 수 3개 * 학과 테이블에서 해당되는 컬럼 수 3개=9
조인
-From 절에 여러 테이블의 경우, 항상 2개씩 조인됨.
*직접 조인 시, 카테시안 곱 발생.
해시 조인
: CPU 많이 사용 (*SQL 실행 계획에서 이너 조인으로도 받아들이기 가능)
Nested Loop 조인 (중접 루프 조인)
-작은 데이터셋에서 효과적 -온라인 트랜잭션 처리(OLTP)에 유용 » 먼저 나오는 테이블의 선택도가 낮은 테이블을 참조하는 것이 유리함. -적절한 인덱스를 찾기 위해 랜덤 엑세스(내부 테이블을 반복적으로 접근) 발생 -조인 컬럼의 인덱스가 존재해야 함. -대용량 데이터를 조인할 때, 후행 테이블에 인덱스가 없으면 중첩루프 조인 사용x
Natual 조인
-두 테이블 간에 동일한 칼럼 이름을 가진 것만 출력하는 방법 -Alias(별칭부여) 불가능 -두 테이블에서 동일한 칼럼 이름을 가지는 칼럼은 모두 조인됨. -동일한 컬럼이 두 개 이상일 경우, Join~Using 문장을 사용 -이너 조인에 속함
Natual 조인 과 Cross 조인
: Cross 조인은 WHERE절에서 조인 조건 추가 가능 (Natual 은 불가)
하지만, 이 경우 Inner 조인과 같은 결과를 얻기 때문에 사용하는 의미x
Sort Merge 조인
-Equal 조인과 Not Equal 조인 모두 사용 가능 -두 개의 테이블을 Sort(정렬) 후에 병합(Merge)하여 조인 -Merge 완료 후 한 번의 Full Scan으로 데이터 검색 -정렬된 결과들을 통해 조인 작업이 수행되며 조인에 성공하면 추출 버퍼에 넣는 작업을 수행함
Sort Merge 조인이 Nested Loop 조인보다 효율적인 경우
-기본키와 외래키 관계에서 외래키에 인덱스가 없을 때
Self 조인
-동일한 테이블 내 두 개의 컬럼 간에 조인을 의미 -From 절에 동일한 테이블명이 두 번 이상 나타나므로 별칭(Alias)
순수 관계 연산자
: Select, Join, Divide, Project
PL/SQL 에서 변수에 값 넣기
: Select 칼럼명 INTO ~
*집계 함수(COUNT 등)에서 조건절이 거짓일 때는 0 반환
NULL 관련 함수
1) NVL(컬럼, null일 때 값) 2) NVL2(컬럼, null 아닐 때 값, null 일 때 값) 3) NULLIF(exp1, exp2) : exp1과 exp2가 같으면 NULL , 다르면 exp1 반환 4) COALESCE(컬럼,null이 아닐 때 값) ex. COALESCE(1,2,3) 은 1 값 나옴. 5) DECODE(컬럼, exp1, 컬럼과 exp1 같을때 값, 다를때 값)
*NVL(컬럼,null인값) 함수: 인덱스가 걸려있는 칼럼에 사용할 경우, 칼럼의 값이 조작되므로 인덱스 사용 불가 » 컬럼이 null 인 경우, pk인 인덱스가 0이 되므로 조작됨!
SQL문에서 null
*SQL문에서 null과 null 비교X » 공집합 나옴 *SQL문에서 null 끼리 곱하면 » null이 나옴.
*그룹 함수(ex.SUM) 에서는 NULL값이 연산에서 제외됨
즉, 그룹 합수에서 NVL사용은 합계오류 예방과는 무관.
*Order by : SQL Server에서 오름차순으로 정렬하면, Null이 가장 먼저 나옴. *Case~When~Then~Else~End 에서 Else 가 생략된다면? » Null 이 나옴.
*null 값이면, SQL Server 는 인덱스 맨 앞에 저장하고, Oracle은 맨 뒤에 저장함. *인덱스 구성 칼럼이 모두 null인 레코드는 SQL Server 에서는 모두 저장하지만, Oracle 에서는 저장하지 않음.(하나라도 null이 아니면 저장)
Distinct
: 중복제거
Decode
- 형식: Decode(컬럼명, 지정 값, 컬럼명=지정값 같으면 해당 값, 다르면 해당 값)
Coalesce
- 형식: Coalesce(null이 아닐 경우 지정값, null일 경우 지정값, 2번째에서도 null 일 경우 디폴트값) ex. col1 (100,null,null) , col2 (100,60,null) SELECT COALESCE(col1,col250,50) » (1) col1 중에 null 이 아닌 것은 col값 그대로 유지 » 10 » (2) null 값들 ‘col250’ 공식 적용 »3000, null » (3) 최종 null 값들에 대한 디폴트값 적용 » 50 =»즉, 답은 (10,3000,50)
DUAL 테이블
- DUAL 테이블사용 시, 조회되는 데이터 타입이 다르면 실행X(오류 발생)
LPAD
- 형식: LPAD(대상 문자, 지정길이, 채울문자)
Case~When~Then~Else~END
*Case [표현식] When 조건1 Then 결과1 When 조건n Then 결과n … Else 결과 END
그룹 연산자
*Rollup, Cube, Groupingsets GROUP BY Rollup(컬럼A, 컬럼B) : ((A,B),(A),()) = GROUP BY GRoupingsets((A,B),(A),())
GROUP BY Cube(컬럼A, 컬럼B) : ((A,B),(A),(B)()) = GROUP BY GRoupingsets((A,B),(A),(B)())
*Where, Select 절에서 집계함수(ex. avg, sum 등) 사용 못함 *멀티 행을 반환하는 서브쿼리는 ‘=’절로 받기 못함
데이터 스캔 방식
- 기본키 내 형변환 발생 시, Full Scan 됨.
- Index Range Scan 이 불가능한 조건 : ‘%’ 기호가 들어가면 불가능
1) 테이블 풀 스캔(Table Full Scan) -주로 인덱스 조건이 없을 경우 사용 -모든 데이터를 읽으므로 비효율적 2) 인덱스 풀 스캔(Index Full Scan) 3) 유니크 인덱스 스캔(Unique Index Scan) -유니크 인덱스: 중복된 값이 없는 인덱스 -검색 조건이 정확히 일치하는 행을 찾을 경우 사용(속도 빠름) 4) 논 유니크 인덱스 스캔(Non-Unique Index Scan) -논 유니크 인덱스: 중복된 값이 있는 인덱스 -중복된 여러 행을 찾을 때 사용
인덱스 튜닝(=인덱스 최적화 작업)
-인덱스를 경유한 테이블의 Random 액세스 부하가 심할 때, 클러스터 테이블이나 IOT를 활용하는 방안을 고려할 수도 있음 -인덱스를 경유한 테이블 액세스 횟수가 같더라도 인덱스 구성에 따라 스캔 효율이 달라짐. (인덱스 스캔 효율을 높이기 위해 인덱스 칼럼 순서를 바꿔야 하는 경우도 종종 있음) -인덱스 칼럼 순서를 아무리 바꿔도 테이블 Random 액세스 횟수를 줄지 않음.(변동없음) -조건절 순서 변경은 인덱스 스캔 효율에 차이 없음.
인덱스 튜닝(=인덱스 최적화 작업) 전략
1) 적절한 인덱스 추가: 자주 조회되는 컬럼에 인덱스 추가 (ex. Where, Order by, Group by 절에서 사용) 2) 복합 인덱스 사용: 여러 컬럼 동시에 사용하는 쿼리가 자주 실행될 경우 3) 인덱스 리빌드 및 리오르기: 인덱스 단편화 현상으로 인한 성능 저하가 발생할 경우 4) 불필요한 인덱스 제거 5) 쿼리 최적화 6) 통계 정보 업데이트: 옵티마이저가 적절한 실행 계획을 세우도록 도와줌
*ROWID : Oracle 데이터베이스 내에서 데이터를 구분할 수 있는 유일한 값 *ROWNUM : Select 문의 최종 결과 집합에 부여되는 가상의 일련 번호
윈도우 함수
-Between ~ And 윈도우의 시작과 끝 위치 지정 가능 -Range 논리적 주소에 의한 행 집합 지정 -Rows 물리적 단위로 행 지정
-Unbounded preceding 시작 위치가 첫번째 행 (처음부터) -Unbounded following 마지막 위치가 마지막 행 (끝까지) -Current row 시작 위치가 현재행
윈도우 함수(순위 함수)
-Rank() : 1,1,3,4 등 -Dense_Rank() : 1,1,2,3 -Row_Number() : 1,2,3,4
윈도우 함수(집계함수) : SUM, AVG, COUNT, MIN, MAX
윈도우 함수 (행 순서 관련)
-First_Value : 가장 처음 나오는 값 (MIN 함수 결과와 같음) -Last_Value : 가장 마지막에 나오는 값 (MAX 함수 결과와 같음) -LAG : 이전 행 가져옴 -LEAD(몇번째 뒤, 디폴트값) : 특정 위치 행 가져옴 (기본값 1) (ex. LEAD(2,9) : 시작 점이 1행이면, 2번째 뒤인 3행의 값 가져옴. 기본값은 9)
윈도우 함수(비율 관련)
-Cume_Dist : 0~1 사이의 누적 배분율 -Percent_RANK : 0~1 사이의 순서별 백분율 -Ntile(숫자) : N등분 (시작 값은 1) -Patio_To_Report : SUM(칼럼) 기준으로 소수점까지 나오는 백분율
계층형 쿼리문의 내장 함수
-특정 값을 “검색” 할때 WHERE 절 사용 -Level : 검색 항목의 깊이를 의미. » 즉, 계층 구조에서 가장 상위 레벨이 1이 됨. -Connect_By_root : root 노드의 정보 표시 » 가장 최상위 값을 표시 -Connect_By_Isleaf : leaf 데이터면 1, 아니면 0을 가짐 » 가장 최하위 값을 표시 -Connect_By_Iscycle : root까지의 경로에 존재하는 데이터 의미 » 발생 지점 표시 -Sys_Connect_By_Path : 하위 레벨의 컬럼까지 모두 표시 » 전체 전개 경로 표시 -Nocycle : 순환 구조가 발생 지점까지만 전개함.
*WHERE DATA1 <> 4 : 4 제외
- Count() OVER (Order By SAL
- Range Between 10 PRECEDING And 150 FOLLOWING)
- SAL을 기준으로 현재 행에서의 SAL의 -10 에서 +150 사이의 급여를 가지는 행의 수 계산
Rownum
: Select문에서 행이 인출될 때 행에 부여되는 일렬번호
*서브쿼리는 Order By절에서 사용 가능
위치에 따른 서브쿼리 종류
1) 스칼라 서브쿼리 : Select 문 내 사용 » 한 행, 한 컬럼을 반환 2) 인라인 뷰 : From절 내 사용 » 메인 쿼리에서 사용이 가능 3) 서브쿼리 : Where절 내 사용
*메인쿼리는 서브쿼리의 칼럼 사용 불가 » 이때는 스칼라 서브쿼리를 사용해야 함.
SQL 개선 측면에서의 서브쿼리 종류
1) Acess 서브쿼리 : 쿼리 변형X, 제공자의 역할 2) Filter 서브쿼리 : 쿼리 변형X, 확인자의 역할 3) Early Filter 서브쿼리 : 쿼리 변형X, 서브쿼리 먼저 실행되므로 데이터 걸러냄.
집합 연산자 종류
-집합 연산자(2개 이상의 테이블에서 조인없이 데이터 조회하는 것) -Union : 합집합, 중복 제거O -Union All : 합집합, 중복 제거X -Intersect : 교집합 -Except : 차집합 (=Oracle에서는 MINUS 사용)
단일행 숫자형 함수
-ABS(숫자) : 절대값 -SIGN(숫자) : 양수, 음수, 0을 구분
-CEIL/CEILING(숫자) : 올림 -FLOOR(숫자) : 내림
-ROUND(숫자,m) : 소수점 m자리 까지 반올림(m의 기본값은 0) -TRUNC(숫자,m): 소수점 m자리 까지 내림(m의 기본값은 0)
DDL
: CREATE, ALTER, DROP, RENAME