제5 외국어 영역2008.04.29 10:46

일괄적으로 DB에 Table/Index를 생성했을 경우 (Db Migration 등) Analyze 를 꼭 해주셔야 합니다.


[Analyzed 확인 방법]


select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables


select index_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_indexes


ex) select table_name, num_rows, to_char(last_analyzed, 'yyyymmdd') from user_tables;

TABLE_NAME                       NUM_ROWS TO_CHAR(
------------------------------ ---------- --------
ABS_TYPE                               38 20040101

ANNIVERS                              183 20040101
APPRFLDRHISTORY                       570 20040101
APPRFOLDER                          16885 20040101
APPRFOLDER_ERR                       3670 20040101
APPRFORM                              359 20040101
.
.
.
USR_INFO_ADMIN                          0 20040101
VAR_DEPT_INFO                           0 20040101
VIEW_TYPE                               0 20040101
WASTEBOX                                0 20040101
ZIP_CODE                            44195 20040101

252 rows selected.


※ 참고 : desc user_tables 에서 보통 num_rows 로도 확인 가능

             


[특정 Table만 Analyze 하는 방법]


analyze table document compute statistics

ex) DOCUMENT Table 만 Analyze


analyze index xpkdocbox compute statistics

ex) XPKDOCBOX Index 만 Analyze


[전체 Table Analyze 하는 간단한 방법]


1. vi analyze_all.sql
    select 'analyze table || table_name || estimate statistics;' from user_tables


2. @analyze_all.sql


3. set heading off
     set echo off
     set feedback off
     set pagesize 300  (line 이 300 미만일 경우)
     spool analyze_table.sql
     /
     spool off


4. vi analyze_table.sql
    필요없는 Line 제거 및 정리


5. @analyze_table.sql



[전체 Index Analyze 하는 간단한 방법]


1. vi analyze_all.sql
    select 'analyze index || index_name || estimate statistics;' from user_indexes


2. @analyze_all.sql


3. set heading off
     set echo off
     set feedback off
     set pagesize 300  (line 이 300 미만일 경우)
     spool analyze_index.sql
     /
     spool off


4. vi analyze_index.sql
    필요없는 Line 제거 및 정리


5. @analyze_index.sql

Posted by is윤군
TAG Oracle
제5 외국어 영역2008.03.14 01:44
<update id="modTelNoStat" parameterClass="map">
     UPDATE  TCR_TELNO_MT  A
      SET (
            A.EFT_STRT_DT
          , A.EFT_STRT_TM
          , A.TEL_NO_STAT
          , A.TEL_STAT_CHNG_RESN
          ,A.SVC_CMPS_ID
          ,A.SUBS_RCPT_ID
          ,A.APPL_ID
          ,A.CHG_PSN_ID
          ,A.CHG_DTTM      ) =
          (SELECT
                    #EFT_STRT_DT#
                  , #EFT_STRT_TM#
                  <isNotNull property="TEL_NO_QTA_STAT">
                  , #TEL_NO_QTA_STAT#
                  , '2'
                  </isNotNull>
                  <isNull property="TEL_NO_QTA_STAT">
                  , DECODE(#CTRT_STAT#,'10','5','1')
                  , DECODE(#CTRT_STAT#,'10','4','5')
                  </isNull>
                  , B.SVC_CMPS_ID
                  , B.SUBS_RCPT_ID
                  , #_APPL_ID_#
                  , #CHG_PSN_ID#
                  , TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')
                FROM
                    TCC_SVC_CMPS_MT B
                    INNER JOIN  TCC_TELNO_USE_HIST C
                ON  B.SVC_CMPS_ID  = C.SVC_CMPS_ID
                AND C.EFT_END_DT   = '99991231'
                AND C.EFT_END_TM   = '235959'
                <isNotNull property="TEL_NO_QTA_STAT">
                AND C.TEL_NO_QTA_STAT  = #TEL_NO_QTA_STAT#
                </isNotNull>
                <isNull property="TEL_NO_QTA_STAT">
                AND C.TEL_NO_QTA_STAT  = DECODE(#CTRT_STAT#,'10','5','1')
                </isNull>
                WHERE
                     B.CTRT_ID      = #CTRT_ID#
                 AND B.EFT_END_TM   = '235959'
                 AND B.EFT_END_DT   = '99991231')
      WHERE
            EXISTS
            (SELECT 'X' FROM
                    TCC_SVC_CMPS_MT B
                    INNER JOIN  TCC_TELNO_USE_HIST C
                ON  B.SVC_CMPS_ID  = C.SVC_CMPS_ID
                AND C.EFT_END_DT   = '99991231'
                AND C.EFT_END_TM   = '235959'
                <isNotNull property="TEL_NO_QTA_STAT">
                AND C.TEL_NO_QTA_STAT  = '3'
                </isNotNull>
                <isNull property="TEL_NO_QTA_STAT">
                AND C.TEL_NO_QTA_STAT  = '4'
                </isNull>
                WHERE
                     B.CTRT_ID      = #CTRT_ID#
                 AND B.EFT_END_TM   = '235959'
                 AND B.EFT_END_DT   = '99991231'
                 AND C.TEL_NO = A.TEL_NO )
</update>
Posted by is윤군
TAG Oracle
제5 외국어 영역2008.01.02 14:20

펌 : http://blog.naver.com/khlee1000/40023271788

▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
Subject:  Materialized View
Type:     WHITE PAPER
Status:   PUBLISHED
▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
 
◎ 이 문서는 Materialized View에 대해 8i부터 10g까지의 자료를 정리한 것이다.
 
◎ 목차:
   1. Materialized View
   2. Materialized View 관련 Initialization 파라미터
   3. Materialized View 사용에 필요한 권한
   4. Query Rewrite에서 Materialized View 사용 여부 판단 알고리즘
   5. Materialized View와 Integrity Constraints
   6. Query Rewrite와 Hint 사용
   7. Three Types of Materialized Views
   8. Materialized View Log 의 구조 (WITH ROWID, PRIMARY KEY 사용)
   9. Materialized View의 문법
  10. Materialized View 의 Index
  11. Materialized View를 만드는 방법 (사용예제)
  12. Materialized View에서 Index Organize Table을 이용하기 (8.1.6 이상)
  13. Materialized View의 On Commit Refresh 기능 (8.1.6 이상)
  14. Materialized View 생성 시 ORA-12054 ERROR 해결 방법 (8.1.6 이상)
  15. Materialized View 에서 Order By 절 사용하기 (8.1.7 이상)
  16. Materialized View를 Refresh 하는 방법
  17. Materialized View와 관련된 시스템 딕셔너리
  18. 진단을 위한 DBMS_MVIEW.EXPLAIN_REWRITE / EXPLAIN_MVIEW 사용법 (9.0.2 이상)
  19. Nested Materialized Views
 
 
 
1. Materialized View
 
◎ Oracle 8i에서의 "MATERIALIZED VIEW" 는 "SNAPSHOT" 와 SYNONYM 으로 생각 하면 가장 좋을 것 같다. 
   이는 대용량의 DATABASE 에서 SUM 과 같은 AGGREGATE FUNCTION 사용 시 값 비싼 COST 를 줄이는 데
   사용하기에 적합한데 이는 REPLICATE 가 가능하여 SNAPSHOT 처럼 사용이 가능함을 의미한다.
 
◎ Materialized View(이하 MVIEW)는 DW 환경이나, replication 환경에 유용한 기능으로, inner-join,
   outer-join, equi-join 등 각종 view를 수동으로 갱신하거나, 주기적으로 자동 갱신을 할 수 있게 해 준다.
 
◎ 원격 데이터베이스의 테이블이 포함된 MVIEW는 양방향 replication을 지원한다. 또한 MVIEW는 사용자에게는
   투명하게 cost optimization에 기반을 둔 qurey rewrite 기능을 제공한다.
 
◎ Query rewrite란 table과 view들에 대한 연산으로 이루어진 SQL 문장이 해당 table들에 기반해서 정의된
   materialized view에 대한 연산으로 변경되는 것을 말한다. 이러한 변경은 Optimizer에 의해 내부적으로
   수행되기 때문에 일반 사용자나 application은 materialized view의 존재 여부를 인식하지 않아도 된다.
 
◎ Query rewrite는 cost-based optimization 모드에서만 가능하다. 따라서 Materialized View를 만들기 위해선
   해당 Table이 반드시 Analyze 되어 있어야 한다.
 
◎ Query rewrite 기능을 제공하기 위해 Oracle 에서는 Dimension이라는 객체를 추가 했는데,
   Dimension 객체는 대용량 데이터에 대한 질의를 집계성 데이터에 대한 질의로 자동 변환 해 주는 기능을 제공해 준다.
 
◎ MVIEW는 질의 실행을 할 때마다 매번 대량의 join이나, Aggregation 연산(예: SUM, COUNT 등)을 수행하지 않고,
   미리 계산된 값을 질의하기 때문에 성능 향상을 가져올 수 있으며, optimizer는 MVIEW가 어느때 사용되는 것이
   적절할지를 판단할 수 있게 설계되었다.
 
◎ Query rewrite는 사용자에는 투명하다. 만약 환경이 적절히 셋업 되어 있다면, 대량 대이터에 대한
   복잡한 질의 응답 속도를 획기적으로 개선할 수 있게 한다.
 
 
 
2. Materialized View 관련 Initialization 파라미터
 
◎ MVIEW와 관련된 파라미터 목록은 다음과 같다.
   - optimizer_mode
   - query_rewrite_enabled
   - query_rewrite_integrity
   - compatible
 
◎ 다음은 파라미터에 대한 설명이다.
  1) optimizer_mode
     - Query Rewrite 기능을 사용하기 위해서는 init.ora 파일의 optimizer mode값은 "ALL_ROWS"나
       "FIRST_ROWS"로 지정하거나, "CHOOSE"인 상태에서 모든 테이블을 ANALYZE 시켜 두어야 한다.
   
  2) query_rewrite_enabled
     - 파라미터 query_rewrite_enabled 의 값은 "TRUE"로 지정한다.
   
  3) query_rewrite_integrity
     - 파라미터 query_rewrite_integrity 는 선택적으로 지정할 수 있는 파라미터이지만,
       "STALE_TOLERATED", "TRUSTED", 또는 "ENFORCED" 으로 지정되어야 한다.
 
     - 이 파라미터는 query rewrite의 정확성을 제어 하는 파라미터이다.

     - 각각의 의미는 다음과 같다
       ☞ TRUSTED : optimizer에서 MVIEW의 데이터가 정확하다고 간주하고 질의 수행. Integrity 확인을 하지않음.
       ☞ ENFORCED: query_rewrite_integrity 의 기본값으로, 사용자가 integrity constraint를 확인하여야 한다.
                    MVIEW는 fresh한 데이터를 포함하여야 한다.
       ☞ STALE_TOLERATED : Optimizer에서 데이터가 stale 상태이거나 fresh 상태인 경우 모두 MVIEW 사용
 
 
 
3. Materialized View 사용에 필요한 권한
                        
◎ MVIEW를 사용하기 위한 권한은 base 테이블에 대한 사용자의 권한에 달려있다.
   두개의 중요한 시스템 권한은 다음과 같다.
   - grant rewrite
   - grant global rewrite
 
◎ 다음은 두개의 중요한 시스템 권한에 대한 설명이다.
  1) grant rewrite
     - MVIEW의 base table이 모두 사용자 자신의 테이블일 경우, 자신이 선언한 MVIWE 사용 가능.
   
  2) grant global rewrite
     - 사용자가 어느 schema에 속한 MVIEW라도 사용 가능.
   
◎ MVIEW 사용에 필요한 권한이 충족된 경우 다음 조건을 만족하여야 한다.
   a.  세션에 query rewrite 기능이 enable 되어 있음.
   b.  MVIWE 자체가 enable 되어 있음.
   c.  integrity level이 적절히 셋업 되어 있음.
   d.  MVIEW에 데이터가 존재함.
 
 
4. Query Rewrite에서 Materialized View 사용 여부 판단 알고리즘
 
1) Full SQL Text Match
   - 질의의 select 문장과 MVIEW를 만들때 사용한 select 문장 비교
 
2) Partial SQL Text Match
   - Full SQL Text Match가 실패할 경우 select 문장의 from 절 이하의
     내용이 MVIEW를 만들때 사용한 내용과 일치하는지 비교
 
3) Generla Query Rewrite Method
   - 1, 2 항에서 실패할 경우, optimizer에서 MVIEW 사용 가능 여부를 판단.
   - 필요한 데이터가 MVIWE에서 제공하는 것 보다 적거나, 많거나, 변환 가능
     한지를 판단하고, MVIWE 데이터가 충분한지 여부를 joing compatibility,
     grouping compatibility, aggregate compatibility 등을 확인하여 판단
 
 
 
5. Materialized View와 Integrity Constraints
 
◎ MVW는 DW 환경에서 유용한데, 대부분의 DW는 integrity constraint를 사용하지 않는다.
   즉 DW는 원천 데이터에서 integrity가 보장되었다고 간주한다.
 
◎ 다른 한편으로 integrity constraint는 query rewrite에 유용하다.
   이 모순되는 사항은 NOVALIDATE 와 RELY 옵션을 이용해 조율을 맞추어야 한다.
 
◎ query rewrite와 integrity constraint의 연관 관계
  1) query_rewrite_enabled = enforced
    - 데이터베이스의 constarint는 validate 상태로 두어야 한다.
 
  2) query_rewrite_enabled = stale_tolerated | trusted
    - 데이터베이스의 constraint를 nonvalidate, rely로 지정 해 준다.
 
 
 
6. Query Rewrite와 Hint 사용
 
◎ Index 관련 Hint를 사용하는 것 처럼, query rewite 관련 Hint를  사용하여 제어할 수 있다.
   - NOREWRITE :  Select /*+ NOREWRITE */...
   - REWRITE   :  Select /*+ REWRITE(MView_Name) */...
 
 
 
7. Three Types of Materialized Views
 
1) Materialized Aggregate View (MA-View)  
  - One Table
  - Aggregation (Sum, Avg...)
  - Example:
    create materialized view MA
    build immediate
    enable query rewrite
    refresh on demand
    as
    select g_no, sum(amount), count(amount), count(*)
    from fact -- One Table
    group by g_no;
 
   
  
2) Materialized Join View (MJ-View)
  - Many Tables
  - inner/outer join (join index)
  - no aggregates
  - Rowids from base tables in MV for incremental refresh
  - Example:
    create materialized view MAJ
    build immediate
    enable query rewrite
    refresh on demand
    as
    select f.g_no, f.amount, t.t_day, f.rowid f_rid
    from fact f, time t
    where f.t_no = t.t_no;
 
   
  
3) Materialized Aggregate Join View (MAJ-View)  
  - Many Tables
  - inner/outer join (join index)
  - Aggregation (Sum, Avg...)
  - Example:
   
create materialized view MAJ
    build immediate
    enable query rewrite
    refresh on demand
    as
    select g_no, sum(amount), count(amount), count(*), t_day
    from fact, time
    where f.t_no = t.t_no
    group by g_no, t_day;
 
 
 
8. Materialized View Log 의 구조 (WITH ROWID, PRIMARY KEY 사용)
 
◎ 우리가 Materialized View Log를 생성하면 Schema에는 mlog$_<master_table_name> 구조의
   Log Table이 생성이 된다.
 
  - Oracle은 이 Log에 변화되는 사항을 반영하게 된다. 그리고 Fast Refresh가 되면 이 Log의
    데이터를 Materialized View에 반영하게 된다.
 
  - 다음의 문장으로 확인을 할 수 있다.
 
    select log_owner, master, log_table, rowids, primary_key
    from dba_mview_logs;
 
    LOG_OWNER       MASTER     LOG_TABLE   ROWIDS    PRIMARY_KEY
    --------------- ---------- ----------- --------- -----------
    SCOTT           DEPT       MLOG$_DEPT  NO        YES
 
 
◎ Log를 생성하기 위한 문법 구조
 
   CREATE  MATERIALIZED  VIEW  LOG  ON <Master_Table_Name>
   TABLESPACE  <Tablespace_name>
   PCTFREE <Percent_Of_Free_Space>
   WITH [ ROWID | PRIMARY KEY ] , [ SEQUENCE ]
   INCLUDING NEW VALUES ;
 
  - WITH 절의 사용 예
    1) WITH ROWID
     SQL> create materialized view log on fnd_user with rowid including new values;
 
    2) WITH ROWID(Col1, ... , ColN)
     SQL> create materialized view log on fnd_user with rowid(user_id, user_name)  
        2  including new values;
 
    3) WITH ROWID, SEQUENCE(Col1, ... , ColN)
     SQL> create materialized view log on fnd_user with rowid, sequence(user_id, user_name)
        2   including new values;
 
    4) WITH ROWID, PRIMARY KEY
     SQL> create materialized view log on wf_in with rowid, primary key  including new values;
 
    5) WITH PRIMARY KEY
     SQL> create materialized view log on wf_in with primary key  including new values;
 
    6) WITH PRIMARY KEY, SEQUENCE(Col1, ... , ColN)
     SQL> create materialized view log on wf_in with primary key, sequence(corrid)
        2  including new values;
 
※ 주의: WITH ROWID(Col1, ... , ColN), SEQUENCE(Col1, ... , ColN) 의 문장은 가능하지 않다.
 
※ 주의:WITH ROWID(Col1, ... , ColN) 보다는 WITH ROWID, SEQUENCE(Col1, ... , ColN) 의 문장써라.
- WITH ROWID(Col1, ... , ColN) 와 WITH ROWID, SEQUENCE(Col1, ... , ColN)의 차이
  ☞ WITH ROWID(Col1, ... , ColN)는 순서가 부여되지 않는다.
  ☞ WITH ROWID, SEQUENCE(Col1, ... , ColN)는 SEQUENCE에 의해 컬럼의 순서가 부여 된다.
     이는 나중에 Fast Refresh를 하기 위해 필요할 때가 있다.. 따라서 두번째 방법을 사용하라.  
  
 
◎ Log를 생성시 Table의 구조..
 
  1) ROWID를 가지고 Log를 생성할 때
 
   SQL> create materialized view log on fnd_user with rowid including new values;
   SQL> desc mlog$_fnd_user
    Name                            Null?    Type
    ------------------------------- -------- ----
    M_ROW$$                                  VARCHAR2(255)
    SNAPTIME$$                               DATE
    DMLTYPE$$                                VARCHAR2(1)
    OLD_NEW$$                                VARCHAR2(1)
    CHANGE_VECTOR$$                          RAW(255)
   SQL> drop materialized view log on fnd_user;
 
 
  2) Primary Key 를 가지고 Log를 생성할 때
   SQL> create materialized view log on wf_in with primary key including new values ;    SQL> desc mlog$_wf_in
    Name                            Null?    Type
    ------------------------------- -------- ----
    MSGID                                    RAW(16)
    SNAPTIME$$                               DATE
    DMLTYPE$$                                VARCHAR2(1)
    OLD_NEW$$                                VARCHAR2(1)
    CHANGE_VECTOR$$                          RAW(255)
   SQL> drop materialized view log on wf_in; 
 
  3) ROWID와 NON-Primary Key 를 가지고 Log를 생성할 때
   SQL> create materialized view log on fnd_user with rowid, sequence(user_id, user_name)
      2 including new values ;    SQL> desc mlog$_fnd_user
    Name                            Null?    Type
    ------------------------------- -------- ----
    USER_ID                                  NUMBER(15)
    USER_NAME                                VARCHAR2(100)
    M_ROW$$                                  VARCHAR2(255)
    SNAPTIME$$                               DATE
    DMLTYPE$$                                VARCHAR2(1)
    OLD_NEW$$                                VARCHAR2(1)
    CHANGE_VECTOR$$                          RAW(255)
   SQL> drop materialized view log on fnd_user; 
 
   
  4) ROWID와 Primary Key 를 가지고 Log를 생성할 때
   SQL> create materialized view log on wf_in with rowid, Primary Key including new values ;    SQL> desc mlog$_wf_in
    Name                            Null?    Type
    ------------------------------- -------- ----
    MSGID                                    RAW(16)
    M_ROW$$                                  VARCHAR2(255)

    SNAPTIME$$                               DATE
    DMLTYPE$$                                VARCHAR2(1)
    OLD_NEW$$                                VARCHAR2(1)
    CHANGE_VECTOR$$                          RAW(255)
   SQL> drop materialized view log on wf_in;
 
 
 
9. Materialized View의 문법
 
◎ 문법 :
 
   CREATE  MATERIALIZED  VIEW  <View_Name>
   TABLESPACE  <Tablespace_name>
   BUILD [ IMMEDIATE | DEFERRED ]
   REFRESH [ FAST | COMPLETE | FORCE ] ON [ DEMAND | COMMIT ]
   START WITH <First_Refresh_Time>  NEXT <Refresh_Time>
   WITH [ ROWID | PRIMARY KEY ]
   [ ENABLE | DISABLE ] QUERY REWRITE
   AS
   <Select_Statements>
 
 
BUILD 절  (처음 MView를 어떻게 생성할 것인가에 대한 문장)
 
  - IMMEDIATE : Default 값이다. 만드는 즉시 MV에 값이 생성이 된다.
 
  - DEFERRED  : 이 옵션을 이용해서 MView를 만들면 초기에는 값이 생성되어 있지 않는다.
                그리고, DBMS_MVIEW.REFRESH()를 이용해서 Refresh 될 때 비로서 값이 생성된다.
                단, 처음에는 반드시 Full Refresh를 수행해야지만 전체 값이 생성이 된다.
 
 
REFRESH(Refresh를 어떻게 할 것인가에 대한 문장)
 
  - FAST     : MV의 Master Table에 DML이 발생할 경우, 변경된 DML만 MV에 반영한다.
               이는 MV가 FAST일 때 System이 자동으로 "Direct Loader Log"라는 것을 생성하고,
               여기에 변화된 direct-path DML을 보관하고 있기 때문이다.  
 
  - COMPLETE : MV와 Master Table을 비교하면서 COMPLETE Refresh를 수행한다.
               그리고 비록 MV가 FAST일지라도, COMPLETE Refresh를 수행하면 COMPLETE로 수행된다.
 
  - FORCE    : Default 값이다.
               ????
  
 
ON(언제 Refresh를 할 것인지에 대한 문장)
 
  - DEMAND : Default 값이다.
             Refresh는 DBMS_MVIEW.REFRESH()를 이용해서 Refresh 될 때 변경된 DML이 반영된다.
 
  - COMMIT : DML이 발생한 후 Commit을 만나면 그 결과를 바로 MView에 반영한다.
          *** 자세한 사항은 아래의 "13. Materialized View의 On Commit Refresh 기능"을 참조
 
 
START WITH 절 : 처음 언제 Refresh를 할 것인지를 명시한다.
 
   NEXT 절  :  Refresh 주기를 몇시간으로 줄 것인지 명시한다.
 
 
WITH (Logging 정보를 명시하는 문장)
 
  - ROWID : Master Table의 Primary Key가 없을 경우 ROWID를 이용해서 생성할 수 있다.
            실제로 ROWID가 훨씬더 빠른 속도를 보장한다.
 
  - PRIMARY KEY : Default 값이다. Master Table의 Primary Key를 이용해서 MView를 생성한다.
 
  - WITH ROWID, PRIMARY KEY 이렇게 동시에 사용할 수도 있다.
 
 
 
10. Materialized View 의 Index
 
◎ Materialized View 는 Table에서 사용하는 Index를 다 사용할 수 있다.
 
   - Oracle 9i 이상의 Materialized View에서는 Function Based Index 뿐만 아니라,
 
   - Oracle 8i 이상에서는 Index Organize Table도 가능하다.
     자세한 것은 "12. Materialized View에서 Index Organize Table을 이용하기" 참조.
 
 
◎ 예제..
 
   SQL> desc FND_USER_MV
    Name                            Null?    Type
    ------------------------------- -------- ----
    USER_ID                         NOT NULL NUMBER(15)
    USER_NAME                       NOT NULL VARCHAR2(100)
    COUNT(*)                                 NUMBER
   
    SQL> create index FND_USER_MV_N1 on FND_USER_MV(USER_ID);
   
    Index created.
 
 
11. Materialized View를 만드는 방법 (사용예제)
 
◎ Materialized View를 만들때 고려사항
   a. 만들려고 하는 Materialized View가 어떤 Type 인지..
   b. Index는 어떻게 생성을 할 것인지.
   c. Refresh 주기를 어떻게 설정할 것인지...
   d. ON COMMIT을 사용할 지, 아니면 ON DEMAND를 사용할 지..
 
◎ 일반적으로 REFRESH FAST ON COMMIT 인 Materialized View는 실제 마스터 테이블의 DML 작업시
   기존 보다 많은 부하가 마스터 테이블에 생성이 됩니다.
 
다음과 같은 Privileges를 갖어야 한다.
   SQL> grant create any materialized view to disuser;
   SQL> grant drop any materialized view to disuser;
   SQL> grant alter any materialized view to disuser;
   SQL> grant global query rewrite to disuser;
   SQL> grant analyze any  to disuser;
 
◎ Materialized View를 만들기 위한 전제 조건.
  
   1) Materialized View의 대상이 되는 모든 Table은 Analyze 되어 있어야 합니다.
      SQL> analyze table GL.GL_PERIOD_STATUSES compute statistics;
      Table analyzed.
 
   2) 사용되는 모든 MASTER Table의 컬럼에 대해 LOG Table을 생성합니다.
      SQL> create materialized view log on applsys.fnd_user
         2 with rowid, sequence(user_id, user_name) including new values;
  
   3) On Commit Fast Refresh를 원한다면, Master Table이 존재하는 Schema에서 MView를 만들어야 함
  
     - 그렇지 않으면 다음의 에러가 발생
       *) "ORA-12015: cannot create a fast refresh materialized view from a complex query"
       *) "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view"
  
     - 따라서 APPS와 같은 Schema에서 여러 User의 Table을 이용해서 Materialized View를 만든다면,
       "REFRESH COMPLETE ON DEMAND" or "REFRESH FORCE ON DEMAND" 로 만들 수 밖에 없다.
  
     - 그렇지 않고, 해당 Schema에 모든 Master Table에 존재하는 경우는 해당 Schema에
       Materialized View를 만들고 이에 대한 Synonym을 주는 것이 가장 좋다.
  
  
◎ Single Table에 대한 Materialized View 만들는 방법:
  
   1) Primary Key가 있는 Single Table에 대한 MV
  
     *) Primary Key가 있는 Single Table 경우는 하나의 Row를 결정할 수 있는
        Unique 한 값이 있기 때문에 어떤 방식으로든 쉽게 만들 수 있다.
  
     i) WITH PRIMARY KEY를 이용해서 Log Table을 만든 경우
  
        SQL> create materialized view log on applsys.wf_in
           2 with primary key, sequence(corrid) including new values;      
  
        SQL> create materialized view applsys.wf_in_mv refresh fast
           2 with primary key as select msgid, corrid from wf_in;  
  
        SQL> drop materialized view applsys.wf_in_mv;
  
        SQL> create materialized view applsys.wf_in_mv build immediate refresh fast on commit
           2 with primary key as select msgid, corrid from wf_in;  
  
        SQL> drop materialized view wf_in_mv;
  
       SQL> create materialized view applsys.wf_in_mv build immediate refresh fast on commit
           2 with  primary key as select  msgid, sum(corrid) from wf_in
           3 where corrid <> '10' group by msgid;
  
  
     ii) WITH ROWID를 이용해서 Log Table을 만든 경우
  
        SQL> create materialized view log on wf_in
           2 with rowid, sequence(msgid, corrid) including new values;      
  
        SQL> create materialized view wf_in_mv refresh fast
           2 with rowid as select msgid, corrid from wf_in;  
  
        SQL> drop materialized view wf_in_mv;
  
       SQL> create materialized view wf_in_mv build immediate refresh fast on commit
           2 with rowid as select msgid, corrid from wf_in;  
  
        SQL> drop materialized view wf_in_mv;
  
       SQL> create materialized view wf_in_mv build immediate refresh fast on commit
           2 with rowid as select  msgid, sum(corrid) from wf_in
           3 where corrid <> '10' group by msgid;
  
  
   2) Primary Key가 없는 Single Table에 대한 MV
  
      *) Primary Key가 없는 Single Table 경우는 하나의 Row를 결정할 수 있는
         Unique 한 값이 없기 때문에 Unique하게 만드는 방법을 써야만 한다.
  
      *) 만약 Unique하게 하는 방법을 적용하지 않고 만든다면 다음의 에러가 난다.
 
         SQL> create materialized view log on fnd_user
            2 with rowid, sequence(user_id, user_name) including new values;

         SQL> create materialized view fnd_user_mv refresh fast
            2 as select user_id, user_name from fnd_user;
         ERROR at line 1:
         ORA-12014: table 'FND_USER' does not contain a primary key constraint
 
         SQL> create materialized view fnd_user_mv refresh fast with rowid as
            2 select distinct user_id, user_name from fnd_user;
         ERROR at line 2:
         ORA-12015: cannot create a fast refresh materialized view from a complex query
       
         SQL> create materialized view log on fnd_user with rowid including new values;

         SQL> create materialized view fnd_user_mv refresh fast with rowid
            2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;
         ERROR at line 2:
         ORA-12033: cannot use filter columns from materialized view log on "APPLSYS"."FND_USER"
  
 
      *) 위의 에러를 해결하기 위해서는 하나의 Row를 Unique하게 만든는 방법을 적용해야 한다.
  
        SQL> create materialized view log on fnd_user
           2 with rowid, sequence(user_id, user_name) including new values;
 
        SQL> create materialized view fnd_user_mv build immediate refresh fast with rowid
           2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;
 
        SQL> drop materialized view fnd_user_mv;
 
        SQL> create materialized view fnd_user_mv build immediate refresh fast on commit with rowid
           2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;
  

      *) 또한 Aggregation이 있는 경우 반드시 Count(*)를 써서 Unique 성을 보장해야 한다.
  
        SQL> create materialized view log on mv1
           2 with rowid, sequence(key, bonus) including new values;
  
        SQL> create materialized view mv1 build immediate refresh fast on commit
           2 s
           3 elect count(*), substr(key,1,1),
           4       sum(decode(trim(key),'aa',bonus,0)) as s1,
           5       count(decode(trim(key),'aa',bonus,0)) as c1,
           6       sum(decode(trim(key),'ab',bonus,0)) as s2,
           7       count(decode(trim(key),'ab',bonus,0))as c2,
           8       sum(decode(trim(key),'ac',bonus,0)) as s3,
           9       count(decode(trim(key),'ac',bonus,0)) as c3
          10 from mv1
          11 group by substr(key,1,1);
  
◎ Multiple Joined Table에 대한 Materialized View 만들는 방법:
 
  ※ Multiple Joined Table에 대한 Materialized View를 만들기 위해서는
     반드시 해당 Table과 Column에 대한 Log Table을 만들어야 한다.
 
  ※ 그리고 두개의 Table이 Join이 되어서 결과가 나오는 구조이기 때문에,
     각 Row에 대한 Unique 성을 보장하기 위해서 반드시 Count(*)를 사용 해야 합니다.
 
  ※ Materialized View를 만들 경우는 반드시 해당 User에서 만들는게 좋다.
 
  ※ 다음과 같은 SQL에 대해 Materialized View를 만드는 것을 가정하자.
      create materialized view xen_emp_sal_mv
      refresh fast on commit
      as
      select count(*) as cnt
            ,xef.employee_id
            ,xef.name
            ,xef.registration_number
            ,xsf.year
            ,xsf.month
            ,sum(xsf.salary) as salary
            ,count(xsf.salary) as cnt_salary
            ,sum(xsf.bonus) as bonus
            ,count(xsf.bonus) as cnt_bonus
            ,sum(xsf.education) as education
            ,count(xsf.education) as cnt_education
            ,sum(xsf.benefit) as benefit
            ,count(xsf.benefit) as cnt_benefit
        from xen_employee_f xef
            ,xen_salary_f   xsf
       where xef.employee_id = xsf.employee_id
       group by xef.employee_id, xef.name, xef.registration_number, xsf.year, xsf.month;
 
  ※ 이때 전제 사항은 xen_employee_f, xen_salary_f Table은 DISUSER Schema에 존재하고,
     Materialized View 또한 DISUSER Schema에 만들면서, Fast Refresh, On Commit의
     Materialized View를 만듬을 전제로 한다.
 
  ※ 그렇지 않고 APPS Schema에 만들고자 한다면, LOG는 DISUSER Schema에 생성하고,
     APPS Schema에서 Materialized View는 REFRESH COMPLETE ON DEMAND로 만들어야 한다.
 
  ※ 그러면 다음과 같은 방법으로 만들 수 있다.
     SQL> create materialized view log on disuser.xen_employee_f
        2 with rowid, sequence(employee_id, name, registration_number)
        3 including new values;
     
     SQL> create materialized view log on disuser.xen_salary_f
        2 with rowid,
        3 sequence(salary_id, employee_id, year, month, salary, bonus, education, benefit)
        4 including new values;
     
     SQL> create materialized view xen_emp_sal_mv
       2  tablespace euld
       3  pctfree 10
       4  build immediate
       5  refresh fast on commit
       6  enable query rewrite
       7  as
       8  select count(*) as cnt
       9        ,xef.employee_id
      10        ,xef.name
      11        ,xef.registration_number
      12        ,xsf.year
      13        ,xsf.month
      14        ,sum(xsf.salary) as salary
      15        ,count(xsf.salary) as cnt_salary
      16        ,sum(xsf.bonus) as bonus
      17        ,count(xsf.bonus) as cnt_bonus
      18        ,sum(xsf.education) as education
      19        ,count(xsf.education) as cnt_education
      20        ,sum(xsf.benefit) as benefit
      21        ,count(xsf.benefit) as cnt_benefit
      22    from xen_employee_f xef
      23        ,xen_salary_f   xsf
      24   where xef.employee_id = xsf.employee_id
      25   group by xef.employee_id, xef.name, xef.registration_number, xsf.year, xsf.month;
 
 
 
12. Materialized View에서 Index Organize Table을 이용하기 (8.1.6 이상)
 
◎ Oracle 8.1.6 이전 버젼에서 Materialized View의 Index 사용...
  - Oracle 8i 이전에는 CREATE MATERIALIZED VIEW 는 'ORGANIZATION INDEX'를 가질수 없었다.
  - 즉 MV 는 heap-organized table 만 사용가능하였다.
  - 이전 version 에서 IOT 를 이용한 MV 를 생성시는 ora-905를 발생시킨다
 
◎ 하지만 Oracle 8.1.6 부터는 Heap 또는 Index-Organized Table(IOT)을 위한 logging이 가능하다

◎ 사용 예제
 
   ※ 다음과 같은 Mview가 있다고 가정하자
      SQL> -- Create heap-organized table, test.t1
      SQL> create table t1 (col1 number primary key, col2 varchar2(255));
 
      SQL> -- Create Index-Organized Table (IOT), test.t1_iot
      SQL> create table t1_iot (col1 number, col2 varchar2(255),
         2 constraint t1_iot_pk primary key (col1))
         3 organization index tablespace userdata
         4 including col1 overflow tablespace userdata;
 
 
   ※ 두 가지 형태의 Index Organize Table을 만들어 보자.
      SQL> -- Demonstrate IOT MV w/OVERFLOW on heap master, test.t1
      SQL> create materialized view mv_t1 organization index 
        2  as select * from t1;
 
      SQL> -- Demonstrate IOT MV w/OVERFLOW on IOT master, test.t1_iot
      SQL> create materialized view mv_t1_iot
        2  organization index 
        3  including col1 overflow tablespace userdata 
        4  refresh with primary key 
        5  as select * from t1_iot;
 
 
   ※ 데이터 딕셔너리에 대한 분석
      SQL> -- Tables:
      SQL>
-- T1 := heap table (iot_type is null)< /FONT>
      SQL>
-- T1_IOT := iot (iot_type = 'IOT')
      SQL> -- SYS_IOT_OVER_24894 := overflow segment for T1_IOT (object_id 24894)
 
      SQL> -- Materialized Views:
      SQL>
-- MV_T1 := MV for master T1 (no associated OVERFLOW - see above CREATE)< /FONT>
      SQL>
-- MV_T1_IOT := MV for master T1_IOT
      SQL> -- SYS_IOT_OVER_24900 := overflow segment for MV_T1_IOT (object_id 24900)
 
      SQL> select table_name, iot_name, iot_type from dba_tables 
        2  where owner = 'TEST'
        3  and (table_name like '%T1%' or iot_name like '%T1%') 
        4  order by table_name;
 
      TABLE_NAME                     IOT_NAME                 IOT_TYPE
      ------------------------------ ---------------------- ------------
      MV_T1                                                    IOT
      MV_T1_IOT                                                IOT
      SYS_IOT_OVER_24894             T1_IOT                    IOT_OVERFLOW
      SYS_IOT_OVER_24900             MV_T1_IOT                 IOT_OVERFLOW
      T1
      T1_IOT                                                   IOT
 
      6 rows selected.
 
      SQL> -- MV Summary
     
SQL> select table_name, master, can_use_log, refresh_method 
        2  from dba_snapshots 
        3  where master in ('T1_IOT','T1');
 
      TABLE_NAME                     MASTER               CAN REFRESH_MET
      ------------------------------ -------------------- --- -----------
      MV_T1                          T1                    YES PRIMARY KEY
      MV_T1_IOT                      T1_IOT                YES PRIMARY KEY
 
      2 rows selected.
 
 
 
13. Materialized View의 On Commit Refresh 기능 (8.1.6 이상)
 
◎ Oracle 7이나 8 버젼의 snapshot은 지정된 시간에 refresh 작업이 기동되는 반면,
   Oracle 8i 버젼의 새로운 기능인 ON COMMIT refresh는 트랜잭션 COMMIT과 동시에
   원격 MATERIALIZED VIEW(구 SNAPSHOT)에 대하여 refresh 작업이 기동된다.
 
◎ Materialized View Log 작성 예제
  - ON COMMIT refresh 기능을 위해서는 반드시 INCLUDING NEW VALUES 옵션을 사용 하여야 한다.
   SQL> drop materialized view log on emp;
   SQL> create materialized view log on emp
      2 with rowid (empno, ename, job, mgr, hiredate, sal, deptno)
      3 including new values;
   SQL> select * from emp;

◎ ON COMMIT Materialized View 작성 예제
   SQL> drop materialized view mv_emp;
   SQL> create materialized view mv_emp
      2 build immediate
      3 refresh fast on commit
      4 as
      5 select count(*), deptno, sum(sal), count(sal)
      6   from emp
      7  group by deptno;
 
  ※ ON COMMIT refresh 기능을 위해서는 반드시
     ***  BUILD IMMEDIATE(default)
     ***  ON COMMIT 옵션
    
을 사용하여야 한다.
 
   SQL> select * from mv_emp;
          COUNT(*)     DEPTNO   SUM(SAL) COUNT(SAL)
        ---------- ---------- ---------- ----------
                 3         10       8750          3
                 5         20      10875          5

   SQL> select deptno from emp where empno = 7934;
            DEPTNO
        ----------
                10
   SQL> update emp set deptno = 20 where empno = 7934;
   SQL> commit;
   SQL> select * from mv_emp;
          COUNT(*)     DEPTNO   SUM(SAL) COUNT(SAL)
        ---------- ---------- ---------- ----------
                 2         10       7450          2
                 6         20      12175          6
  

◎ ON COMMIT refresh 사용에는 다음과 같은 제약 조건이 있다:
  
   1. Materialized View는 반드시 COUNT, SUM 등과 같은 aggregate 함수를 갖거나,
      죠인으로만 구성되어야 한다.
  
   2. 하나의 테이블을 대상으로 반드시 COUNT(*) 함수가 기술되어야 한다.
  
   3. GROUP BY 절에 의해서 grouping 대상이 되는 컬럼은 반드시 COUNT(<column_name>)가 기술되어야 한다.
  
   4. Database Link 를 이용하는 remote db에서는 실행할 수 없다.
  
   5. FAST REFRESH 기능을 사용할 경우에는 다음과 같은 제약 조건을 고려하여야 한다:
      - FROM 절에는 뷰 지정은 가능하지 않고 베이스 테이블 지정만이 가능하다.
      - SYSDATE와 ROWNUM 지정은 가능하지 않다.
      - RAW 혹은 LONG RAW 데이타 타입에 대한 지정은 가능하지 않다.
      - HAVING이나 CONNECT BY 절을 포함할 수 없다.
      - WHERE 절에 죠인을 지정할 경우에는 AND로 구성된 equi-join 만이 가능하다.
      - 서브 질의, 인라인 뷰(INLINE VIEW), UNION이나 MINUS와 같은 집합 함수는 지원되지 않는다.
   
  
◎ 발생 가능한 오류 코드
 
   - 두 경우 모두 ON COMMIT refresh를 수행할 수 없는 상황으로, 문법의 오류가를 검사하여야 한다.
     o ORA-12051: ON COMMIT attribute is incompatible with other options
     o ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
 
 
 
14. Materialized View 생성 시 ORA-12054 ERROR 해결 방법 (8.1.6 이상)
 
◎ Oracle 8i부터 제공되는 기능인 Materialized view를 생성할 때 single table에 대해 ON COMMIT refresh
   옵션을 사용하여 생성 시 발생할 수 있는 ORA-12054 에러의 해결방법에 대하여 알아보기로 한다.

◎ Problem Description
  - 다음과 같이 Materialized view를 생성하려고 시도할 때 ORA-12054 에러가 발생한다.
    현재 테이블 test_v에 다음과 같은 데이타가 저장되어 있다고 가정한다.
 
    SQL> select * from test_v;
    KEY        BONUS        SEQ
    ----- ---------- ----------
    aa        120000          1
    aa        120000          2
    ab        120500          3
    ac        620000          4
    aa        120000          8
    ab        120500          9
    ac        620000         10     
    ....................
 
  - 현재 사용자가 원하는 형태의 출력 format은 다음과 같다.
   .....     SU          S1         S2         S3  ...
   .....     --  ---------- ---------- ----------  ....
   ....       a      720777     241000    1240000  .....     
   ................
 
  - 이와 같은 결과를 얻기 위해 아래와 같이 Materialized view를 생성하였다.
 
   SQL>  create materialized view mv1
      2  build immediate
      3  refresh fast on commit
      4  as
      5  select count(*), substr(key, 1, 1),
      6         sum(decode(trim(key), 'aa', bonus, 0)) as s1,
      7         sum(decode(trim(key), 'ab', bonus, 0)) as s2,
      8         sum(decode(trim(key), 'ac', bonus, 0)) as s3,
      9         count(bonus)
     10  from test_v
     11* group by substr(key,1,1);
 
        from test_v
           *

        ERROR at line 10:
        ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
 
   - 그런데, 이와 같이 ORA-12054 에러가 발생하면서 생성이 되지 않는다.

 
◎ Solution Description
  
   ※ GROUP BY 절에 의해서 grouping 대상이 되는 컬럼(예:key)에 대하여
      COUNT(<column_name>) 함수가 반드시 기술되어야 한다.
  
   ※ 이는 single table에 대하여 ON COMMIT refresh 특성을 갖는
      materialized view를 생성 시에 반드시 고려해야 할 제약사항 중 하나이다.
 
   ※ For M.V.'s with Single-Table Aggregates, there are some conditions
      on refresh that need to be satisfied -
 
        Single Table Aggregates:
        =======================
        a) They can only have a single table.
        b) The SELECT list must contain all GROUP BY columns.
        c) Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same.
        d) They cannot have a WHERE clause.
        e) They cannot have a MIN or MAX function.
        f) A materialized view log must exist on the table and must contain all columns
           referenced in the materialized view.
           The log must have been created with the INCLUDING NEW VALUES clause.
        g) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
        h) If VARIANCE(expr) or STDDEV(expr) is specified, you must have COUNT(expr) and SUM(expr).
 
   ※ 위의 materialized view 생성 문장이 실패한 이유는 위의 제약 조건 중 g)번을 위배했기 때문이다. 
  
   ※ 즉, SUM(expr)에 대한 각각의 COUNT(expr) statement가 빠져 있기 때문이다.
      각 SUM(expr)에 대하여 다음과 같이 모든 COUNT 함수가 추가되어야 한다.
 
   ※ 위와 같은 제약 조건에 따라서 사용자의 materialized view 생성 문장은 다음과 같이 수정되어야 한다.
 
        SQL> create materialized view mv1
           2 build immediate
           3 refresh fast on commit
           4 as
           5 select count(*), substr(key,1,1),
           6        sum(decode(trim(key),'aa',bonus,0)) as s1,
           7        count(decode(trim(key),'aa',bonus,0)) as c1,
           8        sum(decode(trim(key),'ab',bonus,0)) as s2,
           9        count(decode(trim(key),'ab',bonus,0))as c2,
          10        sum(decode(trim(key),'ac',bonus,0)) as s3,
          11        count(decode(trim(key),'ac',bonus,0)) as c3
          12 from test_v
          13 group by substr(key,1,1);
 
 
 
15. Materialized View 에서 Order By 절 사용하기 (8.1.7 이상)
 
◎ Materialized view를 생성하거나, INSERT-SELECT문에서, 보다 나은 성능을 위해 ORDER BY절을
   사용할 수 있게 되었다.  이렇게 하여 table이나 materialized view에 data가 insert될 때,
   지정된 order로 insert할 수 있어, insert된 order와 같은 순서로 select할 때 성능을 향상시킬 수 있다.
 
◎ 이렇게 처음 materialized view를 만들 때 ordering을 하면 data가 physical하게 clustering된다. 
 
◎ 만약 order된 column에 대해 index가 생성되어 있을 경우, 그 index를 사용하여 materialized view의
   data를 access하면, physical clustering되어 있으므로 I/O time이 현저히 줄어든다.
 
◎ Materialized view에서 ORDER BY절은 처음 CREATE시에만 사용할 수 있으며 full refresh 나
   incremental refresh 때에는 사용할 수 없다.
 
◎ ORDER BY절은 materialized view의 definition에 포함되지 않으므로, 이로 인해 Oracle이 materialized
   view를 detect하는 데에 변화는 없다.  또한 query rewrite도 ORDER BY절에 의해 영향을 받지 않는다.
 
◎ 사용 예제
  SQL> create materialized view sales_ordered_date
    2  tablespace sales_ts
    3   -- enable query rewrite
    4  as
    5  select c.channel_desc, p.product_id, p.item_desc, s.customer_id, d.date_id, d.date_desc, s.units
    6    from channels c, products p, days2 d, sales s
    7   where c.channel_id = s.channel_id
    8     and p.product_id = s.product_id
    9     and d.date_id    = s.date_id
   10  order by d.date_id;  -- date_id 순으로 Sorting을 한다.
 
  SQL> alter materialized view sales_ordered_date enable query rewrite;
 
 
16. Materialized View를 Refresh 하는 방법
 
◎ Refresh를 해야하는 원인은 여러가지 이다. 예를 들어, Base Table 이 Truncate 되었고,
   Fast Refresh가 아니라면, 사용자가 수동으로 Refresh를 해야 한다.
 
 
◎ Refresh를 수행한 Time 정보는 SYS.SNAP$ and SYS.MLOG$ 에 저장이 된다.
 
 
DBMS_MVIEW.REFRESH() Package 사용
 
  ※ Refresh는 해당 MView가 어떤 속성을 갖느냐에 따라 다르다.
 
  SQL> -- 해당 MView를 Fast Refresh를 하는 방법
  SQL> dbms_mview.refresh('BOL_LEDGER_MV', 'F');
 
  SQL> -- 해당 MView를 COMPLETE Refresh를 하는 방법
  SQL> dbms_mview.refresh('BOL_LEDGER_MV', 'C');
 
  SQL> -- 해당 MView를 일반적으로 Refresh를 하는 방법
  SQL> dbms_mview.refresh('BOL_LEDGER_MV');
 
 
 
17. Materialized View와 관련된 시스템 딕셔너리
 
◎ 시스템 뷰
 
  ※ DBA_MVIEWS
     - Materialized View에 대한 전체적인 정보를 보여준다.
 
  ※ DBA_MVIEW_LOGS
     - Materialized View Log에 대한 전체적인 정보를 보여준다.
 
  ※ DBA_REGISTERED_SNAPSHOTS
     - Materialized View에 대한 시스템 정보를 보여준다.
     - 예를 들면, current_snapshots Column에는 마지막 Refresh 된 시간을 보여준다.
 
  ※ DBA_SNAPSHOT_LOGS
     - Materialized View Log에 대한 시스템 정보를 보여준다.
 
 
◎ 예제..
 
  ※ MASTER Table ORDERS에 대한 Materialized View d의 정보를 조회해본다.
  
    SQL> select log_owner, master, log_table
           from dba_snapshot_logs
          where master = 'ORDERS';
    
    LOG_OWNER             MASTER          LOG_TABLE
    --------------------  -------------   ---------------
    SCOTT                 ORDERS          MLOG$_ORDERS
    SCOTT                 ORDERS          MLOG$_ORDERS
 
 
  ※ MASTER Table ORDERS에 대한 Materialized View Location의 정보를 조회해본다.
 
    SQL> select owner, name, snapshot_site 
           from dba_registered_snapshots
               ,dba_snapshot_logs 
          where dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id 
            and dba_snapshot_logs.master='ORDERS';
    
    OWNER       NAME          SNAPSHOT_SITE
    ----------  ------------  ---------------
    SCOTT       ORDERS        V804.WORLD
    SCOTT       SNAP_ORDERS   NEGRIL.WORLD
   
  ※ MASTER Table FND_USER에 대한 가장 최근 Refresh Time을 조회해 본다.
 
    SQL> select r.name, r.snapshot_site
               ,to_char(l.current_snapshots,'YYYY-MM-DD, HH:MI:SS') as refresh_date
           from  dba_registered_snapshots  r
                ,dba_snapshot_logs          l 
          where r.snapshot_id = l.snapshot_id
            and l.master='FND_USER';

    NAME            SNAPSHOT_SITE       REFRESH_DATE
    --------------- ------------------- ---------------------
    FND_USER_MV     DEV                 2005-03-31, 10:13:28
 
 
18. 진단을 위한 DBMS_MVIEW.EXPLAIN_REWRITE / EXPLAIN_MVIEW 사용법 (9.0.2 이상)
 
◎ Oracle 9i 이상에서는 DBMS_MVIEW에서는 diagnosing Query Rewrite 문제를 해결하기 위해
   다음의 두가지 유용한 PL/SQL을 제공한다.:
 
  ※ EXPLAIN_REWRITE : Summary Materialized View를 사용할 때 왜 Query Rewrite가 안되는지 도움을 준다.
 
  ※ EXPLAIN_MVIEW   : Summary Materialized View가 Query Rewrite 기능이 가능한지 알려준다.
 
 
DBMS_MVIEW.EXPLAIN_REWRITE procedure
 
  ※ 이 procedure는 임의의 Query가 Rewite가 되는지 아닌지를 판단하는데 도움을 준다.
     즉, 이 procedure를 수행하면 결과로 Rewrite가 되는지 아닌지와, 안되면 왜 안되는지
     이유를 포함하는 Output을 발생하게 된다.
 
  ※ EXPLAIN_REWRITE을 수행하는데에는 두가지 방식이 있다.
     하나는 output을 REWRITE_TABLE 에 저장을 하는 방식과, 다른 하나는 VARRAY에 저장을
     하는 방식이다.
 
  ※ EXPLAIN_REWRITE Procedure의 구조는 다음과 같다.
 
     1) REWRITE_TABLE 을 사용하는 EXPLAIN_REWRITE Procedure
 
        PROCEDURE EXPLAIN_REWRITE (QUERY          IN       VARCHAR2,
                                   MV             IN       VARCHAR2 := NULL,
                                   STATEMENT_ID   IN       VARCHAR2 := NULL);
     ---
     2) VARRAY 를 사용하는 EXPLAIN_REWRITE Procedure
        PROCEDURE EXPLAIN_REWRITE (QUERY          IN       VARCHAR2,
                                   MV             IN       VARCHAR2 := NULL,
                                   MSG_ARRAY      IN OUT   SYS.RewriteArrayType);

  ※ EXPLAIN_REWRITE Procedure의 QUERY Parameter의 길이는 Max 32767 characters 이다.
 
  ※ DBMS_MVIEW.EXPLAIN_REWRITE 를 어떻게 사용하는지 예제는 다음의 File에 들어 있다.
    
     (9.2.0)$ORACLE_HOME/rdbms/demo/smxrw.sql
 
  ※ 예제 1) REWRITE_TABLE 을 사용하는 예제
     0) 준비사항으로 먼저 REWRITE_TABLE 을 만들어야 한다.
 
        SQL> (9.2.0)$ORACLE_HOME/rdbms/admin/utlxrw.sql -- 이 SQL을 수행하면 테이블이 생성된다.
 
     1) 다음과 같은 MV를 가정하자.
 
        create materialized view mvj 
        enable query rewrite as
        select dim1.dk1, dim2.dk2
          from fact, dim1, dim2
         where dim1.dk1 = fact.dk1
           and dim2.dk2 = fact.dk2;
 
     2) 그리고 원하는 Query가 다음과 같다고 가정하자.
 
        select dim1.dk1, dim2.dk2
        from fact, dim1, dim2, dim3
        where dim1.dk1 = fact.dk1
          and dim2.dk2 = fact.dk2
          and dim2.dk2 = 1;
 
     3) 이제 위 Query가 Query Rewrite가 되는지 아닌지 판단해 보자.
         
        SQL> truncate table rewrite_table;
        
        SQL> declare
           2   query varchar2(256) := 'select dim1.dk1, dim2.dk2
           3                             from fact, dim1, dim2, dim3
           4                            where dim1.dk1 = fact.dk1
           5                              and dim2.dk2 = fact.dk2
           6                              and dim2.dk2 = 1';
           7 begin
          8   dbms_mview.explain_rewrite(query);
           9 end;
        SQL> / 
         
        SQL> select message from rewrite_table order by sequence;
        
        ** Here is example output:
        MESSAGE
        -----------------------------------------------------------------
        QSM-01033: query rewritten with materialized view, MVJ
        
        ** Here is example output from another case where rewrite did not work: 
        MESSAGE
        ---------------------------------------------------------------------------
        QSM-01094: outer-join filter not found in materialized join view
        QSM-01105: no primary key or row id in MV, MVJO, is found for table, DIM1
         
         
  ※ 예제 2) VARRAY를 사용하는 예제
 
     1) 원하는 MV와 원하는 Query는 위와 동일하다고 가정하자
 
     2) 다음과 같은 Procedure를 만들자.
 
        $ vi test.sql

        set serveroutput on
        
        declare
          Rewrite_Array sys.rewriteArrayType := SYS.RewriteArrayType();
          querytxt varchar2(1000) :=
          'select dim1.dk1, dim2.dk2
           from fact, dim1, dim2, dim3
           where dim1.dk1 = fact.dk1
             and dim2.dk2 = fact.dk2
             and dim2.dk2 = 1';
          msg_no number;
          i      number;
        begin
          dbms_snapshot.explain_rewrite(querytxt, NULL, Rewrite_Array);
          msg_no := rewrite_array.count;
          for i in 1..msg_no
          loop
            dbms_output.put_line('MV Name: ' ||Rewrite_Array(i).mv_name);
            dbms_output.put_line('Query  : ' ||Rewrite_Array(i).query_text);
            dbms_output.put_line('Message: ' ||Rewrite_Array(i).message);
          end loop;
        end;
        /                    
         
        ** Here is output for our example query and MV:
        
        MV Name:
        Query  : select dim1.dk1, dim2.dk2    from fact, dim1, dim2, dim3    where
        dim1.dk1 = fact.dk1      and dim2.dk2 = fact.dk2      and dim2.dk2 = 1
        Message: QSM-01033: query rewritten with materialized view, MVJO1
         
     
DBMS_MVIEW.EXPLAIN_MVIEW procedure
 
  ※ 이 procedure는 임의의 Materialized View 를 분석하여 MV_CAPABILITIES_TABLE Table에
     분석 결과를 저장한다.
 
  ※ MV_CAPABILITIES_TABLE 테이블을 만들기 위해서는 다음을 수행하면 된다.
 
     SQL> (9.2.0)$ORACLE_HOME/rdbms/admin/utlxmv.sql 
 
  ※ 사용 방법
 
     1) 원하는 MV와 원하는 Query는 위와 동일하다고 가정하자
 
     2) 다음의 작업을 수행하라
 
        SQL> truncate table mv_capabilities_table; 
        SQL> execute dbms_mview.explain_mview('MVJ');
 
        SQL> select capability_name, possible from mv_capabilities_table
           2
where mvname='MVJ' and capability_name like 'REWRITE%';
 
        CAPABILITY_NAME                P
        ------------------------------ -
        REWRITE                        Y
        REWRITE_FULL_TEXT_MATCH        Y
        REWRITE_PARTIAL_TEXT_MATCH     Y
        REWRITE_GENERAL                Y
        REWRITE_PCT                    N 
  ※ 자세한 사용 예제는 다음의 File에 잘 나와 있다.
 
     (9.2.0)$ORACLE_HOME/rdbms/demo/smxmv1.sql
 
 
 
19. Nested Materialized Views
 
◎ Nested Materialized Views 란 MView안에 MView가 존재하는 형태이다.
   따라서 Nested Materialized Views는 매우 복잡한 형태를 지니게 된다.
 
 
◎ 하지만 Nested Materialized Views 의 장점에도 불구하고 Fast Refresh에 대한 문제가 존재한다.
 
 
◎ 다음은 Nested Materialized Views에서 Fast Refresh가 가능하게 하는 방법이다.
 
   a) Master Table에 대한 MView Log를 Rowid 사용해서 만든다.
   b) Nested MView는 single-table aggregate 와 join view View로 구성이 되어야 한다.
   c) single-table aggregate materialized view는 materialized join view 보다 먼저 와야 한다.
 
◎ 만약 Nested Materialized Views를 만들다 실패할 경우 다음의 에러가 발생한다.
 
   Error: ORA-12053  (ORA-12053)
   Text:  This is not a valid nested materialized view  

◎ Nested Materialized Views의 사용 예제 
 
  ※ 다음의 Table을 고려해 보자
     SQL> desc emp
     Name                                      Null?    Type
     ----------------------------------------- -------- --------------------------
     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)
 
     SQL> desc dept
     Name                                      Null?    Type
     ----------------------------------------- -------- --------------------------
     DEPTNO                                    NOT NULL NUMBER(2)
     DNAME                                              VARCHAR2(14)
     LOC                                                VARCHAR2(13)
 
  ※ 위 두 테이블을 이용해서 MView를 만들어 보자
     
     SQL> create materialized view log on emp with primary key, rowid;
 
     SQL> create materialized view log on dept with primary key, rowid;
 
     SQL> -- create the first MV as join MV, Rowid columns were added to enable 
     SQL> -- fast refresh on a join view... 
     SQL> create materialized view empmv1
          refresh fast on demand
          with primary key
          as
          select e.empno,d.deptno,e.ename,d.dname, e.rowid erowid,d.rowid drowid
          from emp e, dept d
          where e.deptno = d.deptno ;
 
     SQL> alter table empmv1 add primary key(empno);
 
     SQL> -- create materialized view log for the empmv1: 
     sql> create materialized view log on empmv1 with primary key,
          rowid(deptno) including new values;
 
 
     SQL> -- now create the nested materialized view empmv2:
     SQL> 
create materialized view empmv2
           refresh fast on demand
           with primary key
           as select empno, deptno  
           from empmv1 ;
-- MView에서 가져온다.
    
     from empmv1
           *
     ERROR at line 5:
     ORA-12053: this is not a valid nested materialized view

     SQL> -- we are missing one rule here , empmv2 has to be a single-table aggregate.. 
     SQL> create materialized view empmv2
          refresh fast on demand
          with primary key
          as select empno,deptno , count(*)   -- Unique 성을 보장하기 위해
          from empmv1
          group by empno,deptno ;
 
◎ Oracle 9.2 부터는 single-table aggregates 와 join views 를 같이 사용할 수 있게 되었다
 
  ※ 사용 예제 (아래의 예제는 Oracle 9.2 이하에서는 ora-12053에러가 발생한다.)
 
      SQL> -- create demo tables in Scott schema in 9.2 database and run the following ..  
      SQL> alter table emp add primary key(empno);
       
      SQL> alter table dept add primary key(deptno); 
       
      SQL> create materialized view log on dept with primary key,
           rowid(dname) including new values;
 
       
      SQL> create materialized view log on emp with primary key,
           rowid(deptno, sal) including new values;
       
      SQL>
create materialized view deptmv1
           refresh fast on demand
           with primary key
           as select empno,deptno , count(sal) cnt_sal  from emp
           group by empno,deptno ;
       
      SQL> alter table deptmv1 add primary key(empno); 
       
      SQL> create materialized view log on deptmv1 with primary key,
           rowid(deptno, cnt_sal) including new values ;
       
      SQL> 
create materialized view deptmv2
            refresh fast on demand
            with primary key
            as
            select empno,cnt_sal,dname,e.rowid erowid,d.rowid drowid
              from deptmv1 e   -- Mview가 먼저와야 한다.
                  ,dept d  
             where e.deptno = d.deptno ;
       
◎ Nested Materialized Views 를 사용할 때의 제약사항
  
   1. Nested materialized views 를 Refresh하고자 한다면 순서적으로 Refresh 되어야 한다.
      예를 들어, 먼저 Empmv1을 수행하고 다음에 Empmv2을 수행해야 한다.
   2. ON COMMIT에 대한 Fast refresh는 single-table aggregates 와 join views 를
      같이 사용할 경우 지원되지 않는다.
Posted by is윤군
TAG Oracle
제5 외국어 영역2007.12.12 11:39

오늘은 2007년11월8일....

입동..(겨울의 시작..)


근디 낮온도는 20도란다..

무신놈의 겨울 온도가 20도나 되는지..헐..~~


오라클의 SYS_CONNECT_BY_PATH 함수를 잘 사용하면

자기참조 형태의 테이블을 아주 이쁘게 표현 할 수 있다.


예를 들어..


Create Table TEST

(

  KEY_ID VARCHAR(10) NULL,

  UP_KEY_ID VARCHAR(10) NULL,

  KEY_NAME VARCHAR(10) NULL

)

이라는 테이블에 아래와 같은 데이터가 들어있다고가정한다면..


KEY_ID, UP_KEY_ID, KEY_NAME

1, 0, 첫번째상위

2, 1, 두번째상위

3, 2, 세번째상위

4, 1, 두번째중간상위


요걸 이쁘게(?) 추출하는 방법으로 SYS_CONNECT_BY_PATH를 쓴다.

명확히 이야기 하믄 전체 full path를 표현하는 것이겠지..


ELECT SYS_CONNECT_BY_PATH (KEY_NAME, '=>') AS full_name
FROM  TEST
START WITH KEY_ID is NOT NULL

CONNECT BY PRIOR KEY_ID = UP_KEY_ID


그럼 데이터는 아마도 이렇게 나올것이다.


=> 첫번째상위

=> 첫번째상위=>두번째상위

=> 첫번째상위=>두번째상위=>세번째상위

=> 첫번째상위=>두번째중간상위


대충 이해는 되셨겟지롱...^^


생각보다 많이 쓰는 함수이고, Tree Node 구현시 꼭 필요한 함수이니..

자주자주 쳐다보시길.. ^^


2007.11 About.K

Posted by is윤군
TAG Oracle
제5 외국어 영역2007.12.12 11:03

제목

오라클 고급쿼리(1) - CONNECT BY

작성자

DEV.WON ( http://blog.naver.com/jadin1 )

작성일

2007-05-31

배포

PUBLIC (, 출처는 밝혀 주세요)



편의상  짧은 어투 양해바라며...



후... 강좌 올만에 올리는군요...그동안 블로그 관리에 소홀히 했는데 이제부턴 열심히 해야겠습니다.





오라클에서 제공하는 쿼리중 좀더 고급스러운 쿼리를 사용해 보도록하자.


처음으로 살펴볼 쿼리는 이른바 계층적 쿼리이다.


CONNECT BY 절을 사용하는데 이 계층적 쿼리는 오라클만이 가능 기능으로써,


데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.


scott의 emp 테이블을 기억하는가?


그 사원테이블에는 사원번호 와 함께 사원의 메니저의 번호(fk) 를 참조하는 외부키 컬럼이 있다.


바로 self join을 사용하는 가장 흔한 예라 하겠다.


필자는 emp테이블로 설명을 하려 했으나, 역시나 지겨운 관계로 새로 테이블을 만들었다.


쇼핑몰이나 기타 솔루션에서 흔히 보게 되는 카테고리 정보 테이블을 예로 들면서 강좌를 진행하도록 하겠다.




우선 DB 데이블의 schema부터 보도록하자.

1. 카테고리 정보 테이블(PRT_CATE)


DDL

-- Create table
create table PRT_CATE
(
  NO            NUMBER not null,
  CATE_NAME     VARCHAR2(100) not null,
  BASE_CATE_NO  NUMBER,
  CATE_MEMO     VARCHAR2(500),
  CATE_VIEW_CNT NUMBER default 0
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column PRT_CATE.NO
  is 'pk';
comment on column PRT_CATE.BASE_CATE_NO
  is '상위 카테고리 의 FK';
comment on column PRT_CATE.CATE_MEMO
  is '카테고리의 설명.';
comment on column PRT_CATE.CATE_VIEW_CNT
  is '카테고리 열람 횟수';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT_CATE
  add constraint PRT_CATE_PK primary key (NO)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );


 








2. 상품 테이블(PRT)

-- Create table
create table PRT
(
  NO          NUMBER not null,
  PRT_NAME    VARCHAR2(100) not null,
  PRT_PRICE   NUMBER not null,
  PRT_CATE_NO NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column PRT.NO
  is 'PK';
comment on column PRT.PRT_NAME
  is 'NAME';
comment on column PRT.PRT_PRICE
  is '가격';
comment on column PRT.PRT_CATE_NO
  is '상품이 속한 카테고리 FK';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT
  add constraint PRT_PK primary key (NO)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );









3. R-R





역시나 필자의 귀차니즘에 의한 엄청나게 허술한 그림이 돋보인다..(예전의 열정은 사라졌다. ㅎ)




위의 schema를 쭉 본 독자들이라면 거의 모두다 파악이 될것이다.


주목할 점은 바로 저, self join을 해야할 PRT_CATE테이블이다.


자 독자라면 여기서 카테고리의 계층 구조를 쿼리로 나타내어 보라.

(8번PK의 DB카테고리의 계층구조는 컴퓨터 > 소프트웨어 > DB 이다.)


우선 필자는 이렇게 처리하였다.


---------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CATE_LISTING
       (p_cateName VARCHAR2, p_level NUMBER DEFAULT
0)
AS
BEGIN
    
--PRINT
     DBMS_OUTPUT.put_line(lpad(
' ',p_level * 3,' ') || p_cateName);

     FOR CATE_ROW IN         
             (
             SELECT * FROM PRT_CATE C
             WHERE C.BASE_CATE_NO IN (
                                       SELECT C2.NO FROM PRT_CATE C2
                                       WHERE C2.CATE_NAME = p_cateName        
                                     ) 
             ORDER BY C.NO
             )
         LOOP
      
      
-- PROCEDURE CALL   
       CATE_LISTING(CATE_ROW.CATE_NAME,p_level +
1);
    
     END LOOP;   

END ;      


-- EXECUTE
CALL CATE_LISTING(
'컴퓨터',0);

---------------------------------------------------------------------------------------------


결과>

--------------

컴퓨터
   소프트웨어
      DB
      Tools
   주변제품
---------------


재귀 프로시져 기법으로 처리하였다.


커서등을 사용하는것도 하나의 방법이 될수 있겠다.








하지만, 너무 복잡하지않은가?


자 이쯤에서 오라클이 자랑하는(?) 기능중 계층적 쿼리를 사용해 보도록하자.


필자는 똑같은 결과를 이렇게 바꾸었다.


---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

---------------------------------------------------------------------------------------------





결과>




오, 놀랍지않은가? 엄청나게 코드가 줄었다.(그렇다면 퍼포먼스는?? 오라클 도큐먼트를 살펴보길 바란다.)


우선 START WITH 절부터 보도록 하자.


SELECT 구문의 START WITH 절은 계층 구조가 어떤 행으로부터 시작하는지 지정하는 절이다.


일종의 WHERE절이라 생각해도 좋다.


      START WITH <조건>




필자는 예제에서

START WITH C.CATE_NAME = '컴퓨터'

라고 작성하였다.


바로 카테고리 이름이 '컴퓨터' 인 레코드부터 계층적 쿼리를 수행하라는 의미이다.


START WITH절은  기본적으로 WHERE절에서 사용가능한 sql을 모두 사용할수 있다.


이를 테면

---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
--START WITH C.CATE_NAME = '컴퓨터'
START WITH C.CATE_NAME IN
      (SELECT CATE_NAME FROM PRT_CATE WHERE CATE_NAME =
'컴퓨터' OR CATE_NAME = '가전제품')     
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

와 같이 바꿀수도 있다.

 

이때 주의할점은 아래의 결과값과 같이 OR 연산 에 의해 두개의 레코드로 부터 계층쿼리가 시작이 되므로

 

결과값도 아래와 같이 두세트로 나온다는것에 주의하기 바란다.

(사실 이러한 경우때문에 가능하면 명료하고 구체적인 , 결과 레코드가 적은 조건을 사용하여야 쿼리 퍼포먼스가 향상된다. )

 

 

결과값>



다음은


CONNECT BY PRIOR 절을 살펴보도록하자.




CONNECT BY 절은 각각의 행들이 어떻게 연결되어야하는지 (여기서는 JOIN을 뜻한다.) 정보를 작성한다.


무슨말이냐하면, 계층적 구조 에서 각 행의 연결 관계를 설정하는것인데...


사원테이블의 사원번호와 , 사원테이블의 사수번호가 여기에 해당된다 하겠다.




그렇다면 어떤 컬럼과 어떤 컬럼이 상위이고 하위인지 이러한 계층정보는 어떻게 설정하는 것일까?


바로 이때 사용하는 키워드가 PRIOR 이다.



필자가 작성한 예제를 보면

CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

와 같이 작성하였는데..

 

우선 PRIOR 키워드를 빼고 생각해보자.

 

CONNECT BY

     C.NO = C.BASE_CATE_NO;

 

이와 같이 되는데 이때 NO 컬럼과 BASE_CATE_NO이 계층적 연결 관계에 있다라고 설정하는것이다

(SELF JOIN이라 생각해도 좋다.)

 

이제 PRIOR를 생각해보자.

CONNECT BY

     PRIOR C.NO = C.BASE_CATE_NO;

 

 

 

 

OUTER JOIN을 생각해보라.

C.NO (+) = C.BASE_CATE_NO; 와 같이, PRIOR 키워드의 위치에 집중하자.

 

 

바로 C.NO 쪽에 위치하고있다.!

 

 

 

 

그렇다면 어느쪽 컬럼에 PRIOR가 위치한다는것은 무엇을 의미할까?

 

이것을 말로 설명하기가 매우 어렵다. 말주변의 바닥... ㅜㅜ;;

 

"NO컬럼을 참조하는 BASE_CATE_NO 컬럼이 속한 레코드를 모두 찾아라" 정도로 이해하면 될듯싶다.

 

 

이렇게 쿼리를 작성하였기때문에 상위 '컴퓨터' 카테고리에 해당하는 하위 카테고리를 찾았고, 또 그 하위 카테고리의

 

하위 카테고리를 찾을 수 있게 된것이다.

(PRIOR 키워드의 위치를 바꾸어서 실습해보면 이해에 도움이 된다.)

 

 

 

 

자 이쯤에서 다음으로 넘어가자.

 

 

 

 

 

 

 

 

알면 유용한 키워드 LEVEL !

 

필자는 위의 예제에서 LEVEL 키워드 사용예를 위해 LEVEL키워드를 추가하였다.

 

--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

 

 

결과값>





눈치빠른 독자라면 LEVEL키워드의 쓰임을 알것이다.


LEVEL은 오라클에서 실행되는 모든 쿼리내에서 ROWNUM과 더불어 가상-컬럼 이라 할수 있는데,


계층적 쿼리 트리내에서 어느한 위치,또는 단계(LEVEL)에 위치하는가를 나타내는 정수값 컬럼이다.



그렇다면 당근, 계층적 쿼리가 아닌 일반 쿼리에서 LEVEL컬럼의 값이 모두 0 으로 나올것이다.




자, LEVEL키워드를 어디에 사용할까??


예를들면 2단계까지의 카테고리 정보만을 출력하라 라는 식의 요구사항일때 유용하다.


예상되는 쿼리는


WHERE LEVEL < 3 일것같지만 사실 LEVEL키워드는 CONNECT BY절에 위치하여야한다.



--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO AND LEVEL <
3;

--------------------------------------------------------------------------------

 

 


결과값>


이로써 오라클이 제공하는 고급쿼리중 CONNECT BY 절에 대해 마칠까 한다.



부족한 강좌 끝까지 읽어주셔서 감사합니다.

Posted by is윤군
TAG Oracle
제5 외국어 영역2007.11.21 17:42

펌 : http://cafe.naver.com/jspworld.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=31
Statement vs. PreparedStatement


PreparedStatement를 사용하는 경우 분포도
이 부분은 preparedStatement를 사용하는 경우 값의 분포도에 따라 오히려 낮은 성능을 보이는 경우로 자바서비스넷(www.javaservice.net)에서 김주현씨가 답변한 내용을 일부 수정한 것으로 상당히 이해가 쉽게 정리가 되어 있습니다.


 BIND 변수를 쓸 경우 실행계획이 틀어져서 느려지는 그런 경우를 unsafe literal 이라고 합니다.  예를 들어 다음과 같은 쿼리 문이 있다고 하면  select * from emp where empno = :v1  위의 SQL에서 empno 는 emp의 PK입니다. 따라서 preparedStatement를 써서 BIND 변수를 사용하든지 그렇지 않든지 실행계획은 똑같이 나옵니다. 


이런 경우를 safe literal 이라고 합니다.  하지만 쿼리 문이 다음과 같다고 하면  select * from emp where deptno > :v1  위의 경우 :v1이 1 일 경우와 1000일 경우는 분포도가 전혀 다릅니다. 1일 경우에는 대부분의 deptno가 1보다는 크기 때문에 분포도가 넓습니다. 즉, 인덱스를 타지 않는게 더 좋습니다. 하지만 deptno가 1000보다 큰 경우는 거의 없습니다.


즉, 분포도가 좁고, 이 경우 인덱스를 타야 합니다.  그러니까 히스토그램이라는 것은 이처럼 실제 :v1 값이 무엇이냐에 따라서 분포도가 달라지는 분포도 정보입니다. 어느 value가 어느 정도의 분포도를 가지고 있는지 하는 정보를 히스토그램이라고 합니다.  문제는 BIND 변수를 쓰면 :v1 이 1이 들어올지 1000이 들어올지에 무관하게 무조건 단 하나의 실행계획만을 만들고 일괄적으로 적용하다가 보니 1000이 들어오는데도 불구하고 인덱스를 타지 않고 Full Scan하는 상황이 나올 수 있습니다.  즉, BIND 변수를 활용하면 히스토그램 정보를 활용할 수 없습니다. 


그렇다고 prepatedStatement를 쓰지 말아야 할까요? 아닙니다. 써야 합니다. 위와 같이 불충분한 통계정보(히스토그램)이 없어서 실행계획을 잘못 수립해서 느려지는 경우는 어쩔 수 없는 현재의 옵티마이져의 한계로 보아야 합니다. 이 경우 똑똑한 사람이 힌트 등을 통해서 SQL에게 올바른 실행계획을 가르쳐줘야 합니다.  아주 특수한 경우 위와 같은 이유로 튜닝을 위해 Statement를 쓰는 경우도 있습니다.


예를 들어 성별이 남, 여 두 가지일 경우, 그리고 남자가 전체의 2%이며 아주 극소수라고 가정하면 남자일 경우에는 인덱스를 타야 하고, 여자일 경우 인덱스를 타지 말아야 합니다. 이 경우 어차피 distinct 값의 종류가 딱 2가지 이므로 BIND 변수를 쓰지 않아서 하드 파싱을 해봤자 2개의 SQL 종류 밖에는 나오지 않으므로 문제가 없습니다.  select * from user where sex = 'm' select * from user where sex = 'f'  오라클의 Shared pool의 Library cache에는 위와 같이 딱 두 종류의 SQL이 저장되어있어서 재사용되겠지요.  



Statement vs. PreparedStatement의 성능 차이 


이 부분은 자바서비스넷(www.javaservice.net) 에서 statement와 preparedstatement 중 어떤 것이 나은지 논쟁 중에 박영록(poci)씨가 “static sql과 dynamic sql의 성능 차이”라는 제목으로 preparedstatement를 사용하는 경우 발생할 수 있는 문제에 대해 정리한 내용을 일부 수정한 것으로 소모적인 논쟁 중에서 빛을 발하는 내용입니다.


 prepared statement에서는 보통 변수를 설정하고 바인딩하는 static sql이 사용되고 statement에서는 쿼리 자체에 조건이 들어가는 dynamic sql이 사용됩니다. (주 - 여기서 언급하고 있는 static/dynamic은 일반적으로 얘기하는 동적 sql과는 다른 의미로, 실행계획이 매번 새로 생성되는지 아닌지에 관한 것입니다.)  preparedStatement가 파싱 타임을 줄여주는 것은 분명히 중요한 장점이지만, static sql을 사용하는데 따르는 퍼포먼스 저하를 생각하지 않을 수가 없습니다.  이를테면 이런 예가 있을 수 있습니다.  dynamic sql을 사용하는 경우 자바에서 다음과 같은 SQL을 생성했다고 가정해봅시다. 


SELECT ... FROM   TAB1 WHERE  COL1 LIKE '%' AND    COL2 LIKE '%' AND    COL3 LIKE 'ABC%'  그리고 인덱스가 COL1 + COL2 + COL3 로 걸려 있습니다.  그렇다면 실행계획은 당연히 인덱스에서 COL3 컬럼만을 액세스하게 설정될 것입니다.  그런데 다음과 같은 static sql을 봅시다.  SELECT ... FROM   TAB1 WHERE  COL1 LIKE :A || '%' AND    COL2 LIKE :B || '%' AND    COL3 LIKE :C || '%'  이런 식으로 SQL을 작성했다고 합시다.


A, B, C에 어떤 값이 들어올지 모르니까 인덱스의 COL1, COL2, COL3 컬럼을 차례대로 스캔하도록 처리 경로가 잡힙니다. 그런데 조건으로 A, B는 NULL, C에는 'ABC'라는 값이 들어왔습니다. COL3 컬럼만 랜덤으로 액세스해서 테이블로 가면 될 것을 인덱스 풀스캔을 해버립니다.  이와 같이 static sql은 변수에 어떤 값이 바인딩 되는지를 알 수 없기 때문에 가장 일반적인 형태로 실행 계획을 작성합니다. 때문에 통계 자료를 제대로 활용할 수 없고 인덱스 활용 등에서 비효율이 발생할 확률이 높아집니다.  이런 예는 정말로 많습니다. 이를테면 TABLE1의 COL1의 분포도가 값이 'A'인 게 100건, 'B'인 게 100만 건 있다고 할 경우, COL1 = :VAR 와 같이 바인딩을 시켜놓으면 늘 풀스캔을 하게 실행계획을 잡습니다.


그런데 :VAR에 'A'가 들어가는 경우 인덱스를 타면 금방 결과가 나올 것을 풀스캔 후에 결과를 리턴하게 되죠.  static sql의 활용으로 파싱 타임을 절약해서 매번 실행 계획을 작성하는 것을 피하는 대가로 좀더 비효율적인 실행 계획이 수립된다는 것이죠. 일반적으로 SQL의 실행과정에서 파싱과 실행 계획 수립 과정은 전체 SQL 수행 시간에서 큰 비중을 차지하지 않습니다. 가장 큰 비중으로 차지하는 것은 테이블에서 로우를 가져오는 과정이고 파싱 시간은 이의 10분의 1에 불과합니다. 그런데, 이 10%의 시간을 줄이자고 90%의 시간에서 비효율이 발생하게 만드는 일이 생길 수 있습니다. 


물론 쿼리 생성 단계에서부터 어느 정도는 해결할 수 있는 문제입니다만, 그로 인해 자바 코드와 SQL이 복잡하게 얽히는 문제도 결코 작은 문제가 아니죠.  얼마전 대용량 데이터베이스 I, II를 모두 수강했습니다. 강사분께서 계속 강조하는 말씀이 두 가지가 있었습니다. 첫째는 원리를 이해하고 이론적으로 따져보는 것이 중요하다는 것, 그리고 둘째는 이론적으로 따져서 작성한 SQL에 대해 반드시 실행 계획을 떠보라는 것이었습니다. 상반되는 이야기인 것처럼도 보이고 일반적인 이론과 경험의 조화를 말하는 것처럼도 보이지만 저에게는 이론적인 체계를 먼저 잡아나가고 원리를 따져본 다음에 그 이론을 검증해보라는 말로 들렸습니다.  


Statement vs. PreparedStatement의 JDBC 드라이버별 속도 차이
이 부분은 O’Reilly 출판사에서 나온 “Java Programming with Oracle JDBC” 란 책의 19장 Performance 에서 statement와 preparedStatement를 JDBC 드라이버별로 속도를 비교한 자료이다. 결론적으로 실 프로젝트에서 여기 결과를 따라 프로그래밍을 할 수는 없겠지만 개념적으로라도 알고 있어야 할 내용이다.
 There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.   When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.  The OCI Driver Table 19-3 shows the timings in milliseconds for 1 insert and 1,000 inserts in the TESTXXXPERF table. The inserts are done first using a Statement object and then a PreparedStatement object. If you look at the results for 1,000 inserts, you may think that a prepared statement performs better. After all, at 1,000 inserts, the PreparedStatement object is almost twice as fast as the Statement object, but if you examine Figure 19-1, you'll see a different story.  Table 19-3: OCI driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,804 1,412   Figure 19-1 is a graph of the timings needed to insert varying numbers of rows using both a Statement object and a PreparedStatement object. The number of inserts begins at 1 and climbs in intervals of 10 up to a maximum of 150 inserts. For this graph and for those that follow, the lines themselves are polynomial trend lines with a factor of 2. I chose polynomial lines instead of straight trend lines so you can better see a change in the performance as the number of inserts increases. I chose a factor of 2 so the lines have only one curve in them. The important thing to notice about the graph is that it's not until about 65 inserts that the PreparedStatement object outperforms the Statement object. 65 inserts! Clearly, the Statement object is more efficient under typical use when using the OCI driver.   Figure 19-1. OCI driver timings      The Thin Driver If you examine Table 19-4 (which shows the same timings as for Table 19-3, but for the Thin driver) and Figure 19-2 (which shows the data incrementally), you'll see that the Thin driver follows the same behavior as the OCI driver. However, since the Statement object starts out performing better than the PreparedStatement object, it takes about 125 inserts for the PreparedStatement to outperform Statement.  Table 19-4: Thin driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,583 1,739    Figure 19-2. Thin driver timings      When you consider typical SQL statement usage, even with the Thin driver, you'll get better performance if you execute your SQL statements using a Statement object instead of a PreparedStatement object. Given that, you may ask: why use a PreparedStatement at all? It turns out that there are some reasons why you might use a PreparedStatement object to execute SQL statements. First, there are several types of operations that you simply can't perform without a PreparedStatement object. For example, you must use a PreparedStatement object if you want to use large objects like BLOBs or CLOBs or if you wish to use object SQL. Essentially, you trade some loss of performance for the added functionality of using these object technologies. A second reason to use a PreparedStatement is its support for batching.  Batching As you saw in the previous section, PreparedStatement objects eventually become more efficient than their Statement counterparts after 65-125 executions of the same statement. If you're going to execute a given SQL statement a large number of times, it makes sense from a performance standpoint to use a PreparedStatement object. But if you're really going to do that many executions of a statement, or perhaps more than 50, you should consider batching. Batching is more efficient because it sends multiple SQL statements to the server at one time. Although JDBC defines batching capability for Statement objects, Oracle supports batching only when Prepared-Statement objects are used. This makes some sense. A SQL statement in a PreparedStatement object is parsed once and can be reused many times. This naturally lends itself to batching.   The OCI Driver Table 19-5 lists Statement and batched PreparedStatement timings, in milliseconds, for 1 insert and for 1,000 inserts. At the low end, one insert, you take a small performance hit for supporting batching. At the high end, 1,000 inserts, you've gained 75% throughput.  Table 19-5: OCI driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,804 691   If you examine Figure 19-3, a trend line analysis of the Statement object versus the batched PreparedStatement object, you'll see that this time, the batched Prepared-Statement object becomes more efficient than the Statement object at about 50 inserts. This is an improvement over the prepared statement without batching.   Figure 19-3. OCI driver timings for batched SQL      WARNING:  There's a catch here. The 8.1.6 OCI driver has a defect by which it does not support standard Java batching, so the numbers reported here were derived using Oracle's proprietary batching.  Now, let's take a look at batching in conjunction with the Thin driver.  The Thin Driver The Thin driver is even more efficient than the OCI driver when it comes to using batched prepared statements. Table 19-6 shows the timings for the Thin driver using a Statement object versus a batched PreparedStatement object in milliseconds for the specified number of inserts.  Table 19-6: Thin driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,583 367   The Thin driver takes the same performance hit on the low end, one insert, but gains a whopping 86% improvement on the high end. Yes, 1,000 inserts in less than a second! If you examine Figure 19-4, you'll see that with the Thin driver, the use of a batched PreparedStatement object becomes more efficient than a Statement object more quickly than with the OCI driver--at about 40 inserts.  Figure 19-4. Thin driver timings for batched SQL      If you intend to perform many iterations of the same SQL statement against a database, you should consider batching with a PreparedStatement object.   

Posted by is윤군
TAG Oracle