나혼렙 BARANUPDATE09SELECT * FROM SCOTT25.TEST;mysql07 / mysqlscott07 / oracleSQL19##스칼라 쿼리의 장단점을 설명할 수 있다실행계획의 FILTER 를 설명할 수 있다.스칼라 쿼리의 장단점을 설명할 수 있다인라인뷰의 MERGE 방식을 설명할 수 있다NESTED LOOP JOIN 으로 풀리는 SQL 문을 HASH JOIN으로 전개되도록 HINT로 SQL문을 수정할 수 있다.cdspsh startup.shsh conn.shcreate database test;use test;source /home/mysql07/test2.sqlmysql07/mysql ~ scott07/oracleSQL19##140.238.28.217 ~ 131.186.30.235 (pdb3.orclpub.orcl.oraclevcn.com)PARSING EXECUTE FETCH 스칼라쿼리는 패치단계에서 가져옴INDEX를 사용하지 않으면, FILTER('SAL'>= 2000 )조회칼럼이 없는 확인자 테이블은 서브쿼리 > 세미나, 필터가 형태로 조정해야함대량데이터는 풀데이터스캔을..주로사용함 (index x)대량데이터는 인라인뷰 사용시 그냥조인보다 데이터 줄인 후 조인 성능 향상`CVM(콤플렉스뷰) 특정 제약사항 발생시 해제 할려고 함대용량 쿼리는 스칼라쿼리는 쓰지않는다 -> 조인으로 풀어서select /*+QB_NAME(MAIN) */ d.department_id, d.department_name, d.location_idfrom departments dwhere exists ( select /*+QB_NAME(SUB) */NULLFROM EMPLOYEES eWHERE e.department_id = d.department_id )AND d.location_id = 1700;SELECT create index DEPT_LOCATION_IX on departments ( location_id );select emp_name from empswhere dept_no in ( select dept_no from depts );select /*+ gather_plan_statistics */ dept_namefrom deptswhere dept_no in ( select dept_no from emps ); select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));select distinct d.dept_name from depts d, emps ewhere e.dept_no = d.dept_no; 0.002SELECT /*+ gather_plan_statistics LEADING(V.C V.L V.D) */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK' ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK' ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics ORDERED */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND e.job_id = 'ST_CLERK' AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics ORDERED */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.job_id = 'ST_CLERK' AND e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK'; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE OR REPLACE VIEW v_dept ASSELECT d.department_id, d.department_name, d.manager_id, l.location_id, l.postal_code, l.city, c.country_id, c.country_name, c.region_id FROM departments d, locations l, countries c WHERE d.location_id = l.location_id AND l.country_id = c.country_id; ALTER TABLE DEPARTMENTS ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID);ALTER TABLE LOCATIONS ADD CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID);ALTER TABLE COUNTRIES ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID);ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID); CREATE INDEX loc_postal_idx ON locations (postal_code);CREATE INDEX dept_name_idx ON departments (department_name);CREATE INDEX coun_region_idx ON countries (region_id);CREATE INDEX emp_department_ix ON employees (department_id);DROP TABLE DEPARTMENTS;CREATE TABLE DEPARTMENTS AS SELECT * FROM SCOTT25.DEPARTMENTS;CREATE TABLE EMPLOYEES AS SELECT * FROM SCOTT25.EMPLOYEES;CREATE TABLE COUNTRIES AS SELECT * FROM SCOTT25.COUNTRIES;CREATE TABLE LOCATIONS AS SELECT * FROM SCOTT25.LOCATIONS;SELECT /*+ gather_plan_statistics QB_NAME(OUTER) */ *FROM ( /*+ QB_NAME(INNER)*/ SELECT ROWNUM, EMP_NAME, HIRE_DATE, SAL, DEPT_NO FROM EMPS WHERE DEPT_NO = 30 ) VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); SELECT /*+ gather_plan_statistics NO_PUSH_PRED(V) */ *FROM VU_EMP_DEPT30 VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(V) */ *FROM VU_EMP_DEPT30 VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE OR REPLACE VIEW VU_EMP_GROUP_BY_DEPTNOASSELECT DEPT_NO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL FROM EMPS GROUP BY DEPT_NO; CREATE OR REPLACE VIEW VU_EMP_DEPT30ASSELECT EMP_NAME, HIRE_DATE, SAL, DEPT_NO FROM EMPS WHERE DEPT_NO = 30;CREATE INDEX EMP_HIRE_DT_IDX02 ON EMPS (HIRE_DATE);SELECT * FROM VU_EMP_GROUP_BY_DEPTNO;SELECT * FROM VU_EMP_DEPT30;"SELECT /*+ FULL(B) */ B.PRDT_CD, MIN(B.PRDT_NM),SUM(A.SALE_CNT), SUM(A.SALE_AMT)FROM TB_PRDT_SALE_DAY A, TB_PRDT BWHERE A.SALE_DT BETWEEN '20140101' AND '20151231' AND -- 로 수정 A.PRDT_CD = B.PRDT_CDGROUP BY B.PRDT_CD;"SELECT /*+ gather_plan_statistics */ A.*, B.PRDT_NMFROM (SELECT A.PRDT_CD, SUM(A.SALE_CNT) AS SALE_CNT, SUM(A.SALE_AMT) AS SALE_AMTFROM TB_PRDT_SALE_DAY AWHERE A.SALE_DT BETWEEN '20140101' AND '20151231'GROUP BY A.PRDT_CD) A, TB_PRDT BWHERE A.PRDT_CD = B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics FULL(B) LEADING(B) */ B.PRDT_CD, MIN(B.PRDT_NM),SUM(A.SALE_CNT), SUM(A.SALE_AMT)FROM TB_PRDT_SALE_DAY A, TB_PRDT BWHERE A.SALE_DT BETWEEN '20140101' AND '20151231' AND -- 로 수정 A.PRDT_CD = B.PRDT_CDGROUP BY B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));0.011SELECT /*+ FULL(B) */B.PRDT_CD, B.PRDT_NM,A.SALE_CNT, A.SALE_AMTFROM ( SELECT SUM(A.SALE_CNT) AS SALE_CNT, SUM(A.SALE_AMT) AS SALE_AMT, PRDT_CD FROM TB_PRDT_SALE_DAY A WHERE SALE_DT BETWEEN '20140101' AND '20151231' GROUP BY PRDT_CD ) A , TB_PRDT BWHERE A.PRDT_CD = B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));0.012 select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE TABLE TB_PRDT AS SELECT * FROM SCOTT25.TB_PRDT;ALTER TABLE TB_PRDT ADD CONSTRAINT TB_PRDT_PK PRIMARY KEY(PRDT_CD);CREATE TABLE TB_PRDT_SALE_DAY AS SELECT * FROM SCOTT25.TB_PRDT_SALE_DAY;ALTER TABLE TB_PRDT_SALE_DAY ADD CONSTRAINT TB_PRDT_SALE__DAY_PK PRIMARY KEY(SALE_NO);CREATE INDEX ORDERS_MEP_DEV_DT_IDX ON ORDERS ( ORD_EMP_NO, DEV_DATE );SELECT /*+ gather_plan_statistics INDEX(O ORDERS_EMP_DEV_DT_IDX) */ D.DEPT_NAME , E.EMP_NO , O.DEV_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ORDER BY D.DEPT_NAME, E.HIRE_DATE, O.DEV_DATE ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ D.DEPT_NAME , E.EMP_NO , O.ORD_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ VA.DEPT_NAME , VA.EMP_NO , VA.EMP_NAME, Y.DEV_DATEFROM (SELECT D.DEPT_NAME , E.EMP_NO, E.EMP_NAMEFROM DEPTS D , EMPS EWHERE D.DEPT_NO = E.DEPT_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 ORDER BY D.DEPT_NAME, E.HIRE_DATE ) VA, ORDERS YWHERE VA.EMP_NO = Y.ORD_EMP_NOAND Y.DEV_DATE > '20090109'; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); --SET PAGES 0 SELECT /*+ gather_plan_statistics NO_MERGE(V) */ EMPNO, DEPTNOFROM ( SELECT EMPNO, DEPTNO FROM EMP WHERE ENAME = 'SMITH' ) VWHERE EMPNO = 7369; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); ORDER BY D.DEPT_NAME , E.HIRE_DATE , O.DEV_DATE ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics no_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));--SET PAGES 0 SELECT /*+ gather_plan_statistics NO_MERGE(V) */ EMPNO, DEPTNOFROM ( SELECT EMPNO, DEPTNO FROM EMP WHERE ENAME = 'SMITH' )WHERE EMPNO = 7369; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); SELECT COUNT(1) FROM EMPLOYEE;select * from employeeswhere dept_nofilter -> dependent subquery <~~ 무조건 개선해야할 사항explainselect employees.emp_no,employees.first_namefrom employees, salarieswhere employees.emp_no > 450000and employees.emp_no = salaries.emp_nogroup by employees.emp_nohaving max(salaries.salary) > 100000;select * from employees where emp_no = ( select max(emp_no) from salaries ) ;select * from employees where emp_no in ( select max(emp_no) from salaries where salary = 40091 ) ;Nested loop join - 소량의 데이터조회 시 유리sort join - 처리량이 많은 전체범위에 주로사용hash join - 먼저 처리되는 집합(Driving)이 소량일 때 성능 극대화index full join - 느려요, b tree 인덱스oracle mysql8.0 mariadb 실행계획 보는 양식이 조금씩 다름 - explain EXTENDED : filtered ( innodb 스토리지 엔진 추출데이터 몇프로 사용? ) - mysql : use index(), !igonore index() <~~ 힌트사용 , mysql 은 +실행계획 - MYSQL, ORACLE, MARIADB 튜닝mysql07/mysql140.238.28.217 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ /*!80016 DEFAULT ENCRYPTION='N' */;USE `test`;-- Table structure for table `departments`DROP TABLE IF EXISTS `departments`;/*!40101 SET @saved_cs_client = @@character_set_client */;:desc select * from employees;simple - select type union all 하지 않은 말그대로 심플 데이터 조회 ( type : ALL, type : const )explainSELECT 'M' as gender, MAX(hire_date) as hire_dateFROM employees as emp1WHERE gender = 'M'UNION ALLSELECT 'F' as gender, MIN(hire_date) as hire_dateFROM employees as emp2WHERE gender = 'F';explain select * from employees where substring(emp_no,1,4) = 1100 and length(emp_no) = 5; - rows flter 100% 전체 full scandesc employees;explain select * from employees where emp_no like '1100%' and length(emp_no) = 5; -- 데이터타입이 숫자이고, 프라머리키 렌지스캔이 될수 있도록 개선explain analyze select * from employees where emp_no between 11000 AND 11009;explain select count(1) from salaries where use_yn = 1;explain select count(1) from salaries where !ignore index(use_yn) = 1;explain select count(1) from salaries where use_yn = '1';show index from salaries;explain analyzeSELECT de.emp_no, departments.dept_noFROM dept_emp de, departmentsWHERE de.dept_no = departments.dept_no AND de.from_date >= '2002-03-01';explainSELECT de.emp_no, departments.dept_noFROM dept_emp de, departmentsWHERE de.dept_no = departments.dept_no AND de.from_date >= '2002-03-01';explain analyzeSELECT STRAIGHT_JOIN de.emp_no, departments.dept_noFROM (select * from dept_emp where from_date >= '2002-03-01' ) de, departmentsWHERE de.dept_no = departments.dept_no ;explainSELECT STRAIGHT_JOIN de.emp_no, departments.dept_noFROM (select * from dept_emp where from_date >= '2002-03-01' ) de, departmentsWHERE de.dept_no = departments.dept_no ;select * from dept_emp where from_date >= '2002-03-01';MYSQL STRIGHT JOIN , ORACLE ORDERED JOIN ;뒷단칼럼 NL 조인 시 랜덤IO 부하가 크다.explain analyzeSELECT employees.emp_no, employees.first_name, employees.last_name FROM employees WHERE emp_no > 450000 AND ( SELECT MAX(salary) FROM salaries WHERE emp_no = employees.emp_no) > 100000;explain analyzeSELECT STRAIGHT_JOIN employees.emp_no, employees.first_name, employees.last_name FROM employees, salaries WHERE employees.emp_no = salaries.emp_no AND employees.emp_no > 450000 AND salaries.salary > 100000;SELECT /*+NO_MERGE(VA)*/ VA.DEPTNO, DNAME, VA_CNT FROM ( SELECT DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE ('19800101','YYYYMMDD') AND TO TO_DATE('19821231'),'YYYMMDD') GROUP BY DEPTNO ) VA, DEPT DSELECT * FROM SCOTT25.TEST;SELECT /*+ gather_plan_statistics no_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT D.DEPT_NAME , E.EMP_NO , O.ORD_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ORDER BY D.DEPT_NAME , E.HIRE_DATE , O.DEV_DATE ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE INDEX ORDERS_MEP_DEV_DT_IDX ON ORDERS ( ORD_EMP_NO, DEV_DATE );SELECT * FROM SCOTT25.TEST;SELECT D.DEPT_NAME , E.EMP_NO , O.ORD_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT D.DEPT_NAME , E.EMP_NO , O.ORD_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ VA.DEPT_NAME , VA.EMP_NO , VA.EMP_NAME, Y.DEV_DATEFROM (SELECT D.DEPT_NAME , E.EMP_NO, E.EMP_NAMEFROM DEPTS D , EMPS EWHERE D.DEPT_NO = E.DEPT_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 ORDER BY D.DEPT_NAME, E.HIRE_DATE ) VA, ORDERS YWHERE VA.EMP_NO = Y.ORD_EMP_NOAND Y.DEV_DATE > '20090109'; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); --SET PAGES 0 SELECT /*+ gather_plan_statistics NO_MERGE(V) */ EMPNO, DEPTNOFROM ( SELECT EMPNO, DEPTNO FROM EMP WHERE ENAME = 'SMITH' )WHERE EMPNO = 7369; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); --> PUSH PREDICATE FILTER PUSH DOWN 외부 PK 조건이 더좋다면 인라인뷰는 무시.CREATE TABLE EMPLOYEE AS SELECT * FROM SCOTT25.EMPLOYEES;CREATE TABLE DEPARTMENTS AS SELECT * FROM SCOTT25.DEPARTMENTS;ALTER TABLE EMPLOYEE ADD CONSTRAINT PK_EMPLOYEES PRIMARY KEY(EMPLOYEE_ID);ALTER TABLE DEPARTMENTS ADD CONSTRAINT PK_DEPARTMENTS PRIMARY KEY(DEPARTMENT_ID);CREATE TABLE TB_PRDT AS SELECT * FROM SCOTT25.TB_PRDT;ALTER TABLE TB_PRDT ADD CONSTRAINT TB_PRDT_PK PRIMARY KEY(PRDT_CD);CREATE TABLE TB_PRDT_SALE DAY AS SELECT * FROM SCOTT25.TB_PRDT_SALE_DAY;ALTER TABLE TB_PRDT_SALE_DAY ADD CONSTRAINT TB_PRDT_SALE__DAY_PK PRIMARY KEY(SALE_NO);"SELECT /*+ FULL(B) */ B.PRDT_CD, MIN(B.PRDT_NM),SUM(A.SALE_CNT), SUM(A.SALE_AMT)FROM TB_PRDT_SALE_DAY A, TB_PRDT BWHERE A.SALE_DT BETWEEN '20140101' AND '20151231' AND -- 로 수정 A.PRDT_CD = B.PRDT_CDGROUP BY B.PRDT_CD;"MERGE -> 인라인뷰로 쓸것인가, 조인으로 쓸것인가explainSELECT employees.emp_no, employees.first_name, employees.last_name FROM employees WHERE emp_no > 450000 AND ( SELECT MAX(salary) FROM salaries WHERE emp_no = employees.emp_no) > 100000;explainSELECT employees.emp_no, employees.first_name, employees.last_name FROM employees WHERE emp_no > 450000 AND ( SELECT MAX(salary) FROM salaries WHERE emp_no = employees.emp_no) > 100000;SELECT /*+ gather_plan_statistics LEADING(V.C V.L V.D) */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK' ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK' ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics ORDERED */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND e.job_id = 'ST_CLERK' AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics ORDERED */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.job_id = 'ST_CLERK' AND e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name FROM employees e, v_dept v WHERE e.department_id = v.department_id AND v.department_name = 'Shipping' AND v.postal_code = '99236' AND v.region_id = 2 AND e.job_id = 'ST_CLERK'; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE OR REPLACE VIEW v_dept ASSELECT d.department_id, d.department_name, d.manager_id, l.location_id, l.postal_code, l.city, c.country_id, c.country_name, c.region_id FROM departments d, locations l, countries c WHERE d.location_id = l.location_id AND l.country_id = c.country_id; ALTER TABLE DEPARTMENTS ADD CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID);ALTER TABLE LOCATIONS ADD CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID);ALTER TABLE COUNTRIES ADD CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY (COUNTRY_ID);ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID); CREATE INDEX loc_postal_idx ON locations (postal_code);CREATE INDEX dept_name_idx ON departments (department_name);CREATE INDEX coun_region_idx ON countries (region_id);CREATE INDEX emp_department_ix ON employees (department_id);DROP TABLE DEPARTMENTS;CREATE TABLE DEPARTMENTS AS SELECT * FROM SCOTT25.DEPARTMENTS;CREATE TABLE EMPLOYEES AS SELECT * FROM SCOTT25.EMPLOYEES;CREATE TABLE COUNTRIES AS SELECT * FROM SCOTT25.COUNTRIES;CREATE TABLE LOCATIONS AS SELECT * FROM SCOTT25.LOCATIONS;SELECT /*+ gather_plan_statistics QB_NAME(OUTER) */ *FROM ( /*+ QB_NAME(INNER)*/ SELECT ROWNUM, EMP_NAME, HIRE_DATE, SAL, DEPT_NO FROM EMPS WHERE DEPT_NO = 30 ) VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); SELECT /*+ gather_plan_statistics NO_PUSH_PRED(V) */ *FROM VU_EMP_DEPT30 VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(V) */ *FROM VU_EMP_DEPT30 VWHERE HIRE_DATE BETWEEN '20090101' AND '20100101';select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE OR REPLACE VIEW VU_EMP_GROUP_BY_DEPTNOASSELECT DEPT_NO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL FROM EMPS GROUP BY DEPT_NO; CREATE OR REPLACE VIEW VU_EMP_DEPT30ASSELECT EMP_NAME, HIRE_DATE, SAL, DEPT_NO FROM EMPS WHERE DEPT_NO = 30;CREATE INDEX EMP_HIRE_DT_IDX02 ON EMPS (HIRE_DATE);SELECT * FROM VU_EMP_GROUP_BY_DEPTNO;SELECT * FROM VU_EMP_DEPT30;"SELECT /*+ FULL(B) */ B.PRDT_CD, MIN(B.PRDT_NM),SUM(A.SALE_CNT), SUM(A.SALE_AMT)FROM TB_PRDT_SALE_DAY A, TB_PRDT BWHERE A.SALE_DT BETWEEN '20140101' AND '20151231' AND -- 로 수정 A.PRDT_CD = B.PRDT_CDGROUP BY B.PRDT_CD;"SELECT /*+ gather_plan_statistics */ A.*, B.PRDT_NMFROM (SELECT A.PRDT_CD, SUM(A.SALE_CNT) AS SALE_CNT, SUM(A.SALE_AMT) AS SALE_AMTFROM TB_PRDT_SALE_DAY AWHERE A.SALE_DT BETWEEN '20140101' AND '20151231'GROUP BY A.PRDT_CD) A, TB_PRDT BWHERE A.PRDT_CD = B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT * FROM SCOTT25.TEST;SELECT /*+ gather_plan_statistics FULL(B) LEADING(B) */ B.PRDT_CD, MIN(B.PRDT_NM),SUM(A.SALE_CNT), SUM(A.SALE_AMT)FROM TB_PRDT_SALE_DAY A, TB_PRDT BWHERE A.SALE_DT BETWEEN '20140101' AND '20151231' AND -- 로 수정 A.PRDT_CD = B.PRDT_CDGROUP BY B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));0.011SELECT /*+ FULL(B) */B.PRDT_CD, B.PRDT_NM,A.SALE_CNT, A.SALE_AMTFROM ( SELECT SUM(A.SALE_CNT) AS SALE_CNT, SUM(A.SALE_AMT) AS SALE_AMT, PRDT_CD FROM TB_PRDT_SALE_DAY A WHERE SALE_DT BETWEEN '20140101' AND '20151231' GROUP BY PRDT_CD ) A , TB_PRDT BWHERE A.PRDT_CD = B.PRDT_CD;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));0.012 select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));CREATE TABLE TB_PRDT AS SELECT * FROM SCOTT25.TB_PRDT;ALTER TABLE TB_PRDT ADD CONSTRAINT TB_PRDT_PK PRIMARY KEY(PRDT_CD);CREATE TABLE TB_PRDT_SALE_DAY AS SELECT * FROM SCOTT25.TB_PRDT_SALE_DAY;ALTER TABLE TB_PRDT_SALE_DAY ADD CONSTRAINT TB_PRDT_SALE__DAY_PK PRIMARY KEY(SALE_NO);CREATE INDEX ORDERS_MEP_DEV_DT_IDX ON ORDERS ( ORD_EMP_NO, DEV_DATE );SELECT /*+ gather_plan_statistics INDEX(O ORDERS_EMP_DEV_DT_IDX) */ D.DEPT_NAME , E.EMP_NO , O.DEV_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ORDER BY D.DEPT_NAME, E.HIRE_DATE, O.DEV_DATE ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ D.DEPT_NAME , E.EMP_NO , O.ORD_DATE FROM DEPTS D , EMPS E , ORDERS OWHERE D.DEPT_NO = E.DEPT_NO AND E.EMP_NO = O.ORD_EMP_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 AND O.DEV_DATE >= '20090109' ; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ VA.DEPT_NAME , VA.EMP_NO , VA.EMP_NAME, Y.DEV_DATEFROM (SELECT D.DEPT_NAME , E.EMP_NO, E.EMP_NAMEFROM DEPTS D , EMPS EWHERE D.DEPT_NO = E.DEPT_NO AND D.MANAGER_EMPNO = '301' AND E.SAL >= 30000 ORDER BY D.DEPT_NAME, E.HIRE_DATE ) VA, ORDERS YWHERE VA.EMP_NO = Y.ORD_EMP_NOAND Y.DEV_DATE > '20090109'; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); --SET PAGES 0 SELECT /*+ gather_plan_statistics NO_MERGE(V) */ EMPNO, DEPTNOFROM ( SELECT EMPNO, DEPTNO FROM EMP WHERE ENAME = 'SMITH' ) VWHERE EMPNO = 7369; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); ORDER BY D.DEPT_NAME , E.HIRE_DATE , O.DEV_DATE ;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics no_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'advanced ALLSTATS LAST'));SELECT /*+ gather_plan_statistics NO_MERGE(VA)*/ VA.DEPTNO, DNAME, VA.CNT FROM (SELECT /* no_merge */ DEPTNO, COUNT(*) CNT FROM EMP WHERE HIREDATE BETWEEN TO_DATE('19800101', 'YYYYMMDD') AND TO_DATE('19821231', 'YYYYMMDD') GROUP BY DEPTNO) VA, DEPT DWHERE VA.DEPTNO = D.DEPTNO;select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));--SET PAGES 0 SELECT /*+ gather_plan_statistics NO_MERGE(V) */ EMPNO, DEPTNOFROM ( SELECT EMPNO, DEPTNO FROM EMP WHERE ENAME = 'SMITH' )WHERE EMPNO = 7369; select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST')); SELECT COUNT(1) FROM EMPLOYEE;SELECT * FROM SCOTT25.TEST;SELECT/*+ GATHER_PLAN_STATISTICS LEADING(A) USE_NL(B) INDEX(A TB_TRD_DAY_PK) */A.EXPORTER_NO,B.EXPORTER_NM,SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNTFROM TB_TRD_DAY A LEFT OUTER JOIN TB_EXPORTER BON ( A.EXPORTER_NO = B.EXPORTER_NO)WHERE A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NO, B.EXPORTER_NMORDER BY A.EXPORTER_NO;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT/*+ GATHER_PLAN_STATISTICS LEADING(A) USE_NL(B) INDEX(A TB_TRD_DAY_PK) */A.EXPORTER_NO,SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNT,( SELECT EXPORTER_NM FROM TB_EXPORTER B WHERE B.EXPORTER_NO = A.EXPORTER_NO ) EXPORTER_NMFROM TB_TRD_DAY A WHERE A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NO, B.EXPORTER_NMORDER BY A.EXPORTER_NO;Sselect * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ T1.C1, T1.C2, T1.C3, (SELECT T2.C3 FROM TMP2 T2 WHERE T2.C1 = T1.C1) T2_C3, (SELECT T3.C3 FROM TMP3 T3 WHERE T3.C1 = T1.C1) T3_C3 FROM TMP1 T1 ORDER BY T1.C1, T1.C2;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ rownum rnum, x.*from ( select c1, c2, c3, ( select t2.c3 from TMP2 t2 where t2.c1 = t1.c1) t2_c3 from TMP1 t1 order by c1, c2 ) xwhere rownum <= 10;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ rownum rnum, x.*from ( select t1.c1, t1.c2, t2.c3 from TMP1 t1, TMP2 t2 WHERE t2.c1 = t1.c1 order by c1, c2 ) xwhere rownum <= 10;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics index(a pk_t_suju_dtl ) */ max(a.suju_no),substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl awhere Exists ( Select /*+ no_unnest */ 1 from s_suju b where b.jumun_dt like '2007%' and b.suju_no = a.suju_no ) ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics index(a pk_t_suju_dtl) */ max(a.suju_no), substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl awhere exists (select /*+ NS_SJ */ 'x' from s_suju b where b.suju_no = a.suju_no and b.jumun_dt like '2007%' );select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics LEADING(A B C) FULL(A) USE_NL(A B C) */ A.CUST_ID, A.CUST_NM, B.CUST_ID, B.SEQ, B.CUST_INFOFROM TB_CUST A, TB_CUST_DTL B, TB_ORD CWHERE A.CUST_NM LIKE 'A%' AND A.CUST_ID = B.CUST_ID AND C.CUST_ID = B.CUST_ID AND C.ORD_DT LIKE '2015%' GROUP BY A.CUST_ID, A.CUST_NM, B.CUST_ID, B.SEQ, B.CUST_INFO;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ c4, c5, c6 from TMP1where c6 >= 1 and c6 <= 450210and exists (select /*+ NL_SJ */ 'x' from TMP2 where TMP2.c1 = TMP1.c4 and TMP2.c3 >= 100000 and TMP2.c3 <= 100004 ) ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics USE_HASH(TMP1) */ c4, c5, c6 from TMP1where c6 >= 1 and c6 <= 450210and exists (select /*+ unnest*/ 'x' from TMP2 where TMP2.c1 = TMP1.c4 and TMP2.c3 >= 100000 and TMP2.c3 <= 100004 );select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics USE_NL(TMP1) */ c4, c5, c6 from TMP2where TMP2.c3 >= 100000 and TMP2.c3 <= 100004and exists (select /*+ no_unnest*/ 'x' from TMP1 where TMP1.c4 = TMP2.c1 and TMP1.c6 >= 1 and TMP1.c6 <= 450210 );select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ TMP1.c4, TMP1.c5, TMP1.c6 from TMP2, TMP1where TMP2.c3 >= 100000 and TMP2.c3 <= 100004AND TMP1.c4 = TMP2.c1AND TMP1.c6 >= 1 and TMP1.c6 <= 450210;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ QB_NAME(MAIN) USE_HASH(d@MAIN e@SUM) */ d.department_id, d.department_name, d.location_idFROM departments dWHERE EXISTS(SELECT /*+ QB_NAME(SUB) */ NULL FROM employees e where e.department_id = d.department_id) and d.location_id = 1700;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));set pages 0select /*+ gather_plan_statistics */ max(a.suju_no),substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl a, s_suju bwhere b.jumun_dt like '2007%' and a.suju_no = b.suju_no;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));CREATE TABLE TB_TRD_DAY ASSELECT * FROM SCOTT25.TB_TRD_DAY;CREATE TABLE TB_EXPORTER ASSELECT * FROM SCOTT25.TB_EXPORTER;ALTER TABLE TB_TRD_DAYADD CONSTRAINT TB_TRD_DAY_PKPRIMARY KEY (TRD_DT, INSU_CD, INSU_DETAIL_CD );ALTER TABLE TB_EXPORTERADD CONSTRAINT TB_EXPORTER_PKPRIMARY KEY (EXPORTER_NO);select /*+ gather_plan_statistics */ c4, c5, c6 from TMP1where c6 >= 1 and c6 <= 450210and exists (select /*+ UNNEST HASH_SJ */ 'x' from TMP2 where TMP2.c1 = TMP1.c4 and TMP2.c3 >= 100000 and TMP2.c3 <= 100004 ) ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select * from scott25.TMP1;CREATE TABLE TMP1 ASselect * from scott25.TMP1;CREATE TABLE TMP2 ASselect * from scott25.TMP2;CREATE TABLE TMP3 ASselect * from scott25.TMP3;CREATE INDEX TMP1_IDX_01 ON TMP1(C4,C5);CREATE INDEX TMP1_IDX_02 ON TMP1(C5);CREATE INDEX TMP2_IDX_01 ON TMP2(C2,C1);ALTER TABLE TMP2 ADD CONSTRAINT PK_TMP2 PRIMARY KEY(C1);CREATE INDEX TMP3_IDX_01 ON TMP3(C1,C2);ALTER TABLE TMP3 ADD CONSTRAINT PK_TMP3 PRIMARY KEY(C1);SELECT * FROM TMP1;select /*+ gather_plan_statistics gb_name(main) leading(d@sub) use_nl(e@main)*/ e. department_id, e.first_name, e.last_name, email, e.salary, e.commission_pct from employees ewhere exists (select /*+ qb_name(sub)*/null from departments d where d.location_id = 1800 and d.department_id = e.department_id);select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select * from v$parameter where name like '%opti%';optimizer_features_enable 2 11.2.0.4 11.2.0.4 19.1.0SELECT /*+ gather_plan_statistics QB_NAME(MAIN)*/ d.department_id, d.department_name, d.location_id FROM departments d WHERE EXISTS (SELECT /*+ QB_NAME(SUB) */ NULL FROM employees e WHERE e.department_id = d.department_id) AND d.location_id = 1700;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ QB_NAME(MAIN) USE_HASH(n@MAIN t@SUM) */ d.department_id, d.department_name, d.location_idFROM departments dWHERE EXISTS(SELECT /*+ QB_NAME(SUB) */ NULL FROM employees e where e.department_id = d.department_id) and d.location_id = 1700;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));set pages 0select /*+ gather_plan_statistics */ max(a.suju_no), substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl a, (select * from s_suju where jumun_dt like '2007%' ) bwhere a.suju_no = b.suju_no;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));set pages 0select /*+ gather_plan_statistics USE_NL(a b) */ max(a.suju_no),substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl a, s_suju bwhere b.jumun_dt like '2007%' and a.suju_no = b.suju_no;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ max(a.suju_no),substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl a, s_suju bwhere b.jumun_dt between '20070101' and '20071231' and a.suju_no = b.suju_noand a.suju_no = (select max(suju_no) from s_suju where jumun_dt like '2007%' );select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select count(1) from s_suju_dtl 4136970select count(1) from s_suju 827447SELECT COUNT(1)FROM TB_TRD_DAY; 1000000SELECT COUNT(1)FROM TB_EXPORTER; 100000SELECT/*+ GATHER_PLAN_STATISTICS */A.EXPORTER_NO,B.EXPORTER_NM,SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNTFROM TB_TRD_DAY A LEFT OUTER JOIN TB_EXPORTER BON ( A.EXPORTER_NO = B.EXPORTER_NO)WHERE A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NO, B.EXPORTER_NMORDER BY A.EXPORTER_NO;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT /*+ GATHER_PLAN_STATISTICS */ A.EXPORTER_NO, ( SELECT EXPORTER_NM FROM TB_EXPORTER B WHERE A.EXPORTER_NO = B.EXPORTER_NO ) AS EXPORTER_NM, SUM(A.TRD_CNT - A.CNCL_CNT) TOT_CNTFROM TB_TRD_DAY AWHERE A.TRD_DT BETWEEN '20100101' AND '20151231' GROUP BY A.EXPORTER_NOORDER BY A.EXPORTER_NO;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics */ T1.C1, T1.C2, T1.C3, (SELECT T2.C3 FROM TMP2 T2 WHERE T2.C1 = T1.C1) T2_C3, (SELECT T3.C3 FROM TMP3 T3 WHERE T3.C1 = T1.C1) T3_C3 FROM (SELECT * FROM TMP1 T1 ORDER BY T1.C1, T1.C2 ) T1 ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT /*+ gather_plan_statistics USE_HASH(T1 T2 T3 ) */ T1.C1, T1.C2, T1.C3, T2.C3 AS T2_C3, T3.C3 AS T3_C3 FROM TMP1 T1, TMP2 T2, TMP3 T3 WHERE T2.C1(+) = T1.C1AND T3.C1(+) = T1.C1ORDER BY T1.C1, T1.C2 ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT COUNT(1) FROM TMP1;CREATE TABLE TMP1 ASSELECT * FROM SCOTT25.TMP1;CREATE TABLE TMP2 ASSELECT * FROM SCOTT25.TMP2;CREATE TABLE TMP3 ASSELECT * FROM SCOTT25.TMP3;CREATE INDEX TMP1_IDX_01 ON TMP1(C1);CREATE INDEX TMP2_IDX_01 ON TMP2(C1);CREATE INDEX TMP3_IDX_01 ON TMP3(C1);select /*+ gather_plan_statistics */ rownum rnum, x.*from ( select c1, c2, c3, ( select t2.c3 from TMP2 t2 where t2.c1 = t1.c1) t2_c3 from TMP1 t1 order by c1, c2 ) xwhere rownum <= 10;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ rownum rnum, x.*from ( select c1, c2, c3, ( select t2.c3 from TMP2 t2 where t2.c1 = t1.c1) t2_c3 from (SELECT * FROM TMP1 t1 WHERE ROWNUM <= 10 ) t1 order by c1, c2 ) x;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ rownum rnum, x.*, (select t2.c3 from TMP2 t2 where t2.c1 = x.c1) t2_c3from ( select c1, c2, c3 from TMP1 t1 order by c1, c2 ) xwhere rownum <= 10;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));PARSING EXECUTE FETCH 스칼라쿼리는 패치단계에서 가져옴select /*+ gather_plan_statistics */ max(a.suju_no), substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl a, (select * from s_suju where jumun_dt like '2007%' ) bwhere a.suju_no = b.suju_no;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));select /*+ gather_plan_statistics */ max(a.suju_no), substr(max(a.suju_no),1,9)||to_number(substr(max(a.suju_no),10,6)+1)from s_suju_dtl awhere exists (select /*+ no_unnest */ 'x' from s_suju b where b.suju_no = a.suju_no and b.jumun_dt like '2007%' );select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));EXPLAIN PLAN FOR SELECT /*+ gather_plan_statistics LEADING(A B C) FULL(A) USE_NL(B) */ A.CUST_ID, A.CUST_NM, B.CUST_ID, B.SEQ, B.CUST_INFOFROM TB_CUST A, TB_CUST_DTL BWHERE A.CUST_NM LIKE 'A%' AND A.CUST_ID = B.CUST_ID AND exists ( SELECT /*+ UNNEST NS_SJ */ 'X' FROM TB_ORD C WHERE C.CUST_ID = B.CUST_ID AND C.ORD_DT BETWEEN '20150101' AND '20151231' )GROUP BY A.CUST_ID, A.CUST_NM, B.CUST_ID, B.SEQ, B.CUST_INFO;SELECT * FROM TABLE(DBMS_XPLAN.display); select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));SELECT * FROM SCOTT25.TB_CUST;CREATE TABLE TB_CUST ASSELECT * FROM SCOTT25.TB_CUST;CREATE TABLE TB_CUST_DTL ASSELECT * FROM SCOTT25.TB_CUST_DTL;CREATE TABLE TB_ORD ASSELECT * FROM SCOTT25.TB_ORD;ALTER TABLE TB_CUST ADD CONSTRAINT TB_CUST_PK PRIMARY KEY (CUST_ID);ALTER TABLE TB_CUST_DTL ADD CONSTRAINT TB_CUST_DTL_PK PRIMARY KEY (CUST_ID, SEQ);ALTER TABLE TB_ORD ADD CONSTRAINT TB_ORD_PK PRIMARY KEY(ORD_NO);CREATE INDEX TB_ORD_IDX01 ON TB_ORD(CUST_ID);CATS : TB_CUST, TB_CUST_DTL, TB_ORDselect /*+ gather_plan_statistics */ c4, c5, c6 from TMP1where c6 >= 1 and c6 <= 450210and exists (select /*+ HASH_SJ NO_SWAP_JOIN_INPUT */ 'x' from TMP2 where TMP2.c1 = TMP1.c4 and TMP2.c3 >= 100000 and TMP2.c3 <= 100004 ) ;select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
'DBA' 카테고리의 다른 글
| 서브쿼리 동작 방식을 제어하는 힌트들 (1) | 2024.09.13 |
|---|---|
| 접근 방법을 결정하는 힌트절? USE_NL, USE_HASH (0) | 2024.09.13 |
| MySQL8 - Semi Join 과 Anti Join - 안티조인 세미조인 (1) | 2024.09.12 |
| DBMS_XPLAN.DISPLAY_CURSOR 결과 보는 법 (0) | 2024.09.12 |
| 접근 순서를 결정하는 힌트절 ? ORDERED, LEADING (0) | 2024.09.12 |
댓글