실행계획이란?
사용자가 SQL을 실행하여 데이터를 추출하려고할 때, 옵티마이저가 수립하는 작업절차(SQL의 성능에 굉장히 중요)
SQL해석 → 실행계획수립 → 실행
[왼쪽 아래가 제일 먼저실행]
실행계획 확인방법
- EXPLAIN PLAN
- 실행계획을 떠서 저장해놓고 확인하는 방식
- SQL에 대한 실행계획만을 확인할 수 있음
- 명령을 사용할때 데이터를 실제로 처리하지않음 (DB에 부하를 주지않기때문에 유용함)
- 다만 SQL문이 여러개면 SQL을 바꿔가면서 작성해야하는 불편함 존재
- 실제로 확인하기위해서는 다시 SELECT를 해야하는 불편함 존재
- 실제로 DB실행이 된것도 아니기때문에 소요시간도 체크 불가능
- SET AUTOTRACE
- EXPLAIN PLAN 명령과는 달리 한번의 명령으로, 여러개의 SQL에 대한 실행계획을 바로 볼수있음
- 다양한 옵션을 사용할수있어서 여러가지의 정보를 선택적으로 확인할수있음 (실제로 실행안되게 한다던지..)
SET AUTOTRACE ON; SELECT /*+ USE_NL(e d) */ e.ename, e.deptno, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;- 명령어 옵션
- SET AUTOTRACE ON; 실행시 출력결과를 화면에 나타내고, 그밑에 실행계획 나타내고, 그아래에 IO & 소팅관련 정보 제공
- 이것만쓰면 전체적으로 다 나오기때문에 보고싶은정보만 추가적으로 선택하는것
- SET AUTOTRACE ON EXPLAIN; 실행시 출력결과를 화면에 나타내고, 그밑에 실행계획까지만 나타낸다
- SET AUTOTRACE ON STATISTICS; 실행시 출력결과를 화면에 나타내고, 그밑에 IO관련 정보를 나타낸다
- SET AUTOTRACE TRACEONLY; 출력결과를 보이고싶지않을때 사용(몇건만됐다는것만 나옴), 그 밑에 IO정보들이 나옴
- SET AUTOTRACE TRACEONLY EXPLAIN; 출력결과를 보여주지않고 그밑에 실행계획만 추출 (데이터를 실제로 안돌림)
- SET AUTOTRACE TRACEONLY STATISTICS; 출력결과를 보여주지않고 그밑에 IO만 추출(데이터 읽어야 IO가 나옴)
- SET AUTOTRACE OFF; 정보를 활용하고싶지않을때
옵티마이저란
사용자가 실행한 SQL을 해석하고, 데이터 추출을 위한 실행계획을 수립하는 프로세스
오라클은 옵티마이저가 2종류가 있음
- RBO - 초장기 버전부터 제공 (기본적으로 15개의 순위가 매겨진 규칙이 있어서 그걸 기준으로 실행계획 수립)
- CBO - 10g부터 기본적으로 제공CBO 성능을 최적상태로 유지시키기위해 테이블, 인덱스, 클러스터 등을 대상으로 통계정보를 생성함 → 정기적으로 ANALYZE 작업을 해야함
- ANALYZE TABLE emp COMPUTE STATISTIC; 테이블이 가지고 있는 전체 데이터를 대상으로 할때 사용
- ANALYZE TABLE emp ESTIMATE STATISTIC; 데이터가 너무 많을때는 표본크기로
- ANALYZE TABLE emp SAMPLE 10 PERCENT;
- ANALYZE TABLE emp ESTIMATE STATISTIC;
- ANALYZE TABLE emp SAMPLE 5 ROW;
SELECT table_name, num_rows, last_analyzed
FROM user_tables
WHERE table_name IN ('EMP', 'DEPT');
오라클같은경우는 package를 통해서도 할수있다.
package를 사용하면 명령어를 사용해서 할때보다 편하게할수있고 oracle 10g이상부터는 작업이 자동화되어있다. (사실상 할필요없다는 뜻인거같음)
옵티마이저 레벨별 설정
- instance level : initSID.ora를 이용하여 지정함 OPTIMIZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]
- 디비 전체차원에서 설정, 10g부터는 ALL_ROWS가 기본설정. 우선순위 제일 낮음
- Session level : ALTER SESSION SET OPTIMIZER_MODE = [RULE/CHOOSE/FIRST_ROWS/ALL_ROWS]
- 작업하는 세션에 한에서만 설정
- Statement level : SELECT /+first_rows/ ename FROM emp;
- SQL문에다가 힌트로 따로 지정해줄수있음. 우선순위 제일 높음
- 옵티마이저레벨별 설정은 3가지가 있다.
- package를 사용하면 명령어를 사용해서 할때보다 편하게할수있고 oracle 10g이상부터는 작업이 자동화되어있다. (사실상 할필요없다는 뜻인거같음)
- SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name IN ('EMP', 'DEPT');
- ANALYZE 실행
- 대상 row들을 처리하는데 필요한 자원사용을 최소화해서, 궁극적으로 데이터를 빨리처리하려고 알아서 최적화 계산을함
오라클을 제외한 나머지는 CBO
RBO와 CBO는 실행계획이 다름.
RBO는 룰이따라서 WHERE절에 상수가 있는 테이블을 먼저 조회함
스캔종류
- full table scan : 테이블의 전체 테이터를 읽어 조건에 맞는 데이터를 추출하는 방식
- rowid scan : rowid를 기준으로 데이터를 추출하는 방식으로 단일행에 접근하는 방식 가운데에서 가장 빠름
- index scan : 원하는 데이터를 추출하기 위해 인덱스를 사용하여 검색하는 방식
full scan이 index scan보다 반드시 느린건 아니다.
인덱스
인덱스를 사용해야하는 이유
- 데이터베이스에 저장된 자료를 더욱빠르게 조회하기 위해 인덱스를 생성하여 사용
- 일반적으로, 인덱스는 테이블의 전체 데이터중 10~15% 이하의 데이터를 처리하는 경우에 효율적이며, 그 이상은 안쓰는게 더 나음
- 기본적으로 B tree구조이므로 소팅이 되어있기때문에 ORDER BY를 안써도됨
- MAX /MIN 을 구하기 쉬움
인덱스는 B Tree구조로 저장됨 (루트 / 브랜치 / 리프 노드로 구성)
데이터는 리프에만 있으며 리프는 소팅이 되어있음 (인덱스를 쓰게되면 ORDER BY를 안써도됨)
// (A,B,C) 로 구성된 인덱스가 있을때 아래는 INDEX RANGE SCAN DESCENDING가 됨.
// 내림차순으로 아예 접근하므로 ORDER BY를 안해도됨 (성능에는 큰차이)
// ORDER BY로 묶여있는 C를 인덱스로 잡아놨기때문
SELECT A, B, C
FROM TT
WHERE A = 160
AND B = '2002'
ORDER BY C DESC;
// (A,B,C) 로 구성된 인덱스가 있을때 아래는 INDEX RANGE SCAN (MIN / MAX)가 됨.
// 인덱스를 타기때문에 바로 가져올수있음
// 인덱스를 구성안하면 조건에 대한 데이터를 다 찾고 소팅을 통해서 찾아나가야함
SELECT MAX(C)
FROM TT
WHERE A = 160
AND B = '2002'
인덱스 선정 절차
- 프로그램 개발에 이용된 모든 테이블에 대하여 access path조사
- 인덱스 컬럼 선정 및 분포도 조사
- 자주사용되는 critical access path결정 및 우선 순위 선정
- 인덱스 칼럼의 조합 및 순서 결정 (각각은 안좋아도 결합하면 좋은 성능의 인덱스가 있을 수 있으니까 체크하라는말)
- 시험 생성 및 테스트 (성능이 좋을것으로 예상되었지만 실제로 안 좋은 인덱스를 걸러내라)
- 결정된 인덱스 기준으로 프로그램 반영
- 적용
주의점
- 기존 sql에 어떤 영향이 있는지 검토를 해야함
- 인덱스 과다 생성으로 인한 dml 작업 속도 (select만 빨라지고 cud는 느려짐)
- 비록 개별 칼럼 분포도가 좋지 않더라도 다른칼럼과 결합하여 자주 사용되고, 갈협한경우에 분포도가 양호하다면 결합 인덱스 생성을 긍정적으로 검토
인덱스 스캔원리
- 조건을 만족하는 최초의 인덱스 row를 찾음
- access된 인덱스 row의 rowid를 이용해서 테이블에 있는 row를 찾음(랜덤액세스)
- 처리 범위가 끝날때까지 차례대로 다음인덱스 row를 찾으면서 2를 반복
- 인덱스 스캔시에는 한번의 i/o가 발생할때마다 한개의 block씩 처리
인덱스 사용 (어떻게하면 인덱스를 사용하게 할수있을까?)
1. 고유(unique) 인덱스의 ' = ' 검색
// empno가 pk임
SELECT * FROM emp WHERE empno = 1234;
2. 고유(unique) 인덱스의 범위 검색
SELECT * FROM emp WHERE empno >= 1234;
3. 중복(non-unique) 인덱스의 범위 검색
CREATE INDEX job_index ON emp (job);
SELECT * FROM emp WHERE job = 1234; // job은 pk가 아니므로 여러건이 들어갈수있으므로 범위검색에 들어감
SELECT * FROM emp WHERE job like '%1234';
4. OR & IN 조건 - 결과의 결합 (OR & IN 은 내부적으로 = 연산 수행)
SELECT * FROM emp WHERE empno IN (1234,235);
SELECT * FROM emp WHERE empno = 1234 OR empno = 235;
5. NOT BETWEEN
SELECT * FROM emp WHERE empno NOT BETWEEN 1234 AND 235; == 사실상 OR임
결합인덱스
여러개의 컬럼을 인덱스로 만듬, 첫번째가 인덱스로 구성되어있는게 성능에 좋다
- 결합인덱스 컬럼선택 기준
- where절에서 and조건으로 자주결합되어 사용되면서 각각의 분포도 보다 두 개 이상의 컬럼이 결합될때 분포도가 좋아지는 컬럼들
- 다른 테이블과 조인의 연결고리로 자주사용되는 컬럼들
- 하나 이상의 키 칼럼 조건으로 같은 테이블의 컬럼들이 자주조회될때, 이러한 컬럼을 모두 포함
- 결합인덱스의 컬럼 순서결정(순서가 굉장히 중요함) - 첫번째를 만족하는 컬럼을 찾고 그중에서 두번째기준을 찾으므로 첫번째부터 범위를 줄이는것이 중요
- where절 조건에 많이 사용되는 칼럼 우선
- =로 사용되는 칼럼 우선
- 분포도가 좋은 칼럼 우선(범위가 좁은것)
- 자주 이용되는 sort의 순서로 결정
결합인덱스 사용가능 예
1. 인덱스 : (aa, bb, cc)
// 이런식으로 인덱스의 첫번째인 aa컬럼이 항상 where절에 포함되어있을때 사용가능
WHERE aa = '1234';
WHERE aa = '1234' and bb = 'shin';
WHERE aa = '1234' and bb = 'shin' and cc = 't12';
2. index skip scanning
결합인덱스의 첫번째 컬럼이 where에 없으면 원래는 결합인덱스를 사용하지않는데, 두번째 컬럼부터 where에 조건으로 기술되어있으면 사용가능
다만 강제로 index skip scanning을 타도록 힌트가 존재함
// 이 힌트를 사용하면 index skip scanning을 탐
INDEX_SS (테이블명 인덱스명)
INDEX_SS_ASC (테이블명 인덱스명)
INDEX_SS_DESC (테이블명 인덱스명)
결합인덱스 = 의미
=이 범위제한 조건을 가지면 속도를 낼수있고, 체크조건이라면 속도를 내지못한다.
인덱스 : (시, 구, 동)
WHERE 시 LIKE '서%'
AND 구 = '강남구' <- 체크조건 (범위를 제한하지 못함)
AND 동 = '역삼동'; <- 체크조건
// 서울시까지는 잘찾는데 역삼동은 바로 찾아내지못함(앞의 조건이 애매하기때문)
WHERE 시 = '서울시' <- 범위제한조건
AND 구 LIKE '강%'
AND 동 = '역삼동'; <- 체크조건
// 앞과 동일하게 서울시까지는 찾지만 앞의조건이 애매해서 역삼동은 바로 찾지못함
WHERE 시 = '서울시' <- 범위제한조건
AND 동 = '역삼동'; <- 체크조건
// 이렇게 짜는것이 성능에 가장좋음
WHERE 시 = '서울시' <- 범위제한조건
AND 구 = '강남구' <- 범위제한조건
AND 동 = '역삼동'; <- 범위제한조건
인덱스 매칭률 향상을 통한 속도개선
매칭률을 높여야함 → where절에서 첫번째 컬럼부터 연속된 칼럼에 대해 상수값을 = 로 비교하는 칼럼의갯수 / 인덱스를 구성하는 칼럼의 총 갯수
인덱스 : (급여연월, 급여코드 사원번호)
// 매칭률 0/3
WHERE 급여연월 LIKE '2016%' // LIKE절이기때문에 범위를 제한하지못함
AND 급여코드 = '정기급여'; // 윗줄이 애매하므로 아랫줄도 범위를 제한하지못함
튜닝후
// 매칭률 2/3
WHERE 급여연월 IN ('201601', '201602', '201603'....) // IN은 내부적으로 = 연산이므로 범위제한
ANS 급여코드 = '정기급여'; // 윗줄이 범위제한이므로 =도 범위제한
인덱스 스캔을 하면 무조건 빠른가?
조건에 의한 처리범위가 넓어짐으로 인해 분포도가 나빠지는 경우가 있는데, 이경우는 full scan하는게 훨씬 나음 (15%이상의 데이터를 찾을때는 안좋음)
인덱스는 한번의 i/o 때마다 1개의 블락만 처리하기때문에 그렇다. (full scan은 한번 i/o때 여러개의 블락을 처리함)
- 인덱스를 못쓰는 경우
- NOT 연산자 (1개를 제외하고 모두니까 과반수를 찾는것이므로)
- IS NULL, IS NOT NULL (인덱스는 NULL을 못집어넣음)
- 옵티마이저의 취사선택 (옵티마이저가 자의적으로 잘못된선택을 할수있음 - Hint로 강제 제어 가능)
- External suppressing (외부적으로 가해지는 suppressing (인덱스로 구성된 컬럼에 변형을 가했을때 사용하지못하는것을 의미))
WHERE SUBSTR(ename,1,1) = 'M'; // ename이 인덱스라도 변형이 가해졌기때문에 사용불가
WHERE job||deptno = 'MANAGER10'; // 문자열결합도 사용불가
WHERE TO_CHAR(hiredate, 'YYYYMMDD') = '20021016'; // DATE변수의 가공도 인덱스사용불가
WHERE hiredate BETWEEN TO_DATE('20021016', 'YYYYMMDD') AND TO_DATE('20021016', 'YYYYMMDD'); // 이렇게 수정해야함
WHERE sal * 12 > 40000; // 이것도 컬럼에 변화가 가해지므로 불가능
WHERE sal > 40000/12 // 이런식으로 바꿔야함
- Internal suppressing(데이터베이스 내부에 스스로 존재하는 suppressing, 예를들어 varhchar2와 number를 비교했을떄 한쪽이 디비내부적으로 변형됨)
// 이런것들이 자동형변환
comm + '500' // 500이 숫자로 자동형변환되서 가능
bonus > sal / '10' // 10이 숫자로 자동형변환
MOD (sal, '100') // 100이 숫자로 자동형변환
WHERE hiredate = '2003-01-01'; // hiredate는 DATA타입인데 문자로 입력받음. 알아서 DATA로 바꿔줌. 여긴 컬럼에 변화가 없으므로 인덱스사용 가능
WHERE ROWID = 'AAAada'; // ROWID는 타입이 별도로 존재하는데 문자로 입력받음. 알아서 ROWID타입으로 변경됨. 여기도 인덱스사용가능
WHERE TO_NUMBER(resno) = 12314; // reson는 varchar2타입인데 TO_NUMBER함수로 변형함. 인덱스사용불가 FULL SCAN함
특정테이블에 대해 사용할수있는 인덱스가 2개이상일때
- 옵티마이저가 조건에 가장 적절한 인덱스를 선택해서 사용함
- 주어진 조건에 가장 적절한 인덱스를 선택하려할때 일련의 절차에 따라 결정
// 인덱스1번 : 부서 + 기준일자 (선택) 매칭률 2/2, 개수2개
// 인덱스2번 : 품목 매칭률 1/1, 개수1개
SELECT *
FROM SALES
WHERE 부서 = '123'
AND 기준일자 = '201201'
AND 품목 = 'B023';
// 인덱스 변경 //////
// 인덱스 1번 : 부서 + 기준일자 + 순번 매칭률 2/3
// 인덱스 2번 : 품목 (선택) 매칭률 1/1
- 옵티마이저의 인덱스 선택 시 판단 절차
- 주어진 조건에 대한 인덱스별로 매칭률을 계산후, 매칭률이 높은것을 우선 선택
- 인덱스 별 매칭률이 같을때 인덱스를 구성하는 칼럼의 개수가 많을 것을 우선 선택
- 인덱스 매칭률 & 칼럼개수가 전부 같은경우 가장 최근에 생성된 것을 우선 선택
근데 RBO CBO가 선택방식이 조금 다른게, 매칭률이 낮더라도 더 좁은 범위를 쫍힐수있는 인덱스라면 CBO는 그걸 선택함
기본적으로 그룹바이를 먼저적용시켜서 범위를 줄이는게 더 도움이 될꺼임(서브쿼리로 그룹바이를 먼저실행하면됨)
NESTED LOOPS 조인
- 드라이빙 테이블 (outer 테이블) / 드라이븐 테이블 (inner 테이블)로 구성
- 드라이빙 테이블의 각 row에 대해 이들이 추출될때마다 드라이븐 테이블의 연관된 모든 row를 조인에 의해 액세스
튜닝포인트
- 테이블간 조인횟수를 최소화 할 수 있도록 드라이빙 테이블 잘 선택해야 한다 (드라이빙 테이블의 각 row만큼 액세스하므로)
- 드라이븐 테이블의 연결고리 칼럼에 대한 인덱스 구성 (거의 반드시)
ORDERED / LEADING은 테이블간의 접근순서를 의미하고( A를 먼저 갈까 B를 먼저갈까)
USE_NL / USE_HASH는 테이블의 접근방식을 의미하는거임( nest loop →→ / hash loop → ← 중간에서 만나냐)
// a b 순차접근으로 nest loop인데 a가 드라이빙 테이블
select /*+ ORDERED USE_NL(a b)*/ *
from student a, teacher b
where a.id = b.id
and a.name = "abc"
and b.phone like = '%010%'
NESTED LOOPS JOIN의 장단점
- 인덱스에 의한 랜덤 액세스에 기반하고 있기때문에 대량의 데이터 처리시 적합하지않음
- 드라이빙 테이블로는 테이블의 데이터가 적은 마스터 테이블이거나, WHERE절 조건으로 적절하게 ROW를 제어할 수 있는 것이여야함
- 드라이븐 테이블에는 연결컬럼을 위한 인덱스가 생성되어야함
조인 순서 제어방법
- 힌트사용
- /+ ORDERED/ : FROM절에 기술한 테이블 순서대로 제어
- /*+ LEADING(테이블명) */ : 힌트내에 제시한 테이블이 드라이빙으로 채택됨
- 둘이 같이쓰게될때 LEADING힌트는 무시됨 (그니까 같이쓰지말라..)
- 뷰활용
- 뷰를 통해 데이터를 읽고 그데이터로 조인을 할수있음. 굳이 힌트를 안써도 뷰를 잘 활용하면 되긴함
- suppressing (변형)활용
- 변형을 가하면 인덱스를 활용할수없기때문에
- FROM절의 테이블 순서변경
- 근데 CBO는 의미없음
- 양쪽 테이블 모두 인덱스가 있는경우
- 두테이블중 조회되는 결과가 적은 테이블을 선택하여 드라이빙 테이블로 선택
- 한쪽 테이블만 인덱스가 있는경우
- 인덱스가 없는 쪽 테이블을 드라이빙 테이블로 사용함
- 양쪽 모두 인덱스가 없는경우
- nested loop 조인방식으로 조인이 이뤄지지않음
- 양쪽 모두 연결고리에 대한 인덱스가 없는 경우
SORT / MERGE JOIN 사용
- 연결고리에 인덱스가 전혀 없는 경우 (nested loop가 안되니까)
- 대용량의 자료를 조인해야 함으로써 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은경우
- 드라이븐테이블이 없음 / 각 테이블에 대해 동시에 독립적으로 데이터를 각각 읽어들임
- 읽혀진 각 테이블의 데이터를 조인을 위한 연결 고리에 대하여 정렬을 수행함
- 정렬이 모두 끝난 후에야 조인 작업이 수행됨
튜닝포인트
- 각 테이블로부터 데이터를 빨리 읽어 들어도록 해야함
- 메모리(OSRT_AREA_SIZE)를 최적화해야함 ( 소팅이 둘다 끝나야 조인이 되므로 소팅이 빨리끝나야함)
// color만 인덱스라는 가정
SELECT /*+ USE_MERGE(a b)*/
a.color, ..., b.size
FROM table_a a, table_b b
WHERE a.joinkey_a = b.joinkey_b
AND a.color = 'RED'
AND b.size = 'MED';
머지조인이므로 table_a, table_b 각각 동시에 읽어들임. a는 color가 RED인 ROW들을 인덱스를 타서 읽고, b는 size가 MED인 ROW들을 풀스캔으로 읽음
데이터를 다 읽은 후에, a는 joinkey_a를 기준으로 소팅진행, b도 joinkey_b를 기준으로 소팅하는데 둘다 소팅이 완료되어야만 그때 = 로 조인을 맺는다.
- SORT / MERGE JOIN이 불리한경우
SORT / MERGE JOIN 장단점
- 연결고리에 인덱스가 생성되어있지 않은 경우에 빠른 조인을 위하여 사용됨
- 조인하고자 하는 가 테이블에 대해서 독립적으로 데이터를 읽어 들일 때, 이를 얼마나 빠르게 할 것 인가가 중요함. (풀스캔할때 블락을 크게잡는다던지..)
- 각 테이블로부터 읽혀진 데이터를 연결고리에 대해 정렬을 수행할때 이를 얼마나 빠르게 할 것인가가 중요함.
HASH JOIN
NESTED LOOP - 인덱스 사용으로 인한 랜덤엑세스의 오버해더
SORT/MERGE JOIN - 정렬 작업으로 인한 오버헤드
이럴때 HASH JOIN을 사용하면 편함.
- SORT/MERGE JOIN 조인과 비교하여, 각 테이블에 대한 처리를 독립적으로 하는것은 같지만 HASH JOIN에서는 드라이빙 테이블이 있음.다음 테이블도 똑같이 읽은 다음 해쉬값을 만들어서 기존의 해쉬값과 비교하여 조인
- 드라이빙을 결정하여 먼저읽고 그 데이터에 대해 조인하기위해서 별도의 해쉬값을 만들어서 메모리에 올려둠 ← 이값으로 조인을함.
- 읽어 들인 각 테이블의 데이터를 서로 조인하기 위해 해싱(hashing)을 이용해서 해시 값을 만듦
- 다만 해시값이 충돌날수있기때문에 2차테이블까지만듬. (?)
- 해시조인은 하드웨어가 넉넉한경우에 좋은 성능을냄. 해시에서 사용되는 메모리는 소팅에서 사용하는 메모리의 거의 2배를 필요하기때문에 많은 자원을 요구
- 기본적으로 hash_area_size에 지정된 크기만큼의 메모리가 할당되어 사용됨.
- 조인을 수행하기에 메모리가 부족하면 가장 큰 순서대로 hash bucket이 임시 테이블스페이스로 내려가서 구성됨.
- 디스크로 내려간 hash bucket에 변경이 일어날때마다 디스크 i/o가 발생하여 성능이 현저하게 떨어짐
튜닝포인트
- 드라이빙 테이블을 잘 결정해야함(nested loop처럼 적은게 나음)
- 각 테이블로부터 데이터를 읽어 들일때, 빨리 읽을 수 있도록 해야함
- 메모리(HASH_AREA_SIZE)를 최적화해야함 - 100건을 해쉬테이블에 올려야하는데 50건밖에 자리가없으면 나머지 50건은 디스크에 쓰기떄문
카테시안 조인의 개념
카테시안 조인이 발생하는 경우
- where절이 없는 조인 수행
- 조인을 위한 조건 없이 조인 수행
데이터 복제라는 개념을 활용하기 위해 사용하지만, 잘못사용하게되면 오히려 데이터를 부풀리는 원인이므로 조심해야함.
자주사용하는 방법
- COPY_T, DUMMY_T 같은 임시테이블을 활용함.
- DUAL을 사용함
- 타 SQL에서 사용하고있는 테이블 활용 및 ROWNUM을 사용함
카테시안 조인 적용예제
// 각각 emp테이블을 읽어서 where조건없이 나온값들을 합치고있다 -> 이럴때 카테시안 조인을 쓰면좋음
SELECT '직군별' AS class, job, COUNT(*) AS cnt
FROM emp
GROUP BY job
UNION ALL
SELECT '부서별' AS class, TO_CHAR(deptno), COUNT(*)
FROM emp
GROUP BY deptno
UNION ALL
SELECT '총인원' AS class, NULL, COUNT(*)
FROM emp
// 이렇게하면 조인문을 쓰지않고 위와 똑같은 결과를 낼수있음
SELECT DECODE(rn,1,'직군별',2,'부서별','총인원') as class,
DECODE(rn,1,job,2,deptno)
SUM(cnt)
FROM (SELECT job, deptno, COUNT(*) AS cnt
FROM emp
GROUP BY job, deptno),
(SELECT ROWNUM AS rn
FROM (SELECT LEVEL RFROM dual
CONNECT BY ROWNUM **<=3)**) // 3건을 복제해야하므로
GROUP BY rn,
DECODE(rn,1,'직군별',2,'부서별','총인원'),
DECODE(rn,1,job,2,deptno);
서브쿼리
NESTED 서브쿼리
SELECT empno, ename
FROM emp
WHERE deptno = (SELECT dptno FROM dept WHERE dname = 'SALES');
---------------------------------------------
id | opertaion | name
------------------------------------------------
0 | SELECT STATEMENT
1 | TABLE ACCESS BY INDEX ROWID | EMP
2 | INDEX RANGE SCAN | EMP_IDX01 (인덱스명) 인덱스를 사용했다는 의미 deptno가 인덱스겠지..
3 | TABLE ACCESS FULL | DEPT <- 서브쿼리테이블 먼저 실행NESTED 서브쿼리는 메인쿼리보다 먼저 실행되어야 속도를 낼 수 있다.
단, 위에서 deptno가 인덱스로 주어져있을때 먼저 동작된다. 또한, 인덱스로 주어져있어도 서브쿼리에서 읽어오는 결과와 비교할때 사용되는 연산자가 인덱스를 사용할 수 없게하는 연산자라면 서브쿼리는 먼저 실행되지않는다.
CORRELATED 서브쿼리
메인쿼리보다 먼저실행되는 서브쿼리
SELECT empno, ename
FROM emp
WHERE EXISTS (SELECT 'X' FROM dept WHERE dept.deptno = emp.deptno AND dept.name = 'SALES'); 메인에서 읽어서 emp.deptno를 알려줘야만 가능
SCALAR 서브쿼리
SELECT절에도 있고 WHERE절에도 있고 ORDER BY에도 있고.. 각종 함수의 아규먼트로 사용되는 쿼리
→ 단 하나의 데이터와 단 하나의 컬럼만을 리턴함. 찾는데이터가 없으면 반드시 NULL리턴
ROLLUP(), CUBE()
- ROLLUP, 총계를 나타내고싶을때
- CUBE, 총계 + 소계까지 나타내고싶을때
- CUBE랑 ROLLUP으로 다 할수있음
'IT > DB' 카테고리의 다른 글
| 오라클 계정내 모든 테이블 삭제하기 (0) | 2024.07.15 |
|---|---|
| ORACLE 정리 (0) | 2023.03.27 |
| mysql 정리 (0) | 2023.03.27 |
| mysql 도커 실행 (0) | 2022.01.24 |
| mybatis $ # 차이 (0) | 2022.01.24 |
댓글