SQL튜닝 Oracle autotrace를 이용하여 실행계획 보기

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL>
SQL> create index job_deptno_index on emp(job, deptno);

Index created.

//인덱스를 만들어서 셀렉트시에 밑에 실행계획

SQL> select * from emp
  2  where job = 'CLERK' AND deptno = 30;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'JOB_DEPTNO_INDEX' (NON-UNIQUE)



SQL> drop index job_deptno_index;

Index dropped.

SQL> select * from emp
  2  where job = 'CLERK' AND deptno = 30;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'