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;