문제 PK 인덱스와 쿼리는 다음과같다.
인덱스를 추가하고 힌트를 추가하거나 where 조건절에 조건을 추가하여 아래와 같은 실행계획이 나오도록 쿼리를 짜보십시오
1~6번 1번 쿼리 7 ~10 2번쿼리
PK 인덱스
--create index empx01 on emp(deptno);
--create index dept1_x01 on dept1(deptno);
문제
select * from dept d
where /*빈칸 */
and
exists (select 'x' from emp e where e.deptno = d.deptno
/*빈칸 */
);
1.
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | FILTER | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 2 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 5 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access("DNAME" LIKE 'S%')
filter("DNAME" LIKE 'S%')
4 - filter("MGR">10)
5 - access("E"."DEPTNO"=:B1)실행계획 해석
create index emp_x01 on emp(deptno);
create index dept_x01 on dept(dname);
FILTER조건으로 서브쿼리가 마지막에 수행됨 (no_unnest no_push_subq)
메인쿼리가 인덱스 레인지 스캔 후 테이블 액세스 -> 메인쿼리 조건절에 인덱스 컬럼조건 추가해주고 SELECT 절에 * 이므로 테이블 액세스는 필요하기 때문에 인덱스는 하나로만 생성
서브쿼리가 인덱스 레인지스캔 후 테이블 액세스 -> 서브쿼리는 조건이 이미 나와있으므로(e.deptno=d.deptno) 인덱스 생성 후 인덱스에 없는 조건을 추가하여 테이블 액세스를 하여 찾게 하기
select /*+INDEX (d dept_x01) */* from dept d
where /*빈칸 */ dname like 'S%' and
exists (select /*+no_unnest no_push_subq INDEX(e emp_x01) */ 'x' from emp e where e.deptno = d.deptno
/*빈칸 */ and mgr >10
);
2.
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | FILTER | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 3 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 2 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access("DNAME" LIKE 'S%')
filter("DNAME" LIKE 'S%')
4 - access("E"."DEPTNO"=:B1)해석
create index emp_x01 on emp(deptno);
create index dept_x01 on dept(dname);
FILTER 조건으로 서브쿼리가 마지막에 수행됨(no_unnest no_push_subq)
서브쿼리가 인덱스 레인지 스캔만 수행 -> 인덱스 조건절만 타고 해결할 수 있는 쿼리라는 뜻 ( 조건절에 기존 조건 외에 아무조건도 추가 x)
메인쿼리가 인덱스 레인지 스캔 후 테이블 액세스 -> 메인쿼리 조건절에 인덱스 컬럼조건 추가해주고 SELECT 절에 * 이므로 테이블 액세스는 필요하기 때문에 인덱스는 하나로만 생성
select /*+INDEX (d dept_x01) */* from dept d
where /*빈칸 */ dname like 'S%' and
exists (select /*+no_unnest no_push_subq INDEX(e emp_x01) */ 'x' from emp e where e.deptno = d.deptno
--/*빈칸 */
);
3.
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( IS NOT NULL)
3 - filter("SAL">=1000)
4 - access("E"."DEPTNO"=:B1 AND "JOB"='SALESMAN')해석
서브쿼리는 레인지 스캔 -> 테이블 액세스이므로 인덱스 조건컬럼에 없는 조건 추가해주기 (기존에 조건절에 인덱스가 생성되어 있기 때문)
메인쿼리는 레인지 스캔 -> 테이블 액세스이므로 인덱스 조건컬럼에 있는 조건으로 추가해주기 (조건절 없기 때문)
조인으로 풀리지는 않음 -> (no_unnest push_subq) 조건까지는 맞는 듯
create index emp_x01 on emp(deptno,job);
create index dept_x01 on dept(dname,deptno);
select /*+ INDEX(d dept_x01) */* from dept d
where /*빈칸 */ dname = 'SALES' and
exists (select /*+ no_unnest push_subq INDEX(e emp_x01) */ 'x' from emp e where e.deptno = d.deptno
/*빈칸 */ and job = 'SALESMAN'
and sal >= 1000
);
4.
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DNAME"='SALES')
filter( IS NOT NULL)
3 - access("E"."DEPTNO"=:B1 AND "JOB"='SALESMAN')
해석
create index emp_x01 on emp(deptno,job);
create index dept_x01 on dept(dname,deptno);
서브쿼리는 레인지 스캔이므로 where절에 인덱스 컬럼만 존재하게 하기
메인쿼리는 레인지 스캔 -> 테이블 풀스캔이므로 where 절에 인덱스 컬럼 추가하기 (where절에 아무것도 없는 상태이므로)
조인으로 풀린 상태가 아니므로 메인 쿼리 - 서브쿼리 상태인듯? (3번과 동일)
select /*+ INDEX(d dept_x01) */* from dept d
where /*빈칸 */ dname = 'SALES' and
exists (select /*+ no_unnest push_subq INDEX(e emp_x01) */ 'x' from emp e where e.deptno = d.deptno
/*빈칸 */ and job = 'SALESMAN'
);
5.
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| EMP | 2 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 5 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("DNAME" LIKE 'S%')
filter("DNAME" LIKE 'S%')
3 - filter("EMPNO">7000)
4 - access("E"."DEPTNO"=:B1)해석
create index emp_x01 on emp(deptno);
create index dept_x01 on dept(dname);
select /*+ INDEX (d dept_x01) */* from dept d
where /*빈칸 */ dname like 'S%' and
exists (select /*+ no_unnest push_subq INDEX(e emp_x01) */ 'x' from emp e where e.deptno = d.deptno
/*빈칸 */ and empno > 7000
);
3번은 dept_x01 인덱스에 deptno컬럼이 포함돼 있으므로 dept 테이블을 액세스 하기 이전 인덱스 레벨에서 서브쿼리 필터링이 가능
하지만 여기서는 dept_x01인덱스가 dname컬럼만으로 구성했기 대문에 인덱스 레벨에서 서브쿼리 필터링이 불가능
따라서 push_subq 힌트를 사용하지 않았다면 1번과 동일한 결과를 낼것(확인)
1번 실행계획보다 emp테이블에 대한 필터처리가 한 칸더 안쪽으로 들어가면서 dept 테이블에 대한 자식노드로 표현된것 -> 테이블 레벨 필터링
3번 실행계획은 인덱스 레벨에서 필터링이 이루어져 서브쿼리 필터링이 dept_x01 인덱스의 자식노드로 표현됨
6.
---------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX RANGE SCAN | DEPT_X01 | 1 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 2 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("DNAME" LIKE 'S%')
filter("DNAME" LIKE 'S%')
3 - access("E"."DEPTNO"=:B1)
create index emp_x01 on emp(deptno);
create index dept_x01 on dept(dname);
select /*+ INDEX (d dept_x01) */* from dept d
where /*빈칸 */ dname like 'S%' and
exists (select /*+ no_unnest push_subq */ 'x' from emp e where e.deptno = d.deptno
/*빈칸 */
);
=============================================================
문제2
select * from dept d
where deptno = (select /*문제*/ deptno from emp
where /*문제*/ ...
);
7.
----------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 4 | INDEX RANGE SCAN | EMP_X02 | 1 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=)
4 - access("ENAME"='SCOTT')해석
dept는 PK를 사용
create index emp_x02 on emp(ename);
emp(ename)
select /*+ INDEX(d dept_pk) */ * from dept d
where deptno = (select /*+ no_unnest push_subq INDEX(emp emp_x02) */ deptno from emp
where /*문제*/ ename='SCOTT'
);
JOIN이 없으므로 동등한 관계 x 필터가 없으므로 push_subq (no_unnest push_subq)
서브쿼리 인덱스 레인지 스캔 -> 테이블 액세스 이므로 조건절에 인덱스 포함 컬럼 추가 되야 함(하지만 deptno가 인덱스 컬럼이면 안됨) 왜냐하면 테이블 액세스를 해야 하는데 이미 SELECT에 deptno가 있음
만약 deptno가 인덱스 컬럼으로 있으면 index range scan만 될듯
메인쿼리 인덱스 유니크 스캔 -> 테이블 액세스 이므로 deptno(PK)가 조건으로 들어가야 함 SELECT * 이므로 테이블 액세스는 될듯
where 절에 사용된 서브쿼리가 메인쿼리와 상관관계에 있지 않으면서 단일로우를 리턴하는 경우.
8.
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
|* 3 | INDEX RANGE SCAN | EMP_X01 | 1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=)
3 - access("DEPTNO"=0)
create index emp_x01 on emp(deptno);
JOIN이 없으므로 동등한 관계 X , 필터가 없으므로 push_subq (no_unnest push_subq)
서브쿼리는 인덱스 레인지 스캔만, deptno로 인덱스 생성 후 조건절에도 인덱스 주면 댐
메인쿼리는 유니크 스캔 -> 테이블 액세스 이므로 따로 추가해줄 것 x
select /*+ INDEX(d dept_pk) */ * from dept d
where deptno = (select /*+ no_unnest push_subq INDEX(emp emp_x01) */ deptno from emp
where /*문제*/ deptno = 0
);
9.
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 3 | SORT AGGREGATE | | 1 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 5 | INDEX RANGE SCAN | EMP_X01 | 5 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=)
4 - filter("ENAME"='SCOTT')
5 - access("DEPTNO"=20)해석
create index emp_x01 on emp(deptno)
JOIN이 없으므로 동등한 관계 X , 필터가 없으므로 push_subq (no_unnest push_subq)
서브쿼리에 SORT(AGGREGATE) - > 정렬작업이 추가되야 함 -> 집계함수 사용 (max,min(deptno))
메인쿼리 유니크 스캔 -> 테이블 액세스 따로 추가해줄 것 x
select /*+ INDEX(d dept_pk) */ * from dept d
where deptno = (select /*+ no_unnest push_subq INDEX(emp emp_x01) */ max(deptno) from emp
where /*문제*/ deptno = 20 and ename='SCOTT'
);
10.
--------------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 3 | SORT AGGREGATE | | 1 |
|* 4 | INDEX RANGE SCAN | EMP_X01 | 2 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=)
4 - access("ENAME" LIKE 'S%')
filter("ENAME" LIKE 'S%')해석
create index emp_x01 on emp(ename,deptno)
JOIN이 없으므로 동등한 관계 X , 필터가 없으므로 push_subq (no_unnest push_subq)
서브쿼리에 SORT(AGGREGATE) - > 정렬작업이 추가되야 함 -> 집계 함수 추가
서브쿼리에 range scan 인덱스만 읽게 해야됨 = 이면 오답과 같은 실행계획이 나오므로 범위 조건을 주기 위해 ename like 조건 추가 후아 select는 두번째 인덱스 컬럼인 dept를 참고해서 읽기 때뭉네 테이블은 읽지 않음
메인쿼리 유니크 스캔 -> 테이블 액세스 따로 추가해줄 것 x
select /*+ INDEX(d dept_pk) */ * from dept d
where deptno = (select /*+ no_unnest push_subq INDEX(emp emp_x01) */ max(deptno) from emp
where /*문제*/ ename like 'S%'
);
추가문제)
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 |
|* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
| 3 | SORT AGGREGATE | | 1 |
| 4 | FIRST ROW | | 1 |
|* 5 | INDEX RANGE SCAN (MIN/MAX)| EMP_X01 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=)
5 - access("DEPTNO"=20 AND "ENAME"='SCOTT')해석
= 조건이기 때문에 min/max 후 first row를 찾음
create index emp_x01 on emp(deptno,ename)
select /*+ INDEX(d dept_pk) */ * from dept d
where deptno = (select /*+ no_unnest push_subq INDEX(emp emp_x01) */ max(deptno) from emp
where /*문제*/ deptno = 20 and ename='SCOTT'
);
'스터디 > 오라클 성능고도화 원리와 해법2' 카테고리의 다른 글
| 04 쿼리변환 - 서브쿼리 Unnesting (0) | 2021.08.30 |
|---|---|
| CH02. 조인 원리와 활용 - 05.Outer 조인 (0) | 2020.06.02 |
| CH02. 조인 원리와 활용 - 04.조인 순서의중요성 (0) | 2020.06.02 |
| CH02. 조인 원리와 활용 - 03.해시조인 (0) | 2020.05.28 |
| CH02. 조인 원리와 활용 - 02.소트 머지 조인 (0) | 2020.04.16 |
댓글