SQL> create table t_emp
2 as
3 select * from scott.emp, (select rownum no from dual connect by level <= 1000)
4 order by dbms_random.value;
SQL> alter table t_emp add constraint t_emp_pk primary key(empno, no);
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname => user
4 , tabname => 't_emp'
5 , method_opt => 'forcolumns sal');
6 end;
7 /
SQL> set autotrace traceonly exp
SQL> select /*+ all_rows */ * from t_emp
2 where sal >= 5000
3 order by empno, no;
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 41000 | 26 (8) |
| 1 | SORT ORDER BY | | 1000 | 41000 | 26 (8) |
| 2 | TABLE ACCESS FULL|T_EMP | 1000 | 41000 | 25 (4) |
----------------------------------------------------------------
SQL> select /*+ first_rows */ * from t_emp
2 where sal >= 5000
3 order by empno, no;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 41000 | 13900 (1)|
| 1 | TABLE ACCESS BY INDEX ROWID |T_EMP | 1000 | 41000 | 13900 (1)|
| 2 | INDEX FULL SCAN |T_EMP_PK | 14000 | | 37 (3)|
----------------------------------------------------------------------------
SQL> set autotrace traceonly exp
SQL> select /*+ first_rows */ * from t_emp
2 where sal >= 5001
3 order by empno, no;
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 26 (8) |
| 1 | SORT ORDER BY | | 1 | 41 | 26 (8) |
| 2 | TABLE ACCESS FULL|T_EMP | 1 | 41 | 25 (4) |
----------------------------------------------------------------
SQL> select /*+ rule */ * from t_emp
2 where sal >= 5001
3 order by empno, no;
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID |T_EMP |
| 2 | INDEX FULL SCAN |T_EMP_PK |
------------------------------------------------
SQL> select /*+ first_rows(10) */ * from t_emp
2 where sal >= 2000
3 order by empno, no;
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 451 | 28 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID |T_EMP | 5999 | 240K| 28 (0)|
| 2 | INDEX FULL SCAN |T_EMP_PK | 26 | | 2 (0)|
----------------------------------------------------------------------------
SQL> select /*+ first_rows(100) */ * from t_emp
2 where sal >= 2000
3 order by empno, no;
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5999 | 240K| 93 (4) |
| 1 | SORT ORDER BY | | 5999 | 240K| 93 (4) |
| 2 | TABLE ACCESS FULL|T_EMP | 5999 | 240K| 25 (4) |
----------------------------------------------------------------
- 강좌 URL : http://www.gurubee.net/lecture/3216
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.