[Oracle] SQL 튜닝

예제 1. 

-- 튜닝 전
select /*+ gather_plan_statistics */ ename, sal
    from emp
    where sal * 12 = 36000;
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


-- 튜닝 후
select /*+ gather_plan_statistics */ ename, sal
    from emp
    where sal = 36000 / 12;
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

버퍼의 개수가 7개에서 2개로 줄었다.

where 절에 검색 조건의 컬럼을 가공하게 되면 index access 를 할 수 없다.

 

 

 

예제 2.

-- 튜닝 전
select /*+ gather_plan_statistics */ ename, sal, job
    from emp
    where substr(job, 1, 5) = 'SALES';
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


-- 튜닝 후
select /*+ gather_plan_statistics */ ename, sal, job
    from emp
    where job like 'SALES%';
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

like 연산자를 사용할 때, 와일드 카드가 양쪽에 있으면 인덱스를 엑세스 못하고 full table scan 한다.

 

 

 

예제 3. 

-- 튜닝 전
select /*+ gather_plan_statistics */ ename, hiredate
    from emp
    where to_char(hiredate, 'RRRR/MM/DD') = '1981/11/17';
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


-- 튜닝 후
select /*+ gather_plan_statistics */ ename, hiredate
    from emp
    where hiredate = to_date('1981/11/17', 'RRRR/MM/DD');
    
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

 

 

인덱스를 이용한 튜닝 방법 중에 정렬 작업을 일으키는 SQL 튜닝

order by 절을 안 쓰고 데이터를 정렬하는 방법이 있는데, 그게 바로 인덱스를 활용하는 것이다.

인덱스는 데이터를 이미 정렬된 상태로 저장하고 있으므로 인덱스에서 데이터를 읽어오면 정렬된 결과를 볼 수 있다.

order by 절을 대용량 데이터가 있는 테이블에서 수행을 하면 성능이 아주 느려지기 때문에

order by 절 사용을 가급적 자제 하는게 바람직하다.

 

 

 

예제 4. 

-- 튜닝 전
select ename, sal
    from emp
    order by sal asc;
    
    
-- 튜닝 후
select ename, sal
    from emp
    where sal >= 0;

• 숫자 >= 0
• 문자 > ' '
• 날짜 < to_date(’9999/12/31’, ‘RRRR/MM/DD’)

튜닝 전 SQL이 데이터 전체를 보는 SQL이므로 WHERE 절에 위와 같은 조건을 주면 된다.

ORDER BY 절 없이 정렬이 되게 하려면 WHERE 절에 해당 인덱스 컬럼이 조건으로 있어야 한다.

 

 

 

예제 5. 

-- 튜닝 전
select ename, sal
    from emp
    order by sal desc;
    
    
-- 튜닝 후
select /*+ index_desc(emp emp_sal) */ ename, sal
    from emp
    where sal >= 0;

index_desc(테이블명 인덱스명) 이라는 힌트를 줘서 해당 테이블의 인덱스를 descending 하게 스캔한다.

힌트 /*+ index_asc(emp emp_sal) */ 은 emp_sal 인덱스를 ascending 하게 읽는 것이고

힌트 /*+ index_desc(emp emp_sal) */ 은 emp_sal 인덱스를 descending 하게 읽는 것이다.

둘 다 where 절에 sal >= 0 은 있어야 한다.

 

 

 

예제 6.

-- 튜닝 전
select /*+ gather_plan_statistics */ ename, hiredate
    from emp
    order by hiredate desc;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));


-- 튜닝 후
select /*+ gather_plan_statistics index_desc(emp emp_hiredate) */ ename, hiredate
    from emp
    where hiredate <= to_date('9999/12/31', 'RRRR/MM/DD');

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

 

 

 

예제 7. 

-- 튜닝 전
select max(sal)
    from emp;
    
    
-- 튜닝 후    
select /*+ index_desc(emp emp_sal) */ sal
    from emp
    where sal >= 0 and rownum = 1;

그룹 함수도 정렬 작업이 내부적으로 일어난다.