레이블이 oracle default keyword인 게시물을 표시합니다. 모든 게시물 표시
레이블이 oracle default keyword인 게시물을 표시합니다. 모든 게시물 표시

2013년 8월 8일 목요일

[오라클자바커뮤니티]Struts Validation Rule 강좌, oraclejavanew.kr

Struts Validation Rule 


오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷 실무전문 강의)  




Validator Framework에는 두개의 설정 파일(validation.xml, validator-rules.xml)이 있습니다. 

===================================================
1.        validation-rules.xml
===================================================

애플리케이션에서 사용하는 전체적인 검증 규칙을 포함, 애플리케이션에 독립적이므로 다른 스트럿츠 애플리케이션에서 사용 가능 합니다.

기본적인 규칙을 수정하거나 확장하는 경우에만 이 파일을 수정 합니다.

파일의 구성을 살펴보면 ROOT ELEMENT인 <form-validation>은 한 개 이상의 global 요소를 필요로 합니다.

<!ELEMENT form-validation (global+)>
<!ELEMENT global (validator+) >

각각의 validator는 유일한 한가지의 검증 규칙을 나타냅니다. 다음은 required 규칙에 대한 선언 부분 입니다. 또한 validator 요소는 자바스크립트를 지원합니다.

<validator name="required"
        classname="org.apache.struts.validator.FieldChecks"
        method="validateRequired"
        methodParams="java.lang.Object,
                      org.apache.commons.validator.ValidatorAction,
                      org.apache.commons.validator.Field,
                      org.apache.struts.action.ActionMessages,
                      javax.servlet.http.HttpServletRequest"
        msg="errors.required"/>

    name : 규칙의 논리적인 이름
    classname : 검증 로직을 포함하는 클래스
    method : 검증 로직을 포함하는 메소드
    methodParams : 메소드의 매개 변수
    msg : 리소스 번들의 키값
depends : 명시된 규칙 이전에 호출 해야 하는 검증 규칙
jsFunctionName : 자바스크립트 함수를 명시,
기본적으로 Validator의 실제 이름을 주로 사용 합니다.


<intRange>규칙을 보면 depends 부분이 기술 되어 있는데 참고 바랍니다. 즉 정수 값의 범위를 체크하기 이전에 정수형인지 체크가 이루어져야 한다는 것 입니다.

<validator name="intRange"
            classname="org.apache.struts.validator.FieldChecks"
            method="validateIntRange"
            methodParams="java.lang.Object,
                      org.apache.commons.validator.ValidatorAction,
                        org.apache.commons.validator.Field,
                      org.apache.struts.action.ActionMessages,
                      javax.servlet.http.HttpServletRequest"
            depends="integer"
            msg="errors.range"/>

다음은 Validator Framework의 기본적인 리소스 번들의 키 입니다.

# Struts Validator Error Messages
  errors.required={0} is required.
  errors.minlength={0} can not be less than {1} characters.
  errors.maxlength={0} can not be greater than {1} characters.
  errors.invalid={0} is invalid.

  errors.byte={0} must be a byte.
  errors.short={0} must be a short.
  errors.integer={0} must be an integer.
  errors.long={0} must be a long.
  errors.float={0} must be a float.
  errors.double={0} must be a double.

  errors.date={0} is not a date.
  errors.range={0} is not in the range {1} through {2}.
  errors.creditcard={0} is an invalid credit card number.
  errors.email={0} is an invalid e-mail address.

-----------------------------------------
다음은 GenericValidator의 기본 검증 규칙 입니다.
-----------------------------------------

Table 4.1: Common Validator Rules
Rule Name(s)                        Description                                 
required            The field is required. It must be present for the  form to be valid.     
minlength          The field must have at least the number of characters as the
specified minimum length.
maxlength          The field must have no more characters than the specified
maximum length.
intrange, floatrange,
doublerange        The field must equal a numeric value between the min and max variables.
byte, short, integer, 
long, float, double  The field must parse to one of these standard  Java types (rules names
equate to the expected Java type).
mask              The field must match the specified regular expression (Perl 5 style).
date                The field must parse to a date. The optional variable datePattern
specifies the date pattern
(see java.text.SimpleDateFormat in the Java-Docs to learn how to
create the date patterns).
You can also pass a strict variable equal to
creditCard          The field must be a valid credit card number. None


==============================================
2.        validation.xml
==============================================


이 파일은 애플리케이션에 종속적이며 특정한 ActionForm에서 사용하는 validation-rules.xml 파일의 검증 규칙을 나타냅니다. 그러므로 ActionForm 안에서는 검증을 위한 별다른 소스의 변경이 필요 없다는 이야기가 되며 validation.xml 파일에서 검증 로직은 ActionForm 한 개 이상과 연동 됩니다.

XML 형식은 다음과 같습니다.

<!ELEMENT form-validation (global*, formset+) >
<!ELEMENT global (constsnts*) >
<!ELEMENT formset (constant*, form+) >

Constants 요소는 자바 프로그래밍에서 상수를 선언하는 것과 매우 유사 합니다.

<global>
<constant>
<constant-name>phone</constant-name>
<constant-value>^\(?(\d{3})\)?[-| ]?(\d{3})[-
| ]?(\d{4})$</constant-value>
</constant>
<constant>
<constant-name>zip</constant-name>
<constant-value>^\d{5}(-\d{4})?$</constant-value>
</constant>
</global>

다음은 간단한 validation..xml 파일 입니다.

<form-validation>
<global>
<constant>
<constant-name>phone</constant-name>
<constant-value>^\(?(\d{3})\)?[-| ]?(\d{3})[-
| ]?(\d{4})$</constant-value>
</constant>
</global>
<formset>
<form name="checkoutForm">
<field
property="phone"
depends="required,mask">
<arg0 key="registrationForm.firstname.displayname"/>
<var>
<var-name>mask</var-name>
<var-value>${phone}</var-value>
</var>
</field>
</form>
</formset>
</form-validation>


<!ELEMENT formset (constant*, form+) >

한편 <formset> 이라는 요소는 <constant>와  <form> 이라는 두개의 요소를 포함 할 수 있는데 <constant> 요소는 <global> 섹션의 <constant>와 유사하여 생략도 가능 하지만 form 요소는 한번 이상 나타나야 합니다. (<formset> 요소는 국제화를 위한 language와 country를 지원 합니다.)

<form>요소는 하나 이상의 <field>를 포함할 수 있으며 XML 형식은 다음과 같습니다.

<!ELEMENT form (field+)>

<field> 요소는 검증을 해야 할 자바빈의 특정 프로퍼티와 일치해야 하며 스트럿츠 애플리케이션에서의 자바 빈은 결국 ActionForm을 의미 합니다.

다음은 field의 속성 입니다.

property :    자바빈(혹은 ActionForm)에서 검증해야 할 프로퍼티 이름
depends :    field 요소에 적용 할 검증 규칙의 목록, 각 검증 규칙은 여러 개가 올 수 있으며 이럴경우 콤마로 구분, 검증이 이루어 지기 위해서는 모든 규칙을 만족 시켜야 합니다.
page :      자바빈이 대응 할 페이지, 자바빈은 page 속성이 속해 있는 form과 대응
indexedListProperty : 리스트를 읽어 가면서 필드를 검증 하는 경우에 이용


field 요소는 다음과 같이 여러 가지 요소를 포함 합니다.

<!ELEMENT field (msg?, arg0?, arg1?, arg2?, arg3?, var*)>

<msg> 요소는 리소스 번들의 키 값이며 이 값을 기본 메시지 대신 이용 할 수 있습니다. 즉 검증이 잘못된 경우 이 메시지의 값이 출력 됩니다. msg를 사용하지 않으면 기본적으로 등록된 메시지가 출력 됩니다.  아래와 같은 메시지…

errors.required={0} is required.
  errors.minlength={0} can not be less than {1} characters.
  errors.maxlength={0} can not be greater than {1} characters.
  errors.invalid={0} is invalid.

또한 msg는 3개의 속성이 있는데…다음과 같습니다.

name : msg요소가 사용할 검증 규칙, 반드시 validation-rules.xml 파일이나 global 섹션에 명시한 규칙 이어야 합니다.

key : 검증 실패 시 ActionError에 추가 할 수 있는 리소스 번들의 키값, resource 속성을 false로 할 경우 리소스 번들을 사용하지 않고 key 속성에 문자열을 직접 넣을 수 있습니다.

아래의 예를 참고 하세요~

<field property="phone" depends="required,mask">
<msg name="mask" key="phone.invalidformat"/>
<arg0 key="registrationForm.firstname.displayname"/>
<var>
<var-name>mask</var-name>
<var-value>${phone}</var-value>
</var>
</field>


------------------------
Validator 플러그 인
------------------------

Validator Framework을 사용하기 위해서는 struts-config.xml에서 PlugIn 설정을 해야 합니다.

<plug-in>
className="org.apache.struts.validator.ValidatorPlugIn">
<set-property property="pathnames" value="/WEB-INF/validator-rules.xml,/WEBINF/
validator.xml"/>
</plug-in>

스트럿츠 애플리케이션이 구동 할 때 ValidatorPlugIn 클래스의 init() 메소드가 호출되며 이 메소드가 실행 하면서 XML 파일의 Validator 리소스 들을 메모리에 적재 합니다. 

2013년 8월 6일 화요일

[ORACLE SGA TUNING, 오라클교육,자바교육, 오라클자바]Literal SQL & Bind Variable

Literal SQL Statement와  Bind Variable 


오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷 실무전문 강의)  




 Literal SQL문을 많이 사용하면 Hard Parsing의 빈도를 높이게 되어 Library Cache내에서 Cache되는 SQL문들이 자주 age out 하게 되므로 주기를 빠르게 하고 Dictionary Cache의 사용율을 높이게 됩니다. 이러한 이유로 OLTP DB환경에서는 Shared SQL문중에서 Literal SQL 문들을 찾아내어 Bind Variable을 이용한 방법을 사용하도록 해야 합니다. 즉 OLTP 환경에서 가급적 Literal SQL Statement의 사용은 줄이라는 이야깁니다.

아래의 내용을 참고 하시구요…

Eg 1: SELECT * FROM emp WHERE ename='CLARK';

 is used by the application instead of SELECT * FROM emp WHERE ename=:bind1;

Eg 2: SELECT sysdate FROM dual;

does not use bind variables but would not be considered as a literal SQL statement for this article as it can be shared.

Eg 3: SELECT version FROM app_version WHERE version>2.0;

If this same statement was used for checking the 'version' throughout the application then the literal value '2.0' is always the same so this statement can be considered sharable.

자 그럼 이젠 Hard Parsing과 Soft Parsing에 대해 정리 해보도록 하겠습니다.

Hard Parse

하드파싱이란 새 SQL문장이 실행 되는 경우엔 Shared Pool에는 없으므로 완전히 전부 새로 파싱을 한다는 의미 입니다. 오라클은 Shared Pool에 새로운 SQL문장을 할당 하며 SQL 문장이 문법은 맞는지등을 검사 하게 됩니다. 이 경우 CPU 사용이 매우 많아 지게 되는거죠, 물론 Latch의 사용도 증가 하게 됩니다.

Soft Parse

소프트 파싱이란 스행하고자 하는 SQL 문장이 이미 Shared Pool에 있어 이미 존재하는 SQL에 관련된 정보를 그대로 이용하는 겁니다.

그럼 Soft Parse가 되기 위해서는 가능 하면 동일한 SQL 문장을 구사해야 하겠죠? (당근이죠^^)

동일한 SQL 문장이란 무엇인지 알아 보도록 하겠습니다. 우선 하드 파싱의 대상에는 어떤 것이 있는지 알아 보기로 하겠습니다.

우선 같은 테이블을 질의 하더라도 사용자 계정이 다른 경우 동일한 SQL 문장으로 간주 되지 않으므로, 또 SQL문장의 공백이 다른 경우, (select * from emp와 select    *    from    emp는 다릅니다.) 음 그리고 Bind Variable을 사용하는 경우 변수명 이나 타입이 다른 경우에도 그렇구요, 동일한 질의라도 SQL문장의 대소문자 역시 다르면 이것 역시 하드 파싱의 대상이므로 삼가 해야 합니다. 마지막으로 SQL문장의 라인이 달라두 역시..
같은 SQL 문장을 한라인에 쓰는 경우와 여러 라인에 나누어 쓰는 것은 다르게 인식 되는 것 입니다.

select substr(sql_text,1,40) "SQL", count(*),
      sum(executions) "총 실행 횟수"
from v$sqlarea
group by substr(sql_text,1,40)
having count(*) > 5
order by 2;

다음의 예문을 잘 이해하도록 하자구요~

SQL> conn / as sysdba
연결되었습니다.

공유 영역을 클리어 합니다.
SQL> alter system flush shared_pool;

시스템이 변경되었습니다.

SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

Emp 테이블의 개수를 얻기 위한걸 한번 수행 했습니다.

그런 다은 v$SQLAREA 뷰에서 확인 해 봅니다.
SQL> conn / as sysdba

SQL> select substr(sql_text,1,40) "SQL", count(*),
  2        sum(executions) "총 실행 횟수"
  3  from v$sqlarea
  4  where sql_text like '%emp%'
  5  group by substr(sql_text,1,40)
  6  having count(*) > 0
  7  order by 2;


 SQL                    COUNT(*)      총 실행 횟수
---------- ----------------------------------
select count(*) from emp        1            1
이하 생략

SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

그런 다음 다시 v$SQLAREA에서 확인 하죠^^

SQL> select substr(sql_text,1,40) "SQL", count(*),
  2        sum(executions) "총 실행 횟수"
  3  from v$sqlarea
  4  where sql_text like '%emp%'
  5  group by substr(sql_text,1,40)
  6  having count(*) > 0
  7  order by 2;

SQL
---------------------------------------------------------------------

  SQL                    COUNT(*)  총 실행 횟수
---------- -----------------------------
select count(*) from emp        1            3

자 그럼 이번에는 동일한 SQL 문장인데 공백을 더 넣어서 Hard Parsing이 일어나게 해 볼까요…

SQL> conn scott/tiger
연결되었습니다.
SQL> select count(*) from    emp;

  COUNT(*)
----------
        14

V$sqlarea를 조회해 보죠…

SQL> select substr(sql_text,1,40) "SQL", count(*),
  2        sum(executions) "총 실행 횟수"
  3  from v$sqlarea
  4  where sql_text like '%emp%'
  5  group by substr(sql_text,1,40)
  6  having count(*) > 0
  7  order by 2;

SQL                            COUNT(*)    총 실행 횟수
---------- ---------------------------------------
select count(*) from    emp        1            1
select count(*) from emp              1            3

이해 되시죠… 공백 뿐 아니라 대소문자등도 주의 해야 합니다.

2013년 8월 5일 월요일

[ORACLE SGA Tuning]DBMS_SHARED_POOL, Object KEEP

[ORACLE SGA Tuning]DBMS_SHARED_POOL, Object KEEP

오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷 실무전문 강의) 
 www.onjprogramming.co.kr 




DBMS_SHARED_POOL을 이용한 KEEP

 Shared Poll에 크기가 큰 프로그램을 KEEP하기 위해서는 아래에 있는 것처럼 DBMS_SHARED_POOL Package를 이용 할 수 있습니다.

SQL> @C:\oracle\ora92\rdbms\admin\dbmspool.sql

패키지가 생성되었습니다.


권한이 부여되었습니다.


뷰가 생성되었습니다.


패키지 본문이 생성되었습니다.

SQL> @C:\oracle\ora92\rdbms\admin\prvtpool.plb

뷰가 생성되었습니다.


패키지 본문이 생성되었습니다.

SQL> grant execute on dbms_shared_pool to scott;

권한이 부여되었습니다.

Object를 KEEP하는 방법은 다음과 같습니다.

Procedure,Function,Package : exec dbms_shared_pool.keep(‘pname’,’p’)
Trigger : exec dbms_shared_pool.keep(‘tr_emp’,’r’)
Sequence : exec dbms_shared_pool.keep(‘seq_empno,’q’)
SQL문은 아래와 같은 방법으로 KEEP 합니다.

예를들어 select empno, ename, sal from emp where deptno = ‘20’ 라는 SQL문장을 Library Cache안의 Shared Cursor 부분에 KEEP하기 위해서는 아래처럼 하면 됩니다…

SQL> conn scott/tiger
연결되었습니다.

SQL> select empno, ename, sal from emp where deptno = 20;

    EMPNO ENAME            SAL
---------- ---------- ----------
      7369 SMITH            800
      7566 JONES            2975
      7788 SCOTT            3000
      7876 ADAMS            1100
      7902 FORD            3000

SQL> conn / as sysdba
연결되었습니다.

SQL> select address, hash_value from v$sqlarea
  2  where sql_text = 'select empno, ename, sal from emp where deptno = 20';

ADDRESS  HASH_VALUE
-------- ----------
7856AC4C 1137127237  <- 원하는 SQL문장에 대한 주소와 해시 값

아래 명령으로  KEEP 합니다.

SQL> exec dbms_shared_pool.keep('7856AC4C, 1137127237','c');

PL/SQL 처리가 정상적으로 완료되었습니다.

Object의 KEEP 상태는 다음으로 체크 가능 합니다.

SQL> select distinct name, sharable_mem, loads
  2  from v$db_object_cache
  3  where name like '%emp%'
  4  and kept = 'YES';

NAME                                      SHARABLE_MEM      LOADS
------------ ----------------------------------------------
select empno, ename, sal from emp where deptno = 20  1469          1

또는 exec dbms_shared_pool.sizes(0)로 확인 가능 합니다. 이 sizes라는 procedure는 제한된 사이크 이상의 keep된 Object를 나타내 줍니다.

SQL> set serveroutput on size 2000
SQL> exec dbms_shared_pool.sizes(0)  -> buffer overflow가 나더라도 pin시킬(KEEP할) SQL문장을 찾을 수는 있습니다.

각 Object를 Shared Pool에 유지하던 것을 해제 할 때는 아래의 unkeep 프로시저를 이용 합니다.

SQL> exec dbms_shared_pool.unkeep('7856AC4C, 1137127237','c');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select distinct name, sharable_mem, loads
  2  from v$db_object_cache
  3  where name like '%emp%'
4  and kept = 'YES';

결과가 없겠죠…


아래는 DBMS_SHARED_POOL.keep procedure 명셉니다… 메타링크 자료니 참고 하세요…

PROCEDURE:DBMS_SHARED_POOL.KEEP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Version/s:  7.1+, 8.0, 8.1
See Also:  [NOTE:67537.1] The Purpose of DB Package Reference Articles
            [NOTE:67569.1] DBMS_SHARED_POOL Package Description
 
Specification:
  procedure keep(name varchar2, flag char DEFAULT 'P');
 
Description:
  Keep an object in the shared pool.  Once an object has been keeped in
    the shared pool, it is not subject to aging out of the pool.  This
    may be useful for certain semi-frequently used large objects since
    when large objects are brought into the shared pool, a larger
    number of other objects (much more than the size of the object 
    being brought in, may need to be aged out in order to create a
    contiguous area large enough.
    WARNING:  This procedure may not be supported in the future when
    and if automatic mechanisms are implemented to make this 
    unnecessary.
 
  Input arguments:
    name
      The name of the object to keep.  There are two kinds of objects:
      PL/SQL objects, triggers, sequences which are specified by name,
          and SQL cursor objects which are specified by a two-part number
      (indicating a location in the shared pool).  For example:
        dbms_shared_pool.keep('scott.hispackage')
      will keep package HISPACKAGE, owned by SCOTT.  The names for
      PL/SQL objects follows SQL rules for naming objects (i.e., 
      delimited identifiers, multi-byte names, etc. are allowed).
      A cursor can be keeped by
        dbms_shared_pool.keep('0034CDFF, 20348871')
      The complete hexadecimal address must be in the first 8 characters.
      The value for this identifier is the concatonation of the
      'address' and 'hash_value' columns from the v$sqlarea view.  This
      is displayed by the 'sizes' call above.
      Currently 'TABLE' and 'VIEW' objects may not be keeped.
    flag
      This is an optional parameter.  If the parameter is not specified,
        the package assumes that the first parameter is the name of a
        package/procedure/function and will resolve the name.  It can also
        be set to 'P' or 'p' to fully specify that the input is the name
        of a package/procedure/function.
       
        Other FLAG options are:
          'T' or 't'        to specify that the input is the name of a type.
          'R' or 'r'        to specify that the input is the name of a trigger 
          'Q' or 'q'        to specify that the input is the name of a sequence.
          other        In case the first argument is a cursor address and 
                        hash-value, the parameter should be set to any 
                        character except 'P','p','Q','q','R','r','T' or 't'.
 
  Exceptions:
    An exception will raised if the named object cannot be found.

2013년 8월 3일 토요일

Oracle SQL Execution Explain Plan실행계획 SQL 연산(FILTER)

SQL 연산(FILTER)

구로디지털 오엔제이프로그래밍실무교육센터


FILTER 연산은 데이터 추출 시 필터링이 일어나고 있음을 알려주는 SQL ROW 연산인데 WHERE 조건 절에서 인덱스를 사용하지 못할 때 발생하는 것입니다. NESTED LOOP 방식으로 해석할 수 있습니다.

아래의 예는 EMP TABLE에서 부서의 최소 급여를 받는 사람들을 추출하는 것입니다.

SQL> SELECT ENAME, SAL, JOB
      FROM   EMPTEST A
      WHERE  SAL = (SELECT MIN(SAL)
                      FROM   EMPTEST B
                      WHERE B.DEPTNO = A.DEPTNO);

Execution Plan
---------------------------------------------------
0       SELECT STATEMENT Optimizer=CHOOSE
1       0  FILTER
2       1     TABLE ACCESS (FULL) OF EMP
3       2     SORT (AGGREGATE)
4       3       TABLE ACCESS (BY INDEX ROWID) OF EMP
5       4         INDEX (RANGE SCAN) OF idx_emp_deptno (NON-UNIQUE) 

ACCESS 경로를 변경하는 힌트(REWRITE), oracle hint 강좌 , oraclejava 강좌

ACCESS 경로를 변경하는 힌트(REWRITE)

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr 
 
이 힌트는 CBO에서 Matreriakized views(구체화뷰)에 대해 Query Rewrite가 일어나도록 하는 힌트인데 8i 이상부터 가능 합니다. REWRITE 힌트 구문에 VIEW가 인자로 와도 되고 안 와도 됩니다. 인자로 뷰 리스트를 주지 않는 경우 적절한 materialized view를 찾고 항상 비용(COST)과 관계없이 사용 합니다.

Materialized views라는 것이 DW(Data WareHouse)에서 집계 데이터 등을 추출할 때 쿼리 수행속도를 빠르게 해주는 것인데 Oracle에서 Query Rewrite가 일어나기 위해서는 다음과 같은 조건이 만족 되어야 합니다.

OPTIMIZER_MODE = ALL_ROWS or FIRST_ROWS or CHOOSE
QUERY_REWRITE_ENABLED = TRUE
COMPITABLE = 8.1.0 이상

사용 형식은 다음과 같습니다.

[형식]




create materialized view  sum_sales
build immediate  
refresh complete  -- 만들어지자 마자 실행 가능한 상태로 원본테이블이 변경되면 모두 갱신함, FAST 갱신된 데이터만 반영, NEVER 반영안함
enable query rewrite  -- 오라클이 데이터 검색시 구체화뷰를 통해 검색하도록
as
select deptno,
       sum(sal) sum_sales
from   emp
group by deptno

만약 위 Query 실행 시 권한이 없다면서 에러가 난다면 관리자로 로그인 하여 다음과 같이 실습 계정에 권한을 주시기 바랍니다.

grant create any materialized view to scott;
grant global query rewrite to scott;
grant query rewrite to scott;
grant alter any materialized view to scott;

MVIEW를 사용하지 않는 모양의 질의를 한번 볼까여

위와 같이 한 후 다음과 같은 SQL문을 실행 합니다.

select deptno, sum(sal)
from   emp
group by deptno


--------------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
-----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               3                        4
  HASH GROUP BY                        3           15         4                                   
    TABLE ACCESS FULL             SCOTT.EMP        15         75         3                                                     

우선 세션 레벨에서 query_rewrite_enabled true 바꿉니다.

alter session set query_rewrite_enabled=true;


select
       deptno, sum(sal)
from   emp
group by deptno


----------------------------------------------------------------
Operation            Object Name      Rows     Bytes    Cost     
----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS               4                        3
MAT_VIEW REWRITE ACCESS FULL         SCOTT.SUM_SALES         4           104       3 

위 실행 계획을 보면 EMP 테이블을 이용하지 않고 SUM_SALES라는 MVIEW를 이용하여 실행 계획이 만들어 짐을 알 수 있습니다.



[실습]

-      실습을 위한 예제 테이블 및 데이터는 아래 링크에서 확인 바랍니다.

myemp1 : 1000만건
myemp1_old : 100만건
mydept : 5

테스트환경 : oracle 11g


우선 구체화뷰를 만들지 않고 CBO, RBO 경우 아래의 쿼리를 날려보자.

select e.deptno,
       avg(sal) sal_sum
from   myemp1 e, mydept1 d
where  e.deptno = d.deptno
group by e.deptno;


SQL>alter session set optimizer_mode = choose  --myemp1통계정보 있으니 CBO로 동작

SQL> select e.deptno,
  2         avg(sal) sal_sum
  3  from   myemp1 e, mydept1 d
  4  where  e.deptno = d.deptno
  5  group by e.deptno;

    DEPTNO    SAL_SUM
---------- ----------
         0   999997.5
         1   999998.5
         2   999999.5
         3  1000000.5
         4  1000001.5

   : 00:00:07.93

---------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    41 | 17484   (4)| 00:03:30 |
|   1 |  SORT GROUP BY NOSORT  |          |     1 |    41 | 17484   (4)| 00:03:30 |
|   2 |   MERGE JOIN           |          |    10 |   410 | 17484   (4)| 00:03:30 |
|   3 |    SORT JOIN           |          |     5 |   140 | 17480   (4)| 00:03:30 |
|   4 |     VIEW               | VW_GBC_5 |     5 |   140 | 17480   (4)| 00:03:30 |
|   5 |      HASH GROUP BY     |          |     5 |    35 | 17480   (4)| 00:03:30 |
|   6 |       TABLE ACCESS FULL| MYEMP1   |    10M|    66M| 16961   (1)| 00:03:24 |
|*  7 |    SORT JOIN           |          |    10 |   130 |     4  (25)| 00:00:01 |
|   8 |     TABLE ACCESS FULL  | MYDEPT1  |    10 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

8초 정도 걸렸다.

이번에는 RBO로 붜리를 실행해 보자. (2분 넘는다)

SQL> alter session set optimizer_mode = rule

SQL> select e.deptno,
  2         avg(sal) sal_sum
  3  from   myemp1 e, mydept1 d
  4  where  e.deptno = d.deptno
  5  group by e.deptno;

    DEPTNO    SAL_SUM
---------- ----------
         0   999997.5
         1   999998.5
         2   999999.5
         3  1000000.5
         4  1000001.5

   : 00:02:17.53

-----------------------------------------------------------
| Id  | Operation                     | Name              |
-----------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |
|   1 |  SORT GROUP BY                |                   |
|   2 |   NESTED LOOPS                |                   |
|   3 |    NESTED LOOPS               |                   |
|   4 |     TABLE ACCESS FULL         | MYDEPT1           |
|*  5 |     INDEX RANGE SCAN          | IDX_MYEMP1_DEPTNO |
|   6 |    TABLE ACCESS BY INDEX ROWID| MYEMP1            |
-----------------------------------------------------------

지금 부터는 물리뷰를 만들어서 실습하자.
Materialized views를 하나 만듭니다.


SQL> create materialized view  avg_myemp_sales
  2  build immediate
  3  refresh complete
  4  enable query rewrite
  5  as
  6  select e.deptno,
  7         avg(sal) sal_sum
  8  from   myemp1 e, mydept1 d
  9  where  e.deptno = d.deptno
 10  group by e.deptno;
구체화된 뷰가 생성되었습니다.


n  이번에는 구체화 뷰를 사용 안하고 질의해 보자. 엄청 시간 걸린다.(거의 3)
n  뷰를 만들지 말고 먼저 질의 해 보라.



SQL>
SQL>-- 뷰를 사용하도록 힌트 구성(힌트를 안써도 구체화뷰를 통해 데이터를 가져 올 것이다.)
SQL> select /*+ rewrite(avg_myemp_sales)  */e.deptno,
  2         avg(sal) sal_sum
  3  from   myemp1 e, mydept1 d
  4  where  e.deptno = d.deptno
  5  group by e.deptno;

    DEPTNO    SAL_SUM
---------- ----------
         0   999997.5
         1   999998.5
         2   999999.5
         3  1000000.5
         4  1000001.5

   : 00:00:00.23  -- 바로 나온다


------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     5 |   130 |     3   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| AVG_MYEMP_SALES |     5 |   130 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------