다물8

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>