(Oracle Tip) order by와 인덱스 , 그리고 힌트 _탑크리에듀

[order by와 인덱스 , 그리고 힌트] --현재 MYEMP1 테이블의 ename 칼럼에는 idx_myemp1_ename 이라는 인덱스가 생성됨 select a.index_name, a.column_name, b.visibility from user_ind_columns a, user_indexes b where a.table_name = 'MYEMP1' and a.index_name = b.index_name IDX_MYEMP1_ENAME ENAME VISIBLE -- first_rows 모드는 한건의 데이터를 가지고 오는데 있어 가장 비용이 -- 적게드는 실행계획을 수립하므로 정렬된 곳 즉 인덱스에 가서 데이터를 추출한다. alter session set optimizer_mode = first_rows select ename, sal from myemp1 order by ename ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10M| 190M| 2399K (1)| 07:59:54 | | 1 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 10M| 190M| 2399K (1)| 07:59:54 | | 2 | INDEX FULL SCAN | IDX_MYEMP1_ENAME | 10M| | 34883 (1)| 00:06:59 | ------------------------------------------------------------------------------------------------ -- all_rows 모드는 모든 데이터를 가지고 오는데 있어 가장 비용이 적게드는 -- 살행계획을 생산하므로 FULL TABLE SCAN한다. alter session set optimizer_mode = all_rows select ename, sal from myemp1 order by ename ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10M| 190M| | 76864 (2)| 00:15:23 | | 1 | SORT ORDER BY | | 10M| 190M| 268M| 76864 (2)| 00:15:23 | | 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 190M| | 15658 (2)| 00:03:08 | ------------------------------------------------------------------------------------- -- 그래서 where절에 ename 칼럼을 출현시켜 보자. select ename, sal from myemp1 where ename >= 'ㄱ' order by ename ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10M| 190M| | 76899 (2)| 00:15:23 | | 1 | SORT ORDER BY | | 10M| 190M| 268M| 76899 (2)| 00:15:23 | |* 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 190M| | 15693 (2)| 00:03:09 | ------------------------------------------------------------------------------------- -- 위 경우도 FULL TABLE 스캔한다. 어떻게 개선할 까? -- 옵티마이저 모드를 first_rows로 두면 되겠지만 이경우 조인등의 경우 -- 해시조인 보다는 중첩루프 조인등을 선호하여 전체적인 쿼리 성능이 저하될 수있다. 1. 인덱스 자체를 ename, sal 칼럼 두개로 구성하든지 sal 칼럼을 select 안하면 ename 인덱스로 가서 데이터 추출한다. select ename from myemp1 where ename >= 'ㄱ' order by ename 2. 오라클 힌트를 사용하면 된다. select /*+ index(myemp1 idx_myemp1_ename)*/ ename, sal from myemp1 where ename >= 'ㄱ' order by ename ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 10M| 190M| 2399K (1)| 07:59:54 | | 1 | TABLE ACCESS BY INDEX ROWID| MYEMP1 | 10M| 190M| 2399K (1)| 07:59:54 | |* 2 | INDEX RANGE SCAN | IDX_MYEMP1_ENAME | 10M| | 34883 (1)| 00:06:59 | ------------------------------------------------------------------------------------------------