ACCESS 경로를 변경하는 힌트(INDEX_ASC)
구로디지털 오엔제이프로그래밍실무교육센터
앞선
강좌의 INDEX 힌트와 동일한데 보이는 그대로 인덱스를 스캔 하는데 오름차순(ASCENDING) 스캔 하라는 의미의 힌트 입니다. 이 힌트를
이용하여 데이터를 추출하게 되면 화면에 나타나는 데이터는 인덱스 키를 기준으로 오름차순 정렬된 모습으로 나타나게 된다.
만약 EMP 테이블에서 사원들의 이름과 급여를 출력하는데 이름순으로 정렬을 하라고 했을
때…
다음과 같이
하면 되지만 이 경우엔 SORT를 위한 TEMP 영역을
사용하게 되므로 많은 양의 데이터를 추출하는 경우엔 상당한 OVERHEAD가 따르게 됩니다. 되도록 이런 식으로는 사용하지 않는 것이 좋다. (예제에서는
데이터가 몇 건 되지 않는 EMP 테이블의 결과이지만 대용량의 테이블 이라면 속도 차이가 상당할
것입니다.)
select
ename, sal
from emp
order
by ename
asc
---------------------------------------------------------------------
Operation Object Name Rows Bytes
Cost
-------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS 15
4
SORT ORDER BY 15 135
4
TABLE ACCESS FULL SCOTT.EMP 15
135 3
이번엔
힌트를 이용해 보도록 합니다.
select
ename, sal
from emp
where ename
>
' '
---------------------------------------------------------------------
Operation Object Name Rows Bytes
Cost
-------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS 15
2
TABLE ACCESS BY INDEX ROWID SCOTT.EMP
15 135 2
INDEX
<st1:placetype
w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_ENAME 15
1
이번에는 INDEX_ASC를 사용해 보도록 하죠… INDEX 힌트와 동일한 결과를
보인다.
select
ename, sal
from emp
where ename
>
' '
---------------------------------------------------------------------
Operation Object Name Rows Bytes
Cost
-------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS 15
2
TABLE ACCESS BY INDEX ROWID SCOTT.EMP
15 135 2
INDEX
<st1:placetype
w:st="on">RANGE</st1:placetype> SCAN SCOTT.IDX_EMP_ENAME 15
1
[실습]
create table myemp1
(empno number not null primary key,
ename varchar2(100),
deptno number,
addr varchar2(100),
sal number
)
create table mydept1
(deptno number,
dname varchar2(100)
)
insert into mydept1 values (0, '인사팀');
insert into mydept1 values (1, '회계팀');
insert into mydept1 values (2, '영업팀');
insert into mydept1 values (3, '기획팀');
insert into mydept1 values (4, '교육팀');
commit
-- 실습을 위해 myemp1을
2000만건 만들자.
DECLARE
v_c NUMBER := 1;
BEGIN
WHILE (v_c <= 10000000)
LOOP
insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c,
1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c,
1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c,
1000000));
v_c := v_c + 1;
insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c,
1000000));
v_c := v_c + 1;
END LOOP;
commit;
END;
create index idx_myemp1_deptno on myemp1(deptno)
analyze table myemp1 compute statistics
analyze table mydept1 compute statistics
-------------------------------------------------- 실습데이터 생성
끝
SQL> select count(*) from myemp1;
COUNT(*)
----------
10000000
SQL> select count(*) from mydept1;
COUNT(*)
----------
5
SQL> select index_name, table_name from
user_indexes
2 where table_name = 'MYEMP1';
INDEX_NAME TABLE_NAME
------------------------------
------------------------------
IDX_MYEMP1_DEPTNO MYEMP1
SYS_C0011302 MYEMP1
이름으로
인덱스를 만들자…
SQL> create index idx_myemp1_ename on
myemp1(ename);
인덱스가
생성되었습니다.
실습을 위해
인덱스를 숨기자.
SQL> alter index idx_myemp1_ename invisible;
인덱스가
변경되었습니다.
인덱스가
없는 상태에서 이름으로 조건 검색을 하니 9초 정도 걸린다.
SQL> select count(ename) from myemp1
2 where ename = '홍길동111';
COUNT(ENAME)
------------
0
경 과: 00:00:09.95
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 16956 (1)|
00:03:24 |
| 1 | SORT AGGREGATE | | 1 | 13 |
| |
|* 2 | TABLE ACCESS FULL| MYEMP1 | 1 | 13 | 16956 (1)|
00:03:24
이번에는
인덱스를 보이도록 하고 다시 검색하자.
SQL> alter index idx_myemp1_ename visible;
바로
데이터가 보인다.
SQL> select count(ename) from myemp1
2 where ename = '홍길동111';
COUNT(ENAME)
------------
0
경 과: 00:00:00.03
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3
(0)|
| 1 | SORT AGGREGATE | | 1 | 13
| |
|* 2 | INDEX RANGE SCAN| IDX_MYEMP1_ENAME | 1 |
13 | 3
SQL> -- 이번에는 이름으로 오름차순으로 데이터를 가지고 와 보자.
SQL> -- oracle 11g에서 했는데 너무 느리다.
디스크 소트도 한번 하고…
SQL> select empno, ename from myemp1
2 order by ename asc
3 /
10000000 개의 행이 선택되었습니다.
경 과: 00:02:16.57 //2분 넘었다.
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 10M| 171M| | 74120
(1)|
| 1 | SORT ORDER BY | | 10M| 171M| 268M| 74120
(1)|
| 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 171M|
| 16931
Statistics
----------------------------------------------------------
268 recursive calls
8 db block gets
61364 consistent gets
95565 physical reads
1620 redo size
294444947 bytes sent via SQL*Net to
client
7333741 bytes received via SQL*Net from
client
666668 SQL*Net roundtrips to/from
client
0 sorts (memory)
1 sorts (disk)
10000000 rows processed
ename 인덱스 영역에서 가지고 오면 금방 가지고 올텐데… 힌트를 써 보자.
SQL> select
Count(ename)
from myemp1
where ename is not null;
약 6초 정도 걸린다. 인덱스 풀 스캔해서…
이번에는 max(sal), min(sal)을 구해보자.
SQL> create index idx_myemp1_sal on myemp1(sal)
2 /
인덱스가
생성되었습니다.
SQL>-- sal 인덱스를 이용하여 간단하게 가지고 온다.
SQL> select max(sal) from myemp1;
MAX(SAL)
----------
999999
경 과: 00:00:00.01
Execution Plan
| 0 | SELECT STATEMENT | | 1
| 4 | 3
| 1 | SORT AGGREGATE | | 1 | 4
|
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_MYEMP1_SAL | 1
| 4 | 3
SQL> -- 최소값도 인덱스로 가면 금방 가지고 올 수 있다.
SQL> select min(sal) from myemp1;
MIN(SAL)
----------
0
경 과: 00:00:00.03
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1
| 4 | 3
| 1 | SORT AGGREGATE | | 1 | 4
|
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_MYEMP1_SAL | 1
| 4 | 3
그럼, 다음처럼 최대값에서 최소값을 뺄 때는 어떻게
?
myemp1을 FULL SCAN 한다..
SQL> select max(sal) - min(sal) from myemp1;
MAX(SAL)-MIN(SAL)
-----------------
999999
경 과: 00:00:09.93
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 16961 (1)|
00:03:24 |
| 1 | SORT AGGREGATE | | 1 | 4 |
| |
| 2 | TABLE ACCESS FULL| MYEMP1 | 10M| 38M| 16961 (1)|
다음처럼
해보자.
SQL> with a as (
2 select max(sal) max from
myemp1
3 ) , b as (
4 select min(sal) min from
myemp1
5 )
6* select a.max - b.min from a,
b
A.MAX-B.MIN
-----------
999999
경 과: 00:00:00.01 //바로
나온다.
Execution Plan
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26
| 6
| 1 | NESTED LOOPS | | 1 | 26
| 6
| 2 | VIEW | | 1 | 13
| 3
| 3 | SORT AGGREGATE | | 1 | 4
|
| 4 | INDEX FULL SCAN (MIN/MAX)| IDX_MYEMP1_SAL |
1 | 4 |
| 5 | VIEW | | 1 | 13
| 3
| 6 | SORT AGGREGATE | | 1 | 4
|
| 7 | INDEX FULL SCAN (MIN/MAX)| IDX_MYEMP1_SAL |
1 | 4 |
물론 다음과
같이 힌트를 사용해도 된다.
with a as (
select /*+ index(myemp1 idx_myemp1_sal) */ max(sal) max
from myemp1
) , b as (
select /*+ index(myemp1 idx_myemp1_sal) */ min(sal) min
from myemp1
)
select a.max - b.min from a, b
