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

2013년 8월 6일 화요일

다중 애플리케이션(Struts Multi-Application)

다중 애플리케이션(Multi-Application)


오라클자바커뮤니티에서 설립한 오엔제이프로그래밍 실무교육센터
(오라클SQL, 튜닝, 힌트,자바프레임워크, 안드로이드, 아이폰, 닷넷 실무전문 강의)   
 스트럿츠 1.1이상에서는 다중 애플리케이션 사용이 가능 합니다.

애플리케이션의 모듈이 동일한 웹애플리케이션의 일부 이지만 서로 독립적입니다.

다중 애플리케이션을 사용하는 이유는 업무를 좀 더 조직화, 세분화 할 수 있다는 장점이 있습니다.

쇼핑몰을 개발하는데 상품의 디스플레이 부분과 쇼핑카트를 구현 하는 부분을 별도의 다중 애플리케이션으로 구성 하는 것이 가능 합니다. 이렇게 하는 이유는 상호 독립적이고 수평적인 개발이 가능하도록 하기 위해서 입니다.


기본 애플리케이션이 아닌 모듈은 config/ 로 시작 합니다.

config/ 다음에 오는 부분은 애플리케이션 모듈의 접두어가 되며 프레임웍 전반을 걸쳐클라이언트의 요청을 처리하는데 사용 됩니다.

아래에 간략히 방법을 설명 하고 예제를 만들어 보도록 합니다.
(이클립스에서 톰캣 애플리테이션으로 Login이라는 프로젝트를 작성 후… 별도의 test라는 독립된 애플리케이션을 만들려고 합니다.)

1.        웹 애플리테이션의 web.xml 파일에 다중 애플리케이션을 위한 설정을 합니다.

<servlet>
          <servlet-name>action</servlet-name>
          <servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
          <init-param>
                        <param-name>config</param-name>
                        <param-value>/WEB-INF/struts-config.xml</param-value>
          </init-param>
          <init-param>
                  <param-name>config/test</param-name>
                  <param-value>/WEB-INF/struts-test-config.xml</param-value>
          </init-param>
          <load-on-startup>1</load-on-startup>               
        </servlet>


2.        이번에 어떻게 모듈(애플리케이션)을 Switch하는지 알아 봅니다.

두 가지의 큰 방법이 있는데 아래와 같습니다.

-        forward를 이용하는 방법

struts-config.xml에서 test 애플리케이션으로 스위칭을 하기 위해서 forward를 이용합니다.

유심히 볼 부분은 contextRelative가 true가 되어 있다는 것이다. 이 값은 하나의 웹 애플리테이션을 사용 하는 경우에는 false(path 설정 시 기준을 애플리케이션을 기준으로)  이지만 다중 웹 애플리케이션을 사용 하기 위해서는 true(path 설정 시 기준이 context가 기준)로 설정 합니다.

즉 아래에서 test 라는 또 하나의 애플리케이션을 가리킬 때 /test/mysubmit과 같이 컨텍스트를 기준으로 경로를 사용 했습니다. (현재 톰캣 프로젝트를 하나 만들었죠^^ Login 이라는 것을…)

<struts-config>
...
<global-forwards>
<forward name="toModuleB"
contextRelative="true"
path="/test/mysubmit"
redirect="true"/>
...
</global-forwards>

</struts-config>



- org.apache.struts.actions.SwitchAction를 이용하는 방법

<action-mappings>
<action path="/toModule"
type="org.apache.struts.actions.SwitchAction"/>
...
</action-mappings>



이제 예제를 만들어 보도록 하겠습니다. 예제는 개념을 익히기 위해 최대한 간단히 작성 했습니다.


[처리흐름]

Login이라는 톰캣 프로젝트(웹애플리케이션)를 하나 만들고 별도의 독립적인 test라는 애플리케이션을 만들어 struts-config.xml과는 별도로 struts-test-config.xml을 만들어 보겠습니다. (물론 둘은 업무적으로 어떤 연관성을 가지지는 않고 있습니다. 예문에서는 단지 다중 애플리케이션을 설정하고 로딩 하는 것만 살펴 볼테니까요…)

사용자가 /Login/login.jsp를 실행 합니다.

실행 화면은 다음과 같습니다. (간단히 ID와 PASSWORD만 입력 받고 submit 버튼을 누릅니다.)

 

Submit 버튼을 누르면 이 액션을 /toTest라는 path로 넘어갑니다. 이 요청을 우선 Login 애플리케이션에서 받습니다.

<action
                path="/toTest"
                type="login2.toTest"
                validate="false" 
                name="loginForm"             
        />

위와 같이 설정이 되어 있습니다. 그래서 login2.toTest 라는 클래스가 실행을 하겠죠…

toTest.java 에서는 간단히 위에서 설명한 2번 방법대로 forward를 이용하여 test라는 애플리케이션으로 스위칭을 해 버립니다.

public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {
                               
                //sub application인 test로 버네기 위해 forward 이용
                return (mapping.findForward("toTest"));
               
        }

위에서 return (mapping.findForward("toTest")); 부분의 처리를 위해 당연히 “toTest” 라는 forward가 있어야 겠죠^^

메인 설정 파일인 struts-config.xml에서 아래와 같이 정의하고 있습니다.

<forward name="toTest"
                contextRelative="true"
                path="/test/mysubmit.do"
                redirect="true"/>


이제 제어가 /test/mysubmit.do라는 액션으로 인해 test 라는 애플리케이션으로 넘어 가게 됩니다. 물론 위의 /test/mysubmit.do 는 struts-test-config.xml에서 정의를 하고 있습니다. 그 설정 내용은 다음과 같습니다.

<action-mappings>           
        <!-- 현재의 설정 파일은 struts-test-config.xml 이므로... path에 "/test/mysubmit" 아님을 주의! -->
        <action
                path="/mysubmit"   
                type="login2.Welcome2"   
                validate="false"                 
        />
         
    </action-mappings>


이제는 login2.Welcom2라는 클래스가 실행 되겠죠^^

그 내용은 단순히 result.jsp로 포워딩 시키는 일을 합니다.

요기까지 입니다.

그럼 이젠 전체 소스를 확인 하도록 하죠….

====================================================================


-------------------------------
/Login/test/login.jsp
-------------------------------
<%@ page pageEncoding="euc-kr" %>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean" %>
<html>
<body>

<html:form action="/toTest" focus="id">
        <table>             
                <tr>                       
                        <th align="right">ID</th>                                             
                        <td><html:text property="id" value=""/></td>
                </tr>
                <tr>
                        <th align="right">PASSWORD</th>                                             
                        <td><html:password property="pwd" redisplay="false"/></td>
                </tr>
                <tr>                     
                        <th></th>
                        <td>
                            <html:submit/>
                            <html:reset/>                             
                        </td>
                </tr>                               
        </table>
</html:form>
</body>
</html>


-------------------------------
/WEB-INF/struts-config.xml
-------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.1//EN" "http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">
<struts-config>
     
    <!-- ========== Form Bean Definitions ================================== -->
    <form-beans>
        <form-bean name="loginForm" type="login2.LoginForm">                 
            <form-property name="pwd" type="java.lang.String" />
            <form-property name="id" type="java.lang.String" />           
        </form-bean>           
    </form-beans>
   
 
    <!-- ========== Global Forward Definitions =============================== -->
    <global-forwards>
        <forward name="toTest"
                contextRelative="true"
                path="/test/mysubmit.do"
                redirect="true"/>
    </global-forwards>
   
    <!-- ========== Action Mapping Definitions =============================== -->
    <!-- valiedate를 true라고 함으로써 LoginForm의 validate가 호출 됩니다.            -->
    <action-mappings>           
        <action
                path="/toTest"
                type="login2.toTest"
                validate="false" 
                name="loginForm"             
        />         
    </action-mappings> 
       
</struts-config>



-------------------------------
/WEB-INF/struts-test-config.xml
-------------------------------

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts-config PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 1.1//EN" "http://jakarta.apache.org/struts/dtds/struts-config_1_1.dtd">
<struts-config>
   
    <!-- ========== Global Forward Definitions =============================== -->
    <global-forwards>
        <forward name="success" path="/result.jsp"/>
    </global-forwards>
   
    <action-mappings>           
        <!-- 현재의 설정 파일은 struts-test-config.xml 이므로... path에 "/test/mysubmit" 아님을 주의!  메인 애플리케이션의 foreard에 기술한 /test/mysubmit에 의해 아래의 매핑과 연결됨, 결국 login2.Welcom2가 실행됨-->
        <action
                path="/mysubmit"   
                type="login2.Welcome2"   
                validate="false"                 
        />
         
    </action-mappings>                         
</struts-config>


-------------------------------
/WEB-INF/src/login2/toTest.java
-------------------------------
package login2;

import org.apache.struts.action.Action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;


public class toTest extends Action {       
       
        public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {
                               
                //sub application인 test로 보내기 위해 forward 이용
                return (mapping.findForward("toTest"));
               
        }
}


-------------------------------
/WEB-INF/src/login2/Welcome2.java
-------------------------------
package login2;

import org.apache.struts.action.Action;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import login2.Constants;

public class Welcome2 extends Action {       
       
        public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) {
                               
                //성공적으로 처리 되었음, test 애플리케이션의 result.jsp로 보내버림...                return (mapping.findForward(Constants.SUCCESS));
               
        }
}




-------------------------------
/Login/test/result.jsp
-------------------------------
OK~ 

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월 4일 일요일

Oracle View...(오라클 뷰)

A. . 정의
- View는 하나 이상의 Table또는 다른 View에 포함된 데이터를 원하는대로 나타낸 것
- View는 내장질의또는 가상 Table등으로 표현할수 있다.
- View는 Table에서 파생되므로 둘사이에는 많은 유사성이 있다. 예를들어 Table과 같이 최대
254개의 Column이 있는 View를 정의할수 있고 뷰를 질의하고 몇가지 제한 사항을 사용하여 뷰를
갱신,삽입,삭제를 할수있다.  뷰에대해 수행되는 모든 작업은 뷰의 기본 Table에 있는 Data에
실제 영향을 주며 기본 Table의 무결성 제약조건과 트리거를 따른다.
- 뷰에는 저장영역이 할당되지 않으며 실제로 Data를 포함하지도 않는다.
B. 처리방법
- Oracle은 View를 정의하는 질의 Text를 Data Dictionary(user_views)에 저장한다,
- Oracle은 기존공유 SQL영역에 동일한 멸령이 들어있지 않을때만 새로운 공유 SQL영역에 뷰를
참조하는 명령문을 Parsing합니다. 따라서 뷰를 사용시 공유 SQL영역과 관련하여 메모리 사용이
감소하는 이익이 있다.
- Oracle은 원래 질의를 뷰정의 질의와 병합할 때 원래 뷰를 변형하여 뷰에 대한 질의에 인덱스를
사용할지를 결정한다.

Create view emp_view as
    Select emp_no, ename, sal, loc
    From emp
    Where emp.deptno = dept.deptno
    And dept.deptno = 10;

<사용자 Query>
select ename from emp_view
where empno = 9876;


select ename
from emp, dept
where emp.deptno = dept.deptno
and  dept.deptno = 10
and  emp.empno = 9876;

Oracle은 가능한 모든 경우에 뷰에대한 질의를 뷰정의 질의 및 기본뷰 질의와 병합한다. 또한 뷰를
참조하지않고 질의를 발생하는 것 처럼 병합된 질의를 최적화한다.
따라서 Column이 뷰 정의 또는 뷰에대한 사용자정의에서 참조되는지의 여부에 관계없이 모든 참조된
기본 Table의 Column에 대한 Index를 사용한다.
만약 뷰에대한 정의와 사용자 질의를 병합할수 없다면 Index를 사용하지 않을수 있다.

C. 갱신가능한 Join View
- Join View란 From절에 하나이상의 Table또는 View를 가지며, Distinct, group by, start with,
connect by, rownum, union all, intersect, for update등의 절에서는 사용하지 않는다.

- 갱신가능한 Join View는 둘이상의 기본 Table 및 View를 포함하며 Update/Insert/Delete등의
작업이 가능하다.

- Dictionary View인 all_updatable_columns,윰_updatable_columns, user_updatable_colums
에는 갱신가능한 View의 Column을 나타낸다.

- Join View에 대한 Insert/Update/Delete등의 작업은 한번에 하나의 기본 Table에서만 가능하며,
With Check Option을 사용하여 View를 정의하는 경우에는 반복된 Table의 모든 Join Column과 모
든 Column은 갱신할수 없으며, Insert명령문은 허용되지 않는다.또한 with Check Option을
사용하여 뷰를 정의하고 키예약 Table이 반복되는 경우에는 뷰에서 행을 삭제할수 없다.

D. View생성
 - Create View명령을 사용한다.
예] create view sales_staff as
      select empno, ename, deptno
      from emp
      where deptno =10
      with check option constraint sales_staff_cnst;

    위에서 check option은 뷰가 선택할수 없는 행에 대해서는 Insert와 Update명령문이
    실행되지 않는다는 제약 조건을 갖는다.
    만약 위의 View에 deptno가 30인 행을 Insert하려고 하면 RollBack되고 오류가 발
    생한다.
E. View수정하기
위에서 만든 staff View를 재정의 하는경우
create or replace view sales_staff_view as
      select empno, ename, deptno
      from emp
      where deptno =30
      with check option constraint sales_staff_cnst;
F. View의 삭제
 - drop view sales_staff_view; 

[출처] 오라클자바커뮤니티, 오엔제이프로그래밍

2013년 8월 3일 토요일

Oracle SQL Tuning, Hint, SQL 연산(COUNT STOPKEY

SQL 연산(COUNT STOPKEY

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr

COUNT STOPKEY연산은 PSEUDO COLUMNS(의사 컬럼) WHERE절에 나타날 때 실행계획에 나타나는 SQL 연산 입니다.

SQL> select  empno from emp
  2  where rownum < 2;

     EMPNO
----------
      7369

   : 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1902391507

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY   |        |       |       |            |          |
|   2 |   INDEX FULL SCAN| PK_EMP |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------



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) 

[Oracle SQL Tuning]ACCESS 경로를 변경하는 힌트(HASH) - 오라클힌트 이론 실습

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

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


인자로 기술한 테이블에 대해 HASH SCAN이 일어나도록 하는 힌트이며 USE_HASH(해시 조인이 일어나도록 하는 힌트)와 구별되며 HASHKEYS parameter를 가지고 만들어진 CLUSTER내에 저장된 테이블에서만 적용 됩니다.

[형식]
/*+ HASH ( table ) */

[]
[먼저 클러스터를 생성]
CREATE CLUSTER MYEMP2_MYDEPT2_CLUSTER (deptno NUMBER(1))
HASH IS deptno HASHKEYS 150;
  

[실습 테이블을 만들면서 클러스터 저장하기 위해 옵션 정의]
   CREATE TABLE MYDEPT2 (
      deptno NUMBER(1) PRIMARY KEY,
  dname  VARCHAR2(100) NOT NULL)
   CLUSTER MYEMP2_MYDEPT2_CLUSTER (deptno);
  
  
   CREATE TABLE MYEMP2 (
          empno  NUMBER PRIMARY KEY,
         ename  VARCHAR2(100) NOT NULL,
         sal    NUMBER(9),
         deptno NUMBER(1) NOT NULL)
   CLUSTER MYEMP2_MYDEPT2_CLUSTER (deptno);




[테스트를 위한 데이터를 만듭니다.]


insert into mydept2 values (0, '인사팀');
 insert into mydept2 values (1, '회계팀');
 insert into mydept2 values (2, '영업팀');
 insert into mydept2 values (3, '기획팀');
 insert into mydept2 values (4, '교육팀');


commit;


DECLARE
          v_c NUMBER := 1;
BEGIN

          WHILE (v_c <= 10000000) LOOP
                insert into myemp2 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp2 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp2 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp2 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
          END LOOP;
          commit;
END;



  
클러스터 인덱스 만들기 전에 일반 인덱스로 먼저 테스트 해보자.
(무지 느리자)

sQL> create index idx_myemp2_deptno on myemp2(deptno)

SQL> SELECT  /*+ index( E idx_myemp2_dept) */
  2             Count(e.ename)
  3     FROM    MYEMP2 E, MYDEPT2 D
  4     WHERE   D.deptno = E.deptno;

COUNT(E.ENAME)
--------------
      10000000

   : 00:01:15.29

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    26 |     3   (0)|
|   1 |  SORT AGGREGATE        |              |     1 |    26 |            |
|   2 |   NESTED LOOPS         |              |     1 |    26 |     3   (0)|
|   3 |    INDEX FAST FULL SCAN| SYS_C0011304 |     1 |    13 |     2  
|*  4 |    TABLE ACCESS HASH   | MYEMP2       |  2050K|    25M|     1  




이번엔 HASH SCAN 위한 힌트를 사용해 보자.
조금 빨라졌다.

SQL> SELECT 
  2             count(E.ename)
  3     FROM    MYEMP2 E, MYDEPT2 D
  4     WHERE   D.deptno = E.deptno;

COUNT(E.ENAME)
--------------
      10000000

   : 00:01:01.54

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |    26 |     3   (0)|
|   1 |  SORT AGGREGATE        |              |     1 |    26 |            |
|   2 |   NESTED LOOPS         |              |     1 |    26 |     3   (0)|
|   3 |    INDEX FAST FULL SCAN| SYS_C0011304 |     1 |    13 |     2  
|*  4 |    TABLE ACCESS HASH   | MYEMP2       |  2050K|    25M|     1  



Oracle 11g에서 테스트 했을 때 위의 경우 NESTEDD LOOP로 풀리며 또는 힌트를 사용하지 않더라도 EMP TABLE HASH SCAN을 하는 것으로 나타났습니다.

CLUSTER를 확인 할 수 있는 VIEW는 다음과 같구요,

DBA_CLUSTERS         ALL_CLUSTERS         USER_CLUSTERS

아래에 간단한 HASH CLUSTER TABLE에 대한 설명이 있으니 참고 하세요~

다량의 범위를 자주 엑세스해야 하는 경우나 인덱스를 사용한 처리가 부담이 되는 범위(넓은 분포도), 수정이 자주 발생하지 않는 Column, 대규모 테이블, 여러 개의 테이블이 빈번한 조인을 일으킬 때 CLUSTER INDEX를 사용하시면 되는데 HASH CLUSTER에 테이블을 저장하는 것은 데이타 검색의 성능을 향상하기 위한 선택적인 방법 입니다.

Hash Cluster는 인덱스나 인덱스 Cluster를 가지는 Cluster되지 않은 테이블의 대용이며 인덱스 테이블, 인덱스 Cluster와 함께 오라클은 별도의 인덱스에 저장된 키값을 사용하는 테이블 내의 로우(row)에 위치 합니다.

또한 오라클은 물리적으로 Hash Cluster내의 테이블의 로우에 저장하고 Hash function의 결과에 의하여 검색 하는데 특정 Cluster 키값을 바탕으로 Hash Values라 불리는 분산된 수치 값을 생성하는 Hash Function을 사용 한다

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 |
----------------------------------------------------------------------------------------------