규칙 기반 옵티마이저(Rule-Based Optimizer) [오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육] 오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 스타쿼리, 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다. 물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면 default로 ALL_ROWS로 동적 합니다. ----------------------------------------------------------- 규칙 기반 옵티마이저의 우선 순위 ----------------------------------------------------------- rowid에 의한 단일 행 검색이 우선 순위가 가장 높습니다. 1. rowid에 의한 single row access SQL>select empno, ename from emp where rowed = ‘AAA78BBBBBBBBB’; 2. cluster join에 의한 single row access 같은 클러스터 내에 있는 테이블을 조인할 때 이용, 아래 예는 EMP 테이블과 DEPT 테이블이 dept_no에 의해 cluster 되어 있으며 empno 컬럼이 EMP 테이블의 pk 인 경우의 예입니다. SQL> select ename, dname, sal from emp e, dept d where e.deptno = d.deptno and e.empno = 8888; Execution Plan ------------------------------------------ SELECT STATEMENT NESTED LOOP TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’ INDEX (UNIQUE SCAN) OF ‘pk_emp’ (UNIQUE) TABLE ACCESS (BY CLUSTER) OF ‘DEPT’ 3. unique key를 가지는 hash cluster에 의한 single row access where절이 = 비교이며 hash cluster key를 가지며 단일 로우를 추출하는 경우에 사용되는 됩니다. 4. unique index에 의한 single row access 아래에서 EMP 테이블의 PK는 EMP_NO SQL> select ename, sal from emp where empno = 8000; 5. cluster join 6. hash cluster key 7. indexed cluster key 8. composite index EMP 테이블에서 JOB과 SAL에 복합 인덱스가 있는 경우 SQL>select ename from emp where job = ‘SALES’ and sal = 8000; 9. non unique index (single column) EMP 테이블에서 JOB에 인덱스가 있는 경우 SQL>select ename from emp where job = ‘SALES’ 10. bounded range search on indexed column(인덱스 컬럼을 이용한 제한된 범위 검색) EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 SQL>select ename, sal from emp where sal > 1000 and sal < 5000; 11. unbounded range search on indexed column(인덱스 컬럼에 의한 제한되지 않은 범위 검색) EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 SQL>select ename, sal from emp where sal > 1000; 12. sort merge join 13. index column의 MIN or MAX EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 SQL>select MAX(SAL) from emp; 14. index column에 대한 order by 15. full table scan 이상 규칙 기반 옵티마이저의 우선 순위에 대해 살펴 보았는데 규칙 기반 옵티마이저인 경우엔 실행 계획 수립시 번호가 낮은 것을 먼저 검토하여 적용가능한지 파악하며 한 단계식 높은 번호를 수행 합니다. 즉 낮은 번호 일수록 높은 우선 순위를 가지는데 예를 들어 다음과 같은 Query가 있다고 가정할 때…. SQL>select ename, job from emp where ename = ‘홍길동’; (ename에 인덱스가 걸려 있다고 할 때) 데이터가 몇건 되지 않는다고 할 때 규칙 기반 옵티마이저인 경우 인덱스를 이용하는 것이 FULL SCAN하는 것보다 우선 순위가 높으므로 무조건 인덱스를 경유하여 EMP TABLE을 rowid에 의해 access 하는 실행계획을 세울 겁니다, 그러나 사실 이 테이블의 경우엔 데이터가 몇건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 말입니다. 이 규칙 기반 옵티마이저의 경우 무조건 규칙에 위해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 그래서 최근엔 CBO를 많이 사용하고 있는 추세 입니다. * 이종철님에 의해서 게시물 이동되었습니다 (2006-06-04 12:28) [출처] 오라클자바커뮤니티 - http://www.oraclejavacommunity.co.kr/bbs/board.php?bo_table=LecHINT&wr_id=23 오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급 www.oraclejavacommunity.com 평일주간(9:30~18:20) 개강 (5/28)[교육전취업확정]Spring,MyBatis,XPlatform실무프로젝트과정 (5/30)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 (5/30)[기업100%환급]SQL기초에서 Schema Object까지 (6/09)[기업100%환급]PL/SQL,ORACLE HINT,TUNING (6/09)[기업100%환급]안드로이드개발자과정 (6/09)[기업100%환급]Spring ,MyBatis,Hibernate실무과정 (6/16)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍 평일야간(19:00~21:50) 개강 (5/28)Spring3.X, MyBatis, Hibernate실무과정 (5/28)SQL초보에서실전전문가까지 (5/29)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 (6/03)안드로이드개발자과정 (6/03)웹퍼블리싱 마스터 (6/10)C#4.0, ADO.NET, Network 프로그래밍 (6/19)C#,ASP.NET마스터 주말(10:00~17:50) 개강 (5/31)Spring3.X, MyBatis, Hibernate실무과정 (5/31)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지 (5/31)SQL초보에서실전전문가까지 (5/31)C#,ASP.NET마스터 (5/31)실무예제로 배워보는 jQuery(개발자/디자이너를위한) (5/31)안드로이드개발자과정 주말저녁(18:30~22:20) 개강 (6/21)JAVA,Network&WEB&Framework (6/21)SQL기초에서실무까지

규칙 기반 옵티마이저(Rule-Based Optimizer) [오라클개발자교육/오라클/ORACLE강좌/오라클교육잘하는곳/오라클교육추천/ORACLE실무교육/ORACLE/ORACLE교육/ORACLE학원/오라클실무교육]

오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 스타쿼리, 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다. 

물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면 default로 ALL_ROWS로 동적 합니다. 

----------------------------------------------------------- 
규칙 기반 옵티마이저의 우선 순위 
----------------------------------------------------------- 

rowid에 의한 단일 행 검색이 우선 순위가 가장 높습니다. 

1.        rowid에 의한 single row access 

SQL>select empno, ename from emp where rowed = ‘AAA78BBBBBBBBB’; 

2.        cluster join에 의한 single row access 

같은 클러스터 내에 있는 테이블을 조인할 때 이용, 아래 예는 EMP 테이블과 DEPT 테이블이 dept_no에 의해 cluster 되어 있으며 empno 컬럼이 EMP 테이블의 pk 인 경우의 예입니다. 

SQL> select  ename, dname, sal 
      from  emp e, dept d 
      where e.deptno = d.deptno 
      and  e.empno = 8888; 

Execution Plan 
------------------------------------------ 
    SELECT STATEMENT 
        NESTED LOOP 
 TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’ 
 INDEX (UNIQUE SCAN) OF ‘pk_emp’ (UNIQUE)  
          TABLE ACCESS (BY CLUSTER) OF ‘DEPT’ 

3.        unique key를 가지는 hash cluster에 의한 single row access 

    where절이 = 비교이며 hash cluster key를 가지며 단일 로우를 추출하는 경우에 
사용되는 됩니다. 

4.        unique index에 의한 single row access 

아래에서 EMP 테이블의 PK는 EMP_NO 

    SQL> select  ename, sal 
          from  emp 
          where  empno = 8000; 
  
5.        cluster join 
6.        hash cluster key 
7.        indexed cluster key 
8.        composite index 

EMP 테이블에서 JOB과 SAL에 복합 인덱스가 있는 경우 

SQL>select ename 
    from  emp 
    where job = ‘SALES’ 
    and  sal = 8000; 

9.        non unique index (single column) 

 EMP 테이블에서 JOB에 인덱스가 있는 경우 

SQL>select ename 
    from  emp 
    where job = ‘SALES’ 
  

10.        bounded range search on indexed column(인덱스 컬럼을 이용한 제한된 범위 검색) 

EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 

SQL>select ename, sal 
    from emp 
    where sal > 1000 
    and  sal < 5000; 

11.        unbounded range search on indexed column(인덱스 컬럼에 의한 제한되지 않은 범위 검색) 

EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 

SQL>select ename, sal 
    from emp 
    where sal > 1000; 

12.        sort merge join 
13.        index column의 MIN or MAX 

EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우 

SQL>select MAX(SAL) 
    from emp; 

14.        index column에 대한 order by 
15.        full table scan 





이상 규칙 기반 옵티마이저의 우선 순위에 대해 살펴 보았는데 규칙 기반 옵티마이저인 경우엔 실행 계획 수립시 번호가 낮은 것을 먼저 검토하여 적용가능한지 파악하며 한 단계식 높은 번호를 수행 합니다. 즉 낮은 번호 일수록 높은 우선 순위를 가지는데 예를 들어 다음과 같은 Query가 있다고 가정할 때…. 

SQL>select ename, job from emp where ename = ‘홍길동’; 
(ename에 인덱스가 걸려 있다고 할 때) 

데이터가 몇건 되지 않는다고 할 때 규칙 기반 옵티마이저인 경우 인덱스를 이용하는 것이 FULL SCAN하는 것보다 우선 순위가 높으므로 무조건 인덱스를 경유하여 EMP TABLE을 rowid에 의해 access 하는 실행계획을 세울 겁니다, 그러나 사실 이 테이블의 경우엔 데이터가 몇건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 말입니다. 

이 규칙 기반 옵티마이저의 경우 무조건 규칙에 위해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 그래서 최근엔 CBO를 많이 사용하고 있는 추세 입니다.
* 이종철님에 의해서 게시물 이동되었습니다 (2006-06-04 12:28) 

[출처] 오라클자바커뮤니티 - http://www.oraclejavacommunity.co.kr/bbs/board.php?bo_table=LecHINT&wr_id=23

오라클자바커뮤니티교육센터, 개발자전문교육, 개인80%환급 
www.oraclejavacommunity.com

평일주간(9:30~18:20) 개강
(5/28)[교육전취업확정]Spring,MyBatis,XPlatform실무프로젝트과정
(5/30)[기업100%환급]자바기초에서 JDBC, Servlet/JSP까지 
(5/30)[기업100%환급]SQL기초에서 Schema Object까지
(6/09)[기업100%환급]PL/SQL,ORACLE HINT,TUNING
(6/09)[기업100%환급]안드로이드개발자과정
(6/09)[기업100%환급]Spring ,MyBatis,Hibernate실무과정
(6/16)[기업100%환급]C#4.0,WinForm,ADO.NET프로그래밍

평일야간(19:00~21:50) 개강
(5/28)Spring3.X, MyBatis, Hibernate실무과정
(5/28)SQL초보에서실전전문가까지
(5/29)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(6/03)안드로이드개발자과정
(6/03)웹퍼블리싱 마스터
(6/10)C#4.0, ADO.NET, Network 프로그래밍
(6/19)C#,ASP.NET마스터

주말(10:00~17:50) 개강
(5/31)Spring3.X, MyBatis, Hibernate실무과정
(5/31)자바기초에서JSP,Ajax,jQuery,Spring3.2,MyBatis까지
(5/31)SQL초보에서실전전문가까지
(5/31)C#,ASP.NET마스터
(5/31)실무예제로 배워보는 jQuery(개발자/디자이너를위한)
(5/31)안드로이드개발자과정

주말저녁(18:30~22:20) 개강
(6/21)JAVA,Network&WEB&Framework
(6/21)SQL기초에서실무까지