Re: materialized view question

On Thu, Aug 21, 2008 at 9:08 AM, Dba DBA <oracledbaquestions_at_gmail.com>wrote:

> 1. can you set the materialized view to rollup using parallel query so it
> runs faster ?
>

Haven't tried it myself, but I can't imagine why not.

>
> 2. how does the materialized view know which records are new and need to be
> added to the rollup? Doesn't it add some kind of loggng table? doesn't cause
> overhead ?
>

There is a logging table on the source.
Yes there is overhead, but I personally have not found this to be a problem, with caveats.
As of 9i (or was it 8i?) the triggers to populate the logs are integrated into the kernel,
and are quite fast.

When creating a MV log, there's an index you should create that is not created by default.

eg.

create index SAPR3."MLOG$_QPGT_IDX1"
on SAPR3."MLOG$_QPGT" (SNAPTIME$$)
pctfree 5
tablespace sap_mv_medium
/

Doing so will greatly reduce the IO on the MV logs. There's an ML note on this somewhere I believe.

Refreshes are tracked by SCN.

Here's how to see the current SCN of the MV Log on the source db:

select o.owner, o.object_name, t.spare3
from sys.tab$ t, dba_objects o
where t.obj# = o.object_id
and o.object_type = 'TABLE'
and nvl(t.spare3,0) > 0
order by 1
/

And here's how to see the current SCN of the MV on the Target:

select o.owner, o.object_name, s.lastrefreshscn from sys.sum$ s, dba_objects o
where s.obj# = o.object_id
order by 1,2
/

An example:

Source DB:

OWNER      OBJECT NAME                        SPARE3
---------- ------------------------------ ----------
SAPR3      AFIH                           6174112193
SAPR3      AFKO                           6174112194
SAPR3      AFVC                           6174112142
SAPR3      AFVV                           6174112143
SAPR3      AUSP                           6174087371

Notice that the SCN's are all different as the updates have occurred at different
times for each table.

Target DB:

OWNER      OBJECT NAME                    LASTREFRESHSCN
---------- ------------------------------ --------------
SAP_MV     AFIH_MV                            6174111583
SAP_MV     AFKO_MV                            6174111583
SAP_MV     AFVC_MV                            6174111583
SAP_MV     AFVV_MV                            6174111583
SAP_MV     AUSP_MV                            6174111583

The SCN at the target is the current SCN at the source DB at the time of the last refresh.
These MV's are refreshed as a group, so the SCN is the same for each.

After the last registered MV is refreshed from a MV log, the entries earlier

than the earliest SCN of a registered MV are removed from the log via DELETE. BTW, should you do any complete refreshes, be sure to google for atomic_refresh,
as the DELETE vs. TRUNCATE table behavior changed in 10g.

Another bit of trivia: the MV's are tracked internally with sys.slog$.snapid.

The only place this column is exposed is not in MVIEW views, but in various SNAPSHOT views.

OWNER      TABLE NAME                     COLUMN

---------- ------------------------------ ------------------------------
--------------------
SYS        ALL_REGISTERED_SNAPSHOTS       SNAPSHOT_ID
NUMBER
           ALL_SNAPSHOT_LOGS              SNAPSHOT_ID
NUMBER
           DBA_REGISTERED_SNAPSHOTS       SNAPSHOT_ID
NUMBER
           DBA_SNAPSHOT_LOGS              SNAPSHOT_ID
NUMBER
           REG_SNAP$                      SNAPSHOT_ID
NUMBER
           USER_REGISTERED_SNAPSHOTS      SNAPSHOT_ID
NUMBER
           USER_SNAPSHOT_LOGS             SNAPSHOT_ID

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l