Oracle 10g R2 features-Data Warehouse integration features (1)

Shard change tracking: MV logs are not required

To understand this enhancement function, you must first understand the partition trimming concept during the materialized view (MV) Refresh process.

Assume that the table ACCOUNTS is partitioned Based on the column ACC_MGR_ID, and each ACC_MGR_ID value is a partition. You have created a music video named ACC_VIEW based on ACCOUNTS. The music video is also partitioned Based on the ACC_MGR_ID column. Each ACC_MGR_ID is partitioned, as shown in:

Assume that the record in the table ACCOUNTS has been updated, but this update is only performed in partition P1. To refresh the MV quickly, you only need to refresh the partition P1 instead of the entire table. Here, it is the partition where the data related to ACC_MGR_ID is located. Oracle automatically executes this task to track partition changes using a feature named partition change tracking (PCT. Note: To enable PCT during the quick refresh process, you must create music video logs. These logs are filled when the rows in the table change. After the refresh command is issued, the refresh process reads the MV log to identify these changes.

Needless to say, this requirement increases the total execution time of the operation. In addition, the additional insert operation consumes the CPU cycle and I/O bandwidth.

Fortunately, in Oracle Database 10g 2nd, the PCT does not need music video logs. Let's take a look at how it works. First, check that the table ACCOUNTS does not contain MV logs.

SQL> select * 2  from dba_mview_logs 3  where master = 'ACCOUNTS'; no rows selected

Update a record in the table.

update accounts set last_name = '...' where acc_mgr_id = 3;

The record is located in partition P3.

Now you can refresh the music video. However, the segment-level statistics of all segments in the table ACCOUNTS are recorded first. Later, you will use the statistical information to learn which segments are used.

select SUBOBJECT_NAME, value from v$segment_statistics where owner = 'ARUP' and OBJECT_NAME = 'ACCOUNTS' and STATISTIC_NAME = 'logical reads' order by SUBOBJECT_NAME / SUBOBJECT_NAME                      VALUE ------------------------------ ---------- P1                                   8320 P10                                  8624 P2                                  12112 P3                                  11856 P4                                   8800 P5                                   7904 P6                                   8256 P7                                   8016 P8                                   8272 P9                                   7840 PMAX                                  256 11 rows selected.

Use Quick refresh to refresh the materialized view ACC_VIEW.

execute dbms_mview.refresh('ACC_VIEW','F')

The 'F' parameter indicates a quick refresh. But if the table does not have music video logs, does it work?

After refresh, check the segment statistics of ACCOUNTS again. The result is as follows:

SUBOBJECT_NAME                      VALUE ------------------------------ ---------- P1                                   8320 P10                                  8624 P2                                  12112 P3                                  14656 P4                                   8800 P5                                   7904 P6                                   8256 P7                                   8016 P8                                   8272 P9                                   7840 PMAX                                  256

The statistical information of these segments shows the segments selected during a logical read process. Because these statistics are cumulative, you must view changes in values (not absolute values. If you carefully check the preceding values, you will find that only the value of partition P3 has changed. Therefore, during the refresh process, only partition P3 is selected, instead of the entire table. Check whether the PCT can work in the table even if there is no MV log.

Even if the base table does not have music video logs, the ability to quickly refresh music videos is a powerful and useful feature, this allows you to perform quick refresh in a partitioned MV without increasing performance overhead. I think this feature is the most useful data warehouse enhancement feature in Oracle 10g 2nd.