SQL TUNNING - 1

SELECT A.ORDER_ID,
       A.ORDER_DATE,
       A.ORDER_MODE ,
       C.FIRST_NAME EMPNAME,
       D.CUST_FIRST_NAME ,
       B.PRODUCT_ID,
       B.UNIT_PRICE,
       B.QUANTITY
  FROM ORDERS A,
       ORDER_ITEMS B,
       EMPLOYEES C,
       CUSTOMERS D
 WHERE A.ORDER_ID = B.ORDER_ID
   AND A.EMPLOYEE_ID = C.EMPLOYEE_ID
   AND A.CUSTOMER_ID = D.CUSTOMER_ID
   AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE < TO_DATE('20120102', 'YYYYMMDD') ;

alter session set statistics_level=all;
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));
select * from table(dbms_xplan.display_cursor(null, null, 'allstats last advanced'));

* ORDER_ITEM을보면 E-Rows가 29M으로 많이 조회된다

--사전 분석
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUM_DISTINCT
  from dba_tab_columns
 where TABLE_NAME IN ('ORDER_ITEMS','ORDERS','CUSTOMERS','EMPLOYEES') 
   and NUM_DISTINCT > 0
 order by NUM_DISTINCT DESC;

create index IDX_ORDITEM_ORDID on order_items(ORDER_ID);

* A-Time이나 Buffer등 확 줄은 것을 확인할 수 있다.

-- 순서 지정
순서 힌트 지정 이유는 옵티마이저가 설정한 최적실행 경로 순서를 힌트로 줌으로써 혹시 나중에 실행 순서가 바뀌더라도 이전의 실행순서를 따르게 하기 위함
SELECT /*+ LEADING (A, C, D, B) */
       A.ORDER_ID,
       A.ORDER_DATE,
       A.ORDER_MODE ,
       C.FIRST_NAME EMPNAME,
       D.CUST_FIRST_NAME ,
       B.PRODUCT_ID,
       B.UNIT_PRICE,
       B.QUANTITY
  FROM ORDERS A,
       ORDER_ITEMS B,
       EMPLOYEES C,
       CUSTOMERS D
 WHERE A.ORDER_ID = B.ORDER_ID
   AND A.EMPLOYEE_ID = C.EMPLOYEE_ID
   AND A.CUSTOMER_ID = D.CUSTOMER_ID
   AND A.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND A.ORDER_DATE < TO_DATE('20120102', 'YYYYMMDD') ;