Dirty 블록을 데이터 파일에 기록하기 전에, Redo 엔트리를 Redo 로그 파일에 기록했음이 보장되야 한다.
메모리 영역
SGA (System Global Area) - 공유 메모리 그룹, 모든 서버 및 백그라운드 프로스세에 공유된다. - Shared Pool, 데이터베이스 버퍼캐시, Redo 로그 버퍼, 딕셔너리 캐시
PGA (Program Global Area) - 비공유 메모리 구조 그룹 - 한 프로세스 혹은 스레드의 개별적인 메모리 공간 - 정렬 공간 (Sort Area), 세션 정보 (Session Information), 커서 상태 정보(Cursor State), 변수 저장 공간 (Stack Area)
UGA (User Global Area) - 로그온 정보나 세션이 필요로 하는 정보를 저장하고 있는 세션 메모리 - Shared Server의 경우 SGA 에 존재 - Dedicated Server일 경우 PGA 내부에 존재
CGA (Call Global Area) - 데이터베이스 콜(parse, execute, fetch)에 필요하고, 끝나면 해제되는 데이터를 담고 있다.
Single Block I/O - 기본적인 인덱스 사용 시 단일 블록으로 읽어들인다.
Multi Block I/O - 인접한 블록을 함께 읽어 들인다. - 한 익스텐트 경계를 넘지는 못한다. - Index Fast Full Scan, Table Full Scan 에서 다중 블록으로 읽어들인다.
Direct Path I/O 는 버퍼 캐시를 경유하지 않는다. (병렬 프로세스로 테이블을 Full Scan)
LRU 알고리즘 - 가장 오래전에 사용된 데이터 블록을 버퍼캐시에서 밀어내는 알고리즘 - 사용빈도가 높은 데이터 블록이 버퍼캐시에 오래 남아 있도록 한다.
Shared Pool
빠른 Parsing을 위해 Hard Parsing을 줄이고 Soft Parsing의 비율을 높여 Oracle Database의 성능을 높히는 것
Soft Parsing
- Library Cache에 저장되어 있는 SQL문을 수행하여 Parse 작업없이 기존에 있는 실행계획대로 실행
Hard Parsing
- Parsing 작업을 수행하여 SQL문 확인 및 실행계획을 새로 생성함
Library Cache
- Parsing 정보를 저장하는 메모리
- Oracle Instance SGA Shared Pool 영역에 존재 - SQL 과 실행계획, PL/SQL 프로그램(프로시저, 함수, 트리거 등) 캐싱
CASE 문 - case expr when 1 then '참' else '거짓' end : expr 이 1이면 참 아니면 거짓 - case when expr <= 10 then '참' else '거짓' end : expr 이 10 이하면 참, 아니면 거짓
GROUP BY / HAVING - 집계함수 Count 에서 데이터가 없으면 0 이 나오지만, having 절에 의해 데이터가 없으면, no records 이다.
ORDER BY - 기본정렬은 오름차순 ASC 이고, 내림차순은 DESC 이다. - 오라클은 NULL 이 가장 큰 값으로 간주하여, 오름차순에서 가장 아래에 위치한다. - MSSQL 은 NULL 이 가장 작은 값으로 간주하여, 오름차순에서 가장 위에 위치한다.
SELECT 실행순서
순서
쿼리
5
SELECT
1
FROM
2
WHERE
3
GROUP BY
4
HAVING
6
ORDER BY
TOP N 쿼리
MSSQL select top 10 * from order by col1
Oracle select * from ( select * from order by col1 ) where rownum <= 10
INNER JOIN - 조건에 일치하는 행만 반환한다. from A inner join B on A.col1 = B.col1 from A join B on A.col1 = B.col1 from A, B where A.col1 = B.col1
- using 사용 from A join B using(col1)
OUTER JOIN - Join 조건에 일치하는 행과, 일치하지 않는 행도 반환한다.
left outer join
from A left outer join B on A.col1 = B.col1 from A left join B on A.col1 = B.col1 from A, B where A.col1 = B.col1(+)
- 조건에 일치하는 행과 왼쪽 테이블 기준으로 오른쪽에는 일치하지 않는 행도 반환한다. - A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행도 반환
right outer join
from A right outer join B on A.col1 = B.col1 from A right join B on A.col1 = B.col1 from A, B where A.col1(+) = B.col1
- 조건에 일치하는 행과 오른쪽 테이블 기준으로 왼쪽에는 일치하지 않는 행도 반환한다. - A, B 테이블에 일치하는 데이터와, B에만 있고 A에는 없는 행도 반환
full outer join
from A full outer join B on A.col1 = B.col1
- 조건에 일치하는 행과 양쪽 테이블의 조건에 일치하지 않는 행도 반환한다. - A, B 테이블에 일치하는 데이터와, A에만 있고 B에는 없는 행, B에만 있고 A에는 없는 행도 반환
NATURAL JOIN - A, B 테이블에 같은 이름의 모든 컬럼이 자동으로 연결된다. - SELECT 절에 EMP.col 테이블명을 지정해서 쓰면 오류가 발생한다.
from A natural join B
CROSS JOIN - join 조건이 없는 경우, 테이블간에 모든 데이터 조합으로 행을 반환한다. - M * N 의 카티션 행 조합
from A cross join B
집합 연산자
UNION - 합집합, 중복데이터는 제외
UNION ALL - 합집합, 중복데이터 유지
INTERSECT - 교집합, 결과에서 중복데이터는 제외
EXCEPT (오라클 MINUS) - 차집합 , 결과에서 중복데이터는 제외 - A 에서 B 의 데이터를 제외한 결과
계층형질의
START WITH CONNECT BY
조직도와 같은 Tree 구조의 데이터를 조회할 때 사용한다.
가상컬럼 level : 해당 행이 ROOT 데이터이면 1, 하위 데이터이면 2, 그 하위이면 +1 씩 증가 connect_by_isleaf : 해당 행이 LEAF 데이터 이면 1, 아니면 0 connect_by_iscycle : 중복 참조이면 1, 아니면 0 : 현 데이터의 하위 데이터가, 하위 데이터의 현데이터로 지정한 경우, 무한 루프에 빠지게 된다.
select level, ( lpad(' ', 4 * (level-1)) || empno ) as empno , connect_by_isleaf as isleaf from emp start with empno is null connect by prior mgr = empno;
start with 조건에는 root 노드를 지정하고 connect by 에는 하위노드를 찾는 방법을 지정한다.
prior 에 지정된 컬럼에서 상대편 컬럼으로 찾아간다.
CONNECT BY PRIOR 하위 컬럼 = 상위 컬럼 : 상위 -> 하위로 가는 순방향 전개 CONNECT BY PRIOR 상위 컬럼 = 하위 컬럼 : 하위 -> 상위로 가는 역방향 전개
ORDER SIBLINGS BY - order by 와 같이 정렬을 수행하나, 같은 level 간의 정렬 순위를 지정한다.
NOCYCLE - connect by NOCYCLE prior mgr = empno - 데이터 입력실수로, 이전에 전개된 데이터가 다시 나타나는 현상이 발생하면 무한루프에 빠지게 된다. - 이전에 전개된 데이터가 다시 나오면 전개를 멈추는 옵션.
서브쿼리 - 괄호로 묶인 SELECT 절
단일행 서브쿼리 - 결과행이 1개만 나오는 서브쿼리 - select 쿼리 안에 단일 값 조회시 사용되거나, wehere 절의 비교 연산자 등에 온다.
다중행 서브쿼리 - 결과행이 n 개 나오는 서브쿼리 - from 절의 쿼리에 오거나, where 절의 in , all, any, some, exists 등에 온다.
뷰(VIEW)
독립성 - 테이블구조가 변경되어도, 뷰를 사용하는 응용프로그램은 변경할 필요가 없다. 편리성 - 복잡한 질의나 자주 사용하는 질의를 뷰로 만들면, 쿼리가 간단해 진다. 보안성 - 숨기고 싶은 정보는 제외하고, 정보(컬럼) 를 제공할 수 있다.
그룹 함수
ROLLUP - 지정된 컬럼의 소계와 합계를 구하는 함수 - 인수의 순서에 따라 결과가 달라진다.
group by rollup(dname, job) - (dname, job) 별 소계, dname 별 소계, 전체 합계를 구한다.
CUBE - 가능한 모든 조합의 소계와 합계를 구한다. - 인수의 순서는 상관이 없다.
group by cube(dname, job) - (dname, job) 별 소계, dname 별 소계, job 별 소계, 전체 합계를 구한다.
GROUPING SETS - 소계 / 합계를 직접 선택하여 지정할 수 있는 함수 - () 또는 null 을 쓰면 전체 합계를 구한다.
group by grouping sets( (dname, job), (dname), () ) - 이렇게 쓰면 group by rollup(dname, job) 과 동일하다.
group by grouping sets( (dname, job), (dname), (job), () ) - 이렇게 쓰면 group by cube(dname, job) 과 동일하다.
GROUPING 함수 - select 절에서 해당 행이 소계에 사용된 행인지 여부를 판단할 때 사용한다.
select (case when grouping(job) = 1 then 'JOB합계' else job end ) - 부서별 모든 소계행인경우, JOB합계로 표시하고, 아닌경우 job 명칭을 표시한다.
Window 함수
partition by
sum(sal) over(partition by deptno)
sum(sal) over(partition by deptno group by emp range between unbounded preceding and current row)
rows - 물리적인 결과 행의 수를 지정한다.
range - 논리적인 값에 의한 범위를 지정한다.
n preceding - n 개 앞의 행
unbounded preceding - 첫번째 행
n following - n 개 뒤의 행
unbounded following - 마지막 행
current row - 현재 행
RANGE UNBOUNDED PRECEDING SUM(sal) OVER(partition by mgr order by sal range unbounded preceding) = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 와 동일하다. - 파티션 내에서 첫번째 행 부터 현재 행까지의 합계 즉 누적합을 표시
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING SUM(sal) OVER(partition by mgr order by sal rows between 1 preceding and 1 following) - 현재행+앞에1행+뒤에1행 합계
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING SUM(sal) OVER(partition by mgr order by sal range between current row and unbounded following) - 현재행부터 마지막 행까지 합계
CTE(Common Table Expression) - 일시적인 결과 세트를 정의하고 이를 나중에 쿼리에서 참조할 수 있게 하는 방법
CTE와 서브쿼리의 차이점 - 가독성: CTE는 복잡한 쿼리를 여러 부분으로 나누어 가독성 높여준다. - 재사용성: CTE는 동일한 쿼리 내에서 여러 번 재사용 가능 - 성능: CTE와 서브쿼리의 성능 차이는 데이터베이스 시스템과 쿼리의 복잡도 따라 다름 - CTE는 재귀적 쿼리에 유리
WITH kor_restaurants AS ( SELECT * FROM restaurants WHERE rest_type = 'kor' )
재귀적 CTE
WITH cte (n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n < 5 )
NTILE - 전체건수를 지정한 건수로 N등분
LAG - N개 앞의 행 값 조회
LEAD - N개 뒤의 행 값 조회
NULL 값 - 아직 정해지지 않은 미지의 값
NULL 비교는 IS NULL / IS NOT NULL 을 사용한다.
where in (list) 에서 list 에 NULL 이 있으면, 실제 NULL 인 데이터는 제외 된다. - where ID in (1, 2, NULL) : ID 가 NULL 인 데이터는 제외되고, 1이나 2인 데이터만 조회된다.
where not in (list) 에서 list 에 NULL 이 있으면, 전체가 false 가 되어, 결과집합이 없다.
- where ID not in (1, 2, NULL) : ID <> 1 and ID <> 2 and ID <> NULL => and 조건에 의해 하나라도 false 면 모두 false 이다.
NULL 과의 연산은 NULL 이 된다. NULL + 10 => NULL
집계함수에서 NULL 값이 있다면 제외된다. - SUM, COUNT 등
순위
ROW_NUMBER()
1,2,3 순차적으로 순위를 매김
RANK()
1,1,1,4,5,6 동일 값이면 같은 순위를 갖고, 다음순위는 건너뜀.
DENSE_RANK()
1,1,1,2,3,4 동일 값이면 같은 순위를 갖고, 다음순위로 넘어감.
PIVOT - 행을 열로 변환한다.
SELECT * FROM 테이블 PIVOT ( 그룹합수(집계컬럼) FOR 피벗컬럼 IN (피벗컬럼값 AS 별칭 ... )
SELECT job, d1, d2, d3 FROM emp PIVOT ( SUM(sal) FOR deptno IN ('10' AS d1, '20' AS d2, '30' AS d3) )
UNPIVOT - 열을 행으로 변환한다.
SELECT * FROM 테이블 UNPIVOT ( 컬럼별칭(값) FOR 컬럼별칭(열) IN (피벗열명 AS '별칭', ... )
SELECT col_nm, col_val FROM emp UNPIVOT ( col_val FOR col_nm IN (col1, col2, col3) )
정규표현식
REGEXP_LIKE - Like 연산과 유사하여 정규식 패턴을 검색
REGEXP_LIKE(srcstr, pattern[,match_option])
SELECT A.* FROM REG_TEST A WHERE REGEXP_LIKE(A.DATA, '[0-9]') ;
SELECT A.* FROM REG_TEST A WHERE REGEXP_LIKE(A.DATA, '[[:digit:]]');
숫자가 포함된 데이터 찾기
SELECLT A.* FROM REG_TEST A WHERE REGEXP_LIKE(A.DATA, '^[[:punct:]]*$');
