옵티마이저 연산자
Optimizer가 선택하는 Access PATH
- Table Access
- 전체 테이블 읽기(Full Table Scan : 실행 계획상에서는 'Table Access Full(TAF)' 로 출력)
- SQL> select * from emp;
- SQL> select /*+ FULL(emp) */ * from emp where deptno=10;
- HINT : Optimizer에게 이 경로를 이용하라고 지시
USER01@orcl2> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
Elapsed: 00:00:00.01
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID a2dk8bdn0ujx7, child number 0
-------------------------------------
select * from emp
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------
13 rows selected.
Elapsed: 00:00:00.04
USER01@orcl2> select /*+ FULL(emp) */ * from emp where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Elapsed: 00:00:00.01
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2faurs97x9jr1, child number 0
-------------------------------------
select /*+ FULL(emp) */ * from emp where deptno=10
Plan hash value: 3956160932
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 5 | 3 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=10)
Elapsed: 00:00:00.01
- TABLE ACCESS BY ROWID
- SQL> elect /*+ INDEX(EMP {INDEXNAME}) */ * from emp where empno=7788;
- SQL> select /*+ INDEX(EMP(EMPNO)) */ * from emp where empno=7788;
- emp table의 empno에 걸려있는 index를 써라.
USER01@orcl2> select /*+ INDEX(EMP(EMPNO)) */ * from emp where empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Elapsed: 00:00:00.00
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 6qy4j9fsdpz1y, child number 0
-------------------------------------
select /*+ INDEX(EMP(EMPNO)) */ * from emp where empno=7788
Plan hash value: 247672086
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMPNO_IX | 1 | 1 | 1 |00:00:00.01 | 1 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
Elapsed: 00:00:00.01
-- index를 통해서 얻은 rowid값을 가지고 table access를 진행한다 => RANDOM ACCESS
- TABLE ACCESS SAMPLE
- SQL> select * from emp sample(10);
- 데이터를 10%만 보고싶다는 의미이다.
USER01@orcl2> select * from emp sample(10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Elapsed: 00:00:00.00
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 46fsy9v9xk3wa, child number 0
-------------------------------------
select * from emp sample(10)
Plan hash value: 4089107469
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS SAMPLE| EMP | 1 | 1 | 4 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------
Elapsed: 00:00:00.01
USER01@orcl2> select * from sales sample block(10);
........
84980 rows selected.
Elapsed: 00:00:00.96
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9y2gwppt1m2k3, child number 0
-------------------------------------
select * from sales sample block(10)
Plan hash value: 3469240937
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 84980 |00:00:00.14 | 1262 | 1272 |
| 1 | TABLE ACCESS SAMPLE| SALES | 1 | 91884 | 84980 |00:00:00.14 | 1262 | 1272 |
------------------------------------------------------------------------------------------------
Elapsed: 00:00:00.03
INDEX ACCESS
- INDEX UNIQUE SCAN
- UNIQUE INDEX=
- SQL>select /*+ INDEX(e (employee_id))*/ * from employees e where department_id = 100
USER01@orcl2> select /*+ INDEX(e (employee_id))*/ * from employees e where department_id = 100;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100
109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100
110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100
113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100
6 rows selected.
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a20fjmnmwkdmw, child number 0
-------------------------------------
select /*+ INDEX(e (employee_id))*/ * from employees e where
department_id = 100
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 6 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 6 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 1 | 10 | 6 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=100)
- INDEX RANGE SCAN
- UNIQUE INDEX + 범위 조건 '>'
- SQL> select /*+ INDEX(e (employee_id))*/ * from employees e where department_id > 100
- 가장 일반적인 접근방법, 기본적으로 ASCENDING으로 접근
USER01@orcl2> select /*+ INDEX(e (employee_id))*/ * from employees e where department_id > 100 ;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
Elapsed: 00:00:00.00
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4jzm62x5skay1, child number 0
-------------------------------------
select /*+ INDEX(e (employee_id))*/ * from employees e where
department_id > 100
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 11 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 1 | 11 | 2 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID">100)
- NONUNIQUE INDEX, =, 범위조건 '>'
USER01@orcl2> select * from employees where department_id = 10;
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1cfy9v4kzrhq0, child number 0
-------------------------------------
select * from employees where department_id = 10
Plan hash value: 3013043712
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMPL_DEPTNO_IX | 1 | 10 | 1 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=10)
Elapsed: 00:00:00.02
- INDEX FULL SCAN
- 모든 LEAF BLOCLK을 순서대로 읽어낸다.
- 사용되는 컬럼에 NOT NULL 제약조건 또는 조건식(IS NOT NULL)이 있을때
- SINGLE BLOCK IO
USER01@orcl2> select count(*) from employees where employee_id is NOT NULL;
COUNT(*)
----------
107
Elapsed: 00:00:00.00
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8vncmrfxz6tc9, child number 0
-------------------------------------
select count(*) from employees where employee_id is NOT NULL
Plan hash value: 3798089054
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 1 |
|* 2 | INDEX FULL SCAN| EMPL_EMPID_IX | 1 | 107 | 107 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID" IS NOT NULL)
Elapsed: 00:00:00.01
-- table access는 하지 않는다 왜냐하면 leaf block의 갯수만 세어도 무관하기 때문
USER01@orcl2> select employee_id from employees where employee_id IS NOT NULL;
......
107 rows selected.
Elapsed: 00:00:00.01
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7632zyvp54wv8, child number 0
-------------------------------------
select employee_id from employees where employee_id IS NOT NULL
Plan hash value: 2656877640
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 3 |
|* 1 | INDEX FULL SCAN | EMPL_EMPID_IX | 1 | 107 | 107 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID" IS NOT NULL)
USER01@orcl2> select employee_id, last_name from employees where employee_id IS NOT NULL;
......
107 rows selected.
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3377j4y3250xs, child number 0
-------------------------------------
select employee_id, last_name from employees where employee_id IS NOT
NULL
Plan hash value: 1445457117
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 6 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID" IS NOT NULL)
--/* INDEX (TABLE_NAME (COLUMN_NAME)) */
USER01@orcl2> SELECT /*+ INDEX (E (EMPLOYEE_ID)) */ EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES E
WHERE EMPLOYEE_ID IS NOT NULL;
......
107 rows selected.
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cxasajrftfzk4, child number 0
-------------------------------------
SELECT /*+ INDEX (E (EMPLOYEE_ID)) */ EMPLOYEE_ID, LAST_NAME FROM
EMPLOYEES E WHERE EMPLOYEE_ID IS NOT NULL
Plan hash value: 1119809800
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 8 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 8 |
|* 2 | INDEX FULL SCAN | EMPL_EMPID_IX | 1 | 107 | 107 |00:00:00.01 | 3 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID" IS NOT NULL)
-- 하나도 빠짐없이 읽는게 나으므로 자동적으로 full scan이 진행된다.
- INDEX FAST FULL SCAN
- 모든 LEAF BLOCLK을 읽어낸다.
- 사용되는 컬럼에 NOT NULL 제약조건 또는 조건식(IS NOT NULL)이 있을때
- MULTI BLOCK IO
USER01@orcl2> select count(*) from employees where employee_id is NOT NULL;
USER01@orcl2> select employee_id from employees where employee_id IS NOT NULL;
-- 위의 두개는 인덱스만 읽으면 해결되는 두놈
USER01@orcl2> select /*+ INDEX_FFS(e (employee_id))*/ count(*) from employees e where employee_id is NOT NULL;
COUNT(*)
----------
107
Elapsed: 00:00:00.00
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0wwg89s9zc3vw, child number 0
-------------------------------------
select /*+ INDEX_FFS(e (employee_id))*/ count(*) from employees e where
employee_id is NOT NULL
Plan hash value: 21458044
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 4 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 4 | 1 |
|* 2 | INDEX FAST FULL SCAN| EMPL_EMPID_IX | 1 | 107 | 107 |00:00:00.01 | 4 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EMPLOYEE_ID" IS NOT NULL)
Elapsed: 00:00:00.01
USER01@orcl2> select /*+ INDEX_FFS(e (employee_id))*/ employee_id from employees e where employee_id IS NOT NULL;
107 rows selected.
Elapsed: 00:00:00.01
USER01@orcl2> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 412hvhgq0tjwz, child number 0
-------------------------------------
select /*+ INDEX_FFS(e (employee_id))*/ employee_id from employees e
where employee_id IS NOT NULL
Plan hash value: 62351567
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 107 |00:00:00.01 | 6 |
|* 1 | INDEX FAST FULL SCAN| EMPL_EMPID_IX | 1 | 107 | 107 |00:00:00.01 | 6 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID" IS NOT NULL)
Elapsed: 00:00:00.01
클러스터
- 데이터를 저장하는 방법
- Index(ed) Cluster
- Hash Cluster
- 해시 함수 결과에 따라 행을 검색
Sort 연산자
- SORT ORDER BY
- SELECT * FROM EMP ORDER BY SAL
- 실제 메모리를 할당해서 SORT를 진행
- SORT AGGREGATE
- SELECT SUM(SAL) FROM EMP
- GROUP 함수
- 실제로 정렬 작업은 하지 않는다.
- SORT UNIQUE
- SELECT DNAME FROM DEPT UNION SELECT ENAME FROM EMP
- SELECT DNAME FROM DEPT UNION ALL SELECT ENAME FROM EMP
- 결과는 같지만 SORT UNIQUE를 하지 않는다.
- 즉 결과가 같으면 UNION ALL을 사용하자
- HASH UNIQUE
- SELECT DISTINCT(DEPTNO) FROM EMP
- 정렬 작업을 할 필요가 없다.
- SORT GROUP BY
- SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO
- HASH GROUP BY
- SELECT DEPTNO, SUM(SAL) FROM EMP GROUP BY DEPTNO
- 실제로 정렬 작업은 하지 않는다 다만 DISTINCT작업만 한것.
- BUFFER SORT
- SELECT * FROM EMP E, DEPT D WHERE ENAME LIKE 'A%'
INLIST ITERATOR 연산자
- SELECT * FROM EMP WHERE DEPTNO IN (10,20)
- 인덱스를 전체 한번읽고 또 따로따로 읽는다.
Count StopKey 연산자
- SELECT * FROM EMP WHERE ROWNUM = 1
'DATABASE(oracleDB 11g) > Tuning' 카테고리의 다른 글
| [SQL Tuning]인덱스(Index)의 기초 (0) | 2019.05.20 |
|---|---|
| [SQL Tuning]인덱스를 사용하지 못하는 사례 6가지와 해결책 (0) | 2019.05.03 |
| [SQL Tuning]실행계획 (0) | 2019.05.03 |
| [SQL Tuning]튜닝의 기초와 옵티마이져 (0) | 2019.05.03 |
| [SQL Tuning] Hinst란 (0) | 2019.02.12 |