다물8

Oracle Technical Bulletins No. 10185 참고..

중복된 RECORD 삭제 방법
================================

중복된 RECORD를 삭제하는 방법은 2가지가 있습니다.


방법1)

SQL> CREATE TABLE emp2 AS SELECT distinct * FROM emp;

SQL> DROP TABLE emp;

SQL> RENAME emp2 TO emp;




방법2) 중복된 데이터중에서 ROWID가 큰 값을 제거


SQL> DELETE FROM emp a
WHERE rowid > (SELECT MIN(ROWID)
FROM emp b
WHERE b.empno = a.empno);



en-core에서 본 白面書生(www.okjsp.pe.kr)님의 글도 참고해서 넣었습니다.

방법3) 나중에 들어온 데이터를 살릴경우


SQL> DELETE FROM emp a
WHERE ROWID < (SELECT MAX(ROWID)
FROM emp b
WHERE a.empno = b.empno);

방법4) 레코드가 완전 중복이 아니고 일부 중복인 데이터를 삭제할 경우


SQL>
DELETE /*+ FULL(A) PARALLEL(A, 7)  */
FROM TB_TEST PARTITION(P201407) A
WHERE ROWID IN ( SELECT RID
                            FROM ( SELECT ROWID AS RID, AREA_NO, KUK_NO, SUB_NO,
                                                     RANK() OVER (PARTITION BY AREA_NO, KUK_NO, SUB_NO ORDER BY START_DATE DESC) AS R_NO
                                        FROM TB_TEST PARTITION(P201407) B
                                        WHERE END_DATE = '99991231'
                                        AND     START_DATE < '20140701'
                                       )
                            WHERE R_NO <> 1

                           )
;

설명: 테이블 TB_TEST에서 AREA_NO, KUK_NO, SUB_NO, END_DATE 가 동일하고
        START_DATE가 7월이전인 자료 중에서 START_DATE가 OLD인 자료를 삭제한다.

       즉, AREA_NO, KUK_NO, SUB_NO, END_DATE는 같으나 START_DATE가 다른 자료를 삭제하되   최그것을 살리고 이전것을 삭제하는 경우입니다.

select  rawtohex(comp_cd) as rowhex, replace(comp_cd,chr(00),'♨')||'abc'
from    tb_imsi a

언제 써먹게 될지 모르지만 정리해봅니다.

관련지식 : LTRIM, START WITH CONNECT BY, SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT

10g이상에서는 바로 CONNECT_BY_ROOT로 구할 수 있는가 봅니다.

9i에서 사용시 참고하면 될듯합니다.

start with절에 지정한 필드가 계층적으로 펼쳐지는 것이므로 여기서 해당 필드 사이즈 만큼 substring하면 됩니다.

ltrim은 첫문자인 '/' 을 없애주는 기능

/* SYS_CONNECT_BY_PATH 를 이용하여 자른다.. ^^&--^^ */
SELECT  LEVEL,
             SUBSTR(LTRIM(SYS_CONNECT_BY_PATH(HOLIDAY_START_DATE, '/'),'/'),1,8)  AS ROOT_ID , --holiday_start_date필드길이만큼 자른다. 1,8
             LTRIM(SYS_CONNECT_BY_PATH(HOLIDAY_START_DATE, '/'),'/')  AS ROOT_PATH , --전체 계층구조를 보여준다.
             HOLIDAY_START_DATE, NEXT_DATE
FROM      (
                SELECT HOLIDAY_START_DATE,
                        TO_CHAR(TO_DATE(HOLIDAY_START_DATE,'YYYYMMDD')+1,'YYYYMMDD') AS NEXT_DATE
                FROM   TB_BUPAWEEKDAY
                WHERE HOLIDAY_START_DATE LIKE '2013%'
                )
START WITH HOLIDAY_START_DATE  LIKE '201309%'  
CONNECT BY PRIOR   NEXT_DATE = HOLIDAY_START_DATE ;

LEVEL ROOT_ID ROOT_PATH HOLIDAY_START_DATE NEXT_DATE
1 20130901 20130901 20130901 20130902 
1 20130907 20130907 20130907 20130908 
2 20130907 20130907/20130908 20130908 20130909 
1 20130908 20130908 20130908 20130909 
1 20130914 20130914 20130914 20130915 
2 20130914 20130914/20130915 20130915 20130916 
1 20130915 20130915 20130915 20130916 
1 20130918 20130918 20130918 20130919 
2 20130918 20130918/20130919 20130919 20130920 
3 20130918 20130918/20130919/20130920 20130920 20130921 
4 20130918 20130918/20130919/20130920/20130921 20130921 20130922 
5 20130918 20130918/20130919/20130920/20130921/20130922 20130922 20130923 
1 20130919 20130919 20130919 20130920 
2 20130919 20130919/20130920 20130920 20130921 
3 20130919 20130919/20130920/20130921 20130921 20130922 
4 20130919 20130919/20130920/20130921/20130922 20130922 20130923 
1 20130920 20130920 20130920 20130921 
2 20130920 20130920/20130921 20130921 20130922 
3 20130920 20130920/20130921/20130922 20130922 20130923 
1 20130921 20130921 20130921 20130922 
2 20130921 20130921/20130922 20130922 20130923 
1 20130922 20130922 20130922 20130923 
1 20130928 20130928 20130928 20130929 
2 20130928 20130928/20130929 20130929 20130930 
1 20130929 20130929 20130929 20130930 

참고한 URL:

1. http://www.oracleclub.com/lecture/2223

2. http://www.oracleclub.com/lecture/2250

3. http://blog.naver.com/swordsman93?Redirect=Log&logNo=33405532

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
   FROM DUAL;

LTRIM example
------------
XxyLAST WORD

xy패턴의 문자열을 차례로 왼쪽부터 찾아서 하나씩 지우게 되므로

첫번째 문자 x를 지우고 다음 y를 지우고 그 다음 x를 지우고 그 다음 y를 찾았으나

없으므로 그대로 리턴한다.  얼핏보면 'xXxyLAST WORD' 이 될듯 하지만 처리방식이 문자 하나씩 찾는 방식이다.

다음예를 보자...

SELECT LTRIM('abcabcabkabcABxcXabAbabAAbb','abc') as aa
from dual

AA
kabcABxcXabAbabAAbb 

LTRIM

Syntax

ltrim::=

Text description of ltrim

Purpose

LTRIM removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. If char is a character literal, then you must enclose it in single quotes. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Both char and set can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.

Examples

The following example trims all of the left-most x's and y's from a string:

SELECT LTRIM('xyxXxyLAST WORD','xy') "LTRIM example"
   FROM DUAL;

LTRIM example
------------
XxyLAST WORD

cd C:\app\owner\diag\rdbms\orcl\orcl\trace
sqlplus scott/oracle
set linesize 132
set timing on
set pages 3000
alter session set tracefile_identifier='test28';
alter session set sql_trace=true;

select a.empno, a.ename, b.dname
from big_emp1 a, big_dept1 b
where a.deptno = b.deptno;

alter session set sql_trace=false;

C:\app\owner\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_1220_test28.trc test28.txt sys=no

------------------------------
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

desc dba_tablespaces;
select tablespace_name, retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

create materialized view emp_sumsal
build immediate refresh complete on commit
enable query rewrite
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

select * from all_objects where object_name = 'EMP_SUMSAL';

drop materialized view emp_sumsal;

create materialized view emp_sumsal
build deferred  -- 추후 data  생성
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

--? 알아서 data 가져온다.
SQL> exec dbms_mview.refresh('EMP_SUMSAL','?');

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

SQL> select * from emp_sumsal;

   SUM_SAL     DEPTNO
---------- ----------
      9400         30
     10075         20
      8750         10

- C : COMPLETE
CREATE 명령의 모든 QUERY 정의를 재실행하여 기졲 데이터를 잘라내는 작업과 마스터 테이블을 기반으로 하는 모든 데이터를 재구축하는 작업이 포함
- F : FAST
마지막 REFRESH 이후의 변경 사항을 적용하며 젂체 재구축을 수행하지는 않음
MIVEW LOG를 사용한 REFRESH : 마스터 테이블의 모든 변경사항이 로그에 기록된 후 MVIEW에 적용
ROWID 범위를 이용한 REFRESH : 싞규 행의 ROWID를 이용하여 DIRECT PATH LOAD 후 MVIEW에 적용
- ? : FORCE
COMPLETE + FAST

EXEC DBMS_MVIEW.REFRESH('EMP_SUMSAL','?');

CREATE MATERIALIZED VIEW dept_sal
BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
REFRESH COMPLETE -- FORCE, COMPLETE, ?
ON COMMIT -- ON DEMAND, ON COMMIT 선택.
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

INSERT INTO EMP
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
VALUES
( 7935, 'KIM', 'MANAGER', 7839,
TO_Date( '12/17/1980 12:00:00 오전', 'MM/DD/YYYY HH:MI:SS AM'),3000, NULL, 20);

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

grant query rewrite to scott;
grant create materialized view to scott;

SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

-- OPTIMIZER_MODE가 CHOOSE인 상태에서 Analyze를 실행하지 않아 QueryRewrite가 실행되지 않음
-- OPTIMIZER_MODE가 CHOOSE일 경우는 모든 테이블을 ANALYZE
-- 또한 MVIEW를 ENABLE QUERY REWRITE 옵션으로 생성해야 함
SQL> SET AUTOTRACE OFF;
SQL> drop MATERIALIZED VIEW EMP_SUMSAL;
SQL> CREATE MATERIALIZED VIEW EMP_SUMSAL
ENABLE QUERY REWRITE
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

SQL> set autot trace
SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 503417823

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

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> SELECT /*+ NOREWRITE */ SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>

SQL>  SELECT SUM(a.sal), a.deptno
  2   FROM emp a, dept b
  3   WHERE a.deptno = b.deptno
  4   GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 503417823

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

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

?FAST REFRESH 방식을 이용하는 경우 MATERIALIZED VIEW LOG가 필요(PCT REFRESH는 예외)
?마스터 테이블의 변경된 데이터를 저장하는 LOG 테이블
?FAST REFRESH 수행 시 젂체 테이블이 아닌 변경된 데이터맊을 반영할 수 있게 해줌
?MVIEW의 FAST REFRESH를 위해서 MVIEW LOG 정의에 ROWID 혹은 PRIMARY KEY 옵션을 지정
?집계(SUM, AVG 등) MVIEW 인 경우에는 MVIEW가 참조하는 테이블의 모든 컬럼을 포함해야 하므로 INCLUDING NEW VALUES옵션과 SEQUENCE 옵션을 지정해야 함
?MIXED DML(한 트랜잭션에서 여러 테이블에 대한 INSERT, UPDATE, DELETE의 조합 사용)의 경우 SEQUENCE 옵션을 권장

DB 링크시 완젂 동기 리프레쉬(ON COMMIT) 안되고 동일 MACHINE에서맊 가능

INSERT INTO EMP
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
VALUES
( 7916, 'KIM', 'MANAGER', 7839,
sysdate,
3000, NULL, 20)

ALTER TABLE sales
     SPLIT PARTITION sales_q2 AT (2005, 01, 01)
     INTO (PARTITION sales_q1 ,
           PARTITION sales_q2 );

ALTER TABLE sales
     SPLIT PARTITION sales_q3 AT (2005, 09, 01)
     INTO (PARTITION sales_q31 ,
           PARTITION sales_q32 );

- Local의 의미는 테이블 파티션 키와 인덱스 파티션 키가 같음
- Prefixed 는 인덱스 첫 번째 컬럼이 인덱스 파티션 키와 같음
SQL> CREATE INDEX sales_idx01
ON sales(sale_year, sale_month, sale_day) LOCAL
(PARTITION idx_sales_p1 ,
PARTITION idx_sales_p2 ,
PARTITION idx_sales_p3 ,
PARTITION idx_sales_p4 );
또는,
SQL> CREATE INDEX sales_idx01 ON sales(sale_year, sale_month, sale_day) LOCAL;
-- 인덱스 키 컬럼 확인
SQL> select INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
from all_ind_columns where table_name='SALES' ORDER BY 1,3;
-- 인덱스 파티션 확인
SQL> select *
from all_ind_partitions where index_name = 'SALES_IDX01‘;

- Index의 첫 번째 컬럼이 파티션 키 컬럼으로 시작하지 않는 Local Index
- 파티션 키 컬럼이 Index에 중갂에 올 수도 있지맊 항상 선두에 오지 않는 한 Non-prefixed index
SQL> CREATE INDEX sales_idx02
ON sales(sales_no, sale_year) LOCAL
(PARTITION idx_sales_p1 ,
PARTITION idx_sales_p2 ,
PARTITION idx_sales_p3 ,
PARTITION idx_sales_p4 ) ;
-- 인덱스 확인
SQL> SELECT INDEX_NAME, PARTITION_COUNT, partitioning_type, locality, ALIGNMENT
fROM USER_PART_INDEXES WHERE TABLE_NAME = 'SALES' ;
INDEX_NAME PARTITION_COUNT PARTITION LOCALI ALIGNMENT
------------------------------ --------------- --------- ------ ------------
SALES_IDX01 4 RANGE LOCAL PREFIXED
SALES_IDX02 4 RANGE LOCAL NON_PREFIXED

-Range Partition으로 생성된 sales 테이블 sale_year 컬럼에 Global prefixed Index로 생성
SQL> CREATE INDEX sales_idx03
ON sales(sale_year)
GLOBAL PARTITION BY RANGE (sale_year)
(PARTITION idx_sales_year_p1 VALUES LESS THAN (2005) ,
PARTITION idx_sales_year_p2 VALUES LESS THAN (2010) ,
PARTITION idx_sales_year_p3 VALUES LESS THAN (MAXVALUE) ); ? 없으면 에러
SQL> SELECT INDEX_NAME, PARTITION_COUNT, partitioning_type, locality, ALIGNMENT
fROM USER_PART_INDEXES WHERE TABLE_NAME = 'SALES' ;
INDEX_NAME PARTITION_COUNT PARTITION LOCALI ALIGNMENT
------------------------------ --------------- --------- ------ ------------
SALES_IDX01 4 RANGE LOCAL PREFIXED
SALES_IDX02 4 RANGE LOCAL NON_PREFIXED
SALES_IDX03 3 RANGE GLOBAL PREFIXED

SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE
from USER_ind_partitions where INDEX_NAME LIKE 'SALES%';
INDEX_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ---------------
SALES_IDX01 IDX_SALES_P1 2005, 01, 01
SALES_IDX01 IDX_SALES_P2 2005, 07, 01
SALES_IDX01 IDX_SALES_P3 2006, 01, 01
SALES_IDX01 IDX_SALES_P4 2006, 07, 01
SALES_IDX02 IDX_SALES_P1 2005, 01, 01
SALES_IDX02 IDX_SALES_P2 2005, 07, 01
SALES_IDX02 IDX_SALES_P3 2006, 01, 01
SALES_IDX02 IDX_SALES_P4 2006, 07, 01
SALES_IDX03 IDX_SALES_YEAR_P1 2005
SALES_IDX03 IDX_SALES_YEAR_P2 2010
SALES_IDX03 IDX_SALES_YEAR_P3 MAXVALUE

SQL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE,
PARTITION_POSITION, NUM_ROWS
from user_tab_partitions
where table_name='SALES';
SQL> alter table SALES drop partition SALES_Q1;
SQL> select index_name, partition_name, high_value, num_rows, status
from user_ind_partitions
where index_name LIKE'SALES%';
-- GLOBAL PARTITION INDEX는 테이블 파티션 DROP 되거나 TRUNCATE 될 때 UNUSABLE 됨
SQL> select * from all_indexes where STATUS not in ('VALID','N/A');
SQL> select index_name, partition_name, status from all_ind_partitions where STATUS not in ('USABLE');

INDEX REBUILD
SQL> alter index SALES_IDX03 NOLOGGING;
SQL> alter index SALES_IDX03 rebuild partition IDX_SALES_YEAR_P1;
SQL> alter index SALES_IDX03 rebuild partition IDX_SALES_YEAR_P2;
SQL> alter index SALES_IDX03 rebuild partition IDX_SALES_YEAR_P3;
SQL> alter index SALES_IDX03 LOGGING;

-- MATERIALIZED VIEW 생성
SQL> CREATE MATERIALIZED VIEW dept_sal
-- PCTFREE 0 TABLESPACE mviews
-- STORAGE (initial 16k next 16k pctincrease 0)
BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
REFRESH
COMPLETE -- FORCE, COMPLETE, FAST, NEVER 선택.
ON DEMAND -- ON DEMAND, ON COMMIT 선택.
ENABLE QUERY REWRITE
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;
구체화된 뷰가 생성되었습니다.

- REFRESH 절은 오라클이 MView의 데이터를 언제, 어떻게 Refresh 하는지를 결정 하는 방법입니다.
.Refresh 방법에는 ON COMMIT 방법과, ON DEMAND 방법 2 가지가 있습니다.

.ON COMMIT 은 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며,
이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나, MView에 조인만이
있는 경우, Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만
사용이 가능 합니다.

.ON DEMAND는 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS,
REFRESH_DEPENDENT) 를 실행 한 경우 Refresh 되는 경우 입니다.


- Refresh를 하는 방법에는 FORCE, COMPLETE, FAST, NEVER의 4가지가 존재 합니다.

.COMPLETE : MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는 것으로
ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우 입니다.

.FAST : 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안으로 Direct Path나
Mview log를 이용 합니다.

.FORCE : 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면 이를 적용하고,
아니면 Complete Refresh를 적용 합니다.(디폴트)

.NEVER : MView의 Refresh를 발생시키지 않습니다

이 내용은 어딘가에서 퍼온 내용입니다.

참고하시면 될 것 같습니다.

cd C:\app\owner\diag\rdbms\orcl\orcl\trace
sqlplus scott/oracle
set linesize 132
set timing on
set pages 3000
alter session set tracefile_identifier='test28';
alter session set sql_trace=true;

select a.empno, a.ename, b.dname
from big_emp1 a, big_dept1 b
where a.deptno = b.deptno;

alter session set sql_trace=false;

C:\app\owner\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_1220_test28.trc test28.txt sys=no

------------------------------
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

desc dba_tablespaces;
select tablespace_name, retention from dba_tablespaces;
alter tablespace undotbs1 retention guarantee;

create materialized view emp_sumsal
build immediate refresh complete on commit
enable query rewrite
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

select * from all_objects where object_name = 'EMP_SUMSAL';

drop materialized view emp_sumsal;

create materialized view emp_sumsal
build deferred  -- 추후 data  생성
as
select sum(a.sal) as sum_sal, a.deptno
from emp a, dept b
where a.deptno = b.deptno
group by a.deptno;

--? 알아서 data 가져온다.
SQL> exec dbms_mview.refresh('EMP_SUMSAL','?');

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

SQL> select * from emp_sumsal;

   SUM_SAL     DEPTNO
---------- ----------
      9400         30
     10075         20
      8750         10

- C : COMPLETE
CREATE 명령의 모든 QUERY 정의를 재실행하여 기졲 데이터를 잘라내는 작업과 마스터 테이블을 기반으로 하는 모든 데이터를 재구축하는 작업이 포함
- F : FAST
마지막 REFRESH 이후의 변경 사항을 적용하며 젂체 재구축을 수행하지는 않음
MIVEW LOG를 사용한 REFRESH : 마스터 테이블의 모든 변경사항이 로그에 기록된 후 MVIEW에 적용
ROWID 범위를 이용한 REFRESH : 싞규 행의 ROWID를 이용하여 DIRECT PATH LOAD 후 MVIEW에 적용
- ? : FORCE
COMPLETE + FAST

EXEC DBMS_MVIEW.REFRESH('EMP_SUMSAL','?');

CREATE MATERIALIZED VIEW dept_sal
BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
REFRESH COMPLETE -- FORCE, COMPLETE, ?
ON COMMIT -- ON DEMAND, ON COMMIT 선택.
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

INSERT INTO EMP
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
VALUES
( 7935, 'KIM', 'MANAGER', 7839,
TO_Date( '12/17/1980 12:00:00 오전', 'MM/DD/YYYY HH:MI:SS AM'),3000, NULL, 20);

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

grant query rewrite to scott;
grant create materialized view to scott;

SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

-- OPTIMIZER_MODE가 CHOOSE인 상태에서 Analyze를 실행하지 않아 QueryRewrite가 실행되지 않음
-- OPTIMIZER_MODE가 CHOOSE일 경우는 모든 테이블을 ANALYZE
-- 또한 MVIEW를 ENABLE QUERY REWRITE 옵션으로 생성해야 함
SQL> SET AUTOTRACE OFF;
SQL> drop MATERIALIZED VIEW EMP_SUMSAL;
SQL> CREATE MATERIALIZED VIEW EMP_SUMSAL
ENABLE QUERY REWRITE
AS
SELECT SUM(a.sal), a.deptno
FROM emp a, dept b
WHERE a.deptno = b.deptno
GROUP BY a.deptno;

SQL> set autot trace
SQL> SELECT SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 503417823

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

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> SELECT /*+ NOREWRITE */ SUM(a.sal), a.deptno
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY a.deptno;

Execution Plan
----------------------------------------------------------
Plan hash value: 2006461124

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     3 |    30 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |     3 |    30 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    14 |   140 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    12 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."DEPTNO"="B"."DEPTNO")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        555  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL>