Database Software

Hello, experts!

My customer has a bad performance problem with mview refresh.
He uses fast refresh for mview which has both max(expr) and count(expr).

However, a Fast refreshing MVIEW is using 'index fast full scan' or 'full table scan' on Master table.
Is it possible that the customer makes "fast refresh" to use 'index range scan' on master table ?

# Detail information is like below. (This is a simple testcase, so the table has only 11 rows.)

1) Step for 'fast refresh' is very simple.

SQL> DELETE EMP WHERE EMPNO = 7900;
SQL> exec dbms_mview.refresh('MV_EMP_TEST1', 'F');

2) Then, plan is like below.

INSERT INTO "NEXS"."MV_EMP_TEST1" SELECT "AV$"."DEPTNO",COUNT(*),
COUNT("AV$"."EMPNO"),MAX("AV$"."EMPNO") FROM (SELECT "MAS$".* FROM
"NEXS"."EMP" "MAS$" WHERE (SYS_OP_MAP_NONNULL("MAS$"."DEPTNO")) IN ( SELECT
SYS_OP_MAP_NONNULL("MAS$"."DEPTNO") FROM (SELECT /*+ HASH_SJ */
"MAS$"."DEPTNO", "MAS$"."EMPNO" FROM "NEXS"."MLOG$_EMP" "MAS$" WHERE
"MAS$".SNAPTIME$$ > :B_ST0 ) AS OF SNAPSHOT(:B_SCN) "MAS$" )) "AV$" GROUP
BY "AV$"."DEPTNO"

Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=15 pr=0 pw=0 time=0 us)
1 HASH GROUP BY (cr=10 pr=0 pw=0 time=0 us cost=10 size=528 card=11)
4 HASH JOIN SEMI (cr=10 pr=0 pw=0 time=3 us cost=9 size=528 card=11)
11 INDEX FAST FULL SCAN EMP_PRIMARY_KEY (cr=3 pr=0 pw=0 time=10 us cost=2 size=286 card=11)(object id 73584)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1 TABLE ACCESS FULL MLOG$_EMP (cr=7 pr=0 pw=0 time=0 us cost=6 size=22 card=1)


# MVIEW script
CREATE MATERIALIZED VIEW MV_EMP_TEST1
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
WITH ROWID
DISABLE QUERY REWRITE
AS
select DEPTNO, count(*) cnt,
count(EMPNO) EMPNO_cnt ,
max(EMPNO) EMPNO_max
from EMP
group by DEPTNO;


# MVIEW LOG script
CREATE MATERIALIZED VIEW LOG ON EMP WITH SEQUENCE, ROWID (DEPTNO, EMPNO, SAL) INCLUDING NEW VALUES;


# Master table script

CREATE TABLE NEXS.EMP
(
DEPTNO NUMBER (2) NOT NULL,
EMPNO NUMBER (4) NOT NULL,
ENAME VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR NUMBER (4),
HIREDATE VARCHAR2 (10),
SAL NUMBER (7,2),
COMM NUMBER (7,2),
CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (DEPTNO,EMPNO));


Thanks in advance.
Youseob in GSS Korea.