Shulou(Shulou.com)05/31 Report--

How to create a materialized view in Oracle, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Oracle materialized view

1. The basic concept of oracle materialized view

The materialized view first needs to create a materialized view log

Oracle creates a materialized view log table based on the materialized view log created by the user

The name of the materialized view log table is mlog$_ followed by the name of the base table

If the length of the table name is more than 20 digits, only the first 20 digits will be taken. When the name is duplicated after truncation, oracle will automatically add a number to the materialized view log name as the sequence number.

There are several options for creating materialized view logs: you can specify rowid, primary key, and object id types, and you can also specify sequence or explicitly specify column names.

The structure of the materialized view log resulting from all of the above situations is different.

The columns that any materialized view will include:

Snaptime$$: is used to indicate the refresh time.

Dmltype$$: is used to indicate the type of dml operation, and I represents insert,d for delete,u and update.

Old_new$$: is used to indicate whether the value is new or old. N (ew) represents the new value, o (ld) represents the old value, and u represents the update operation.

Change_vector$$ represents a modification vector, which is used to indicate which field or fields are being modified.

If with is followed by primary key, the materialized view log contains primary key columns.

If the with is followed by rowid, the materialized view log contains: masks rowrecords: the rowid used to store the changed records.

If with is followed by object id, the materialized view log contains the object id that sys_nc_oid$: uses to record each change object.

If with is followed by sequence, the materialized view day will include: sequence$$: gives each operation a sequence number, which ensures that the refresh will be done in order.

If the with is followed by one or more column names, these columns are included in the materialized view log.

2. Oracle materialized view log

1.primary key

Drop table test_id;-Delete the table

Create table test_id (id number,name varchar2 (30), mark number);-- create tables

Alter table test_id add constraint pk_test_id primary key (id);-- add primary key

Drop materialized view log on test_id;-- deletes materialized view log

Create materialized view log on test_id tablespace ttts with primary key;-create materialized view log based on primary key

-- the system builds tables for logs

Sql > desc mlog$_test_id

Name type nullable default comments

--

Id number y uses the primary key to record the line on which the dml operation occurred

The snaptime$$ date y snaptime$$ column records the time of the refresh operation.

The record values I, u, and d of dmltype$$ varchar2 (1) y dmltype$$ indicate whether the operation is insert, update, or delete.

Old_new$$ varchar2 (1) y old_new$$ indicates whether the information saved in the materialized view log is the value before the dml operation (old value) or after the dml operation (new value). In addition to the o and n types, for update operations, it may also be expressed as u.

The change_vector$$ raw (255y change_vector$$ record dml operation occurs on that field or fields.

When creating materialized view logs using primary key, oracle creates temporary tables RUPD$_ base tables

Sql > desc rupd$_test_id

Name type nullable default comments

--

Id number y

Dmltype$$ varchar2 (1) y

Snapid integer y

Change_vector$$ raw (255Y)

2.rowid

Drop table test_rowid;-Delete the table

Create table test_rowid (id number,name varchar2 (30), mark number);-- create tables

Drop materrialized view log on test_rowid

-- create materialized view log on test_rowid with rowid, sequence (id, name) including new values

Create materialized view log on test_rowid with rowid;-- creates materialized view log based on rowid

Sql > desc mlog$_test_rowid

Name type nullable default comments

--

Varchar2 (255y) y

Snaptime$$ date y

Dmltype$$ varchar2 (1) y

Old_new$$ varchar2 (1) y

Change_vector$$ raw (255Y)

3.object id

Create type test_object as object (id number, name varchar2 (30), num number);-- create type

Create table test_objid of test_object;-create tables

Create materialized view log on test_objid with object id;-- creates materialized view log based on object id

Sql > desc mlog$_test_objid

Name type nullable default comments

--

Sys_nc_oid$ raw (16) y

Snaptime$$ date y

Dmltype$$ varchar2 (1) y

Old_new$$ varchar2 (1) y

Change_vector$$ raw (255Y)

4. Sequenceuploading widget + (property column)

Drop table test_sq;-Delete the table

Create table test_sq (id number,name varchar2 (30), mark number);-- create tables

Drop materialized view log on test_sq;-- deletes materialized view log

Create materialized view log on test_sq tablespace ttts with sequence;-create materialized view log based on sequence

-- ora-12014: table 'test_sq' does not contain primary key constraints

Create materialized view log on test_sq with sequence (id, name,num) including new values;-- contains all the columns of the underlying table

-- ora-12014: table 'test_sq' does not contain primary key constraints

Alter table test_sq add constraint uk_test_sq unique (id,name);-- add uk

Create materialized view log on test_sq with sequence (id,name) including new values

-- ora-12014: table 'test_sq' does not contain primary key constraints

That is, the primary key, rowid or object id are used to uniquely represent the records in the materialized view log. Sequence cannot uniquely identify the record, so it cannot be used to create the log alone.

Create materialized view log on test_sq with rowid,sequence (id, name) including new values

Sql > desc mlog$_test_sq

Name type nullable default comments

--

The columns specified when id number y creates the materialized view are recorded in the materialized view log.

Name varchar2 (30) y

Varchar2 (255y) y

Sequence$$ number y sequence numbers the records in the materialized view log according to the order in which the operations occur.

Snaptime$$ date y

Dmltype$$ varchar2 (1) y

Old_new$$ varchar2 (1) y

Change_vector$$ raw (255Y)

3. Oracle materialized view log table

Basic table: test_id,test_rowid,test_objid,test_sq

Log table: mlog$_test_id,mlog$_test_rowid,mlog$_test_objid,mlog$_test_sq

1. Add

Insert into test_id values (1,'a', 5)

Insert into test_rowid values (1,'a', 5)

Insert into test_objid values (1,'a', 5)

Insert into test_sq values (1,'a', 5)

Commit

two。 Modify

Update test_id set name ='c 'where id = 1

Update test_rowid set name ='c 'where id = 1

Update test_objid set name ='c 'where id = 1

Update test_sq set name ='c 'where id = 1

Commit

3. Delete

Delete test_id

Delete test_rowid

Delete test_objid

Delete test_sq

Commit

View log table records after each step of commit.

4. Value analysis of log table fields in oracle materialized view.

1. Snaptimekeeper $

When the dml operation occurs in the base table, it is recorded in the materialized view log, and the specified time is 00:00:00 on January 1st, 4000 (the materialized view is not refreshed).

If the materialized view log is used by multiple materialized views, after a materialized view is refreshed, the time of its refresh record is updated to its refresh time.

Only by establishing a rapidly refreshing materialized view can you use the materialized view log. If only one materialized view is created, the materialized view log will be cleared when the materialized view is refreshed.

2. Dmltypewriter $

The operation type is relatively simple: only I (insert), d (delete) and u (update) are included.

3. Oldnews news $

There are also three kinds of new and old values: O represents the old value (delete for the corresponding operation), n represents the new value (the corresponding operation is insert), and u (corresponding to the update operation).

It should be noted that for the primary key-based materialized view log, if the primary key is updated, the update operation is transformed into a delete operation, an insert operation. Finally, there is the delete operation.

The only difference is that each update operation corresponds to two records in the materialized view log.

The original record dmltype$$ and old_new$$ corresponding to the update operation are both u, and a new record after the update operation is dmltype$$.

This occurs when the including new values statement is indicated when the materialized view log is created.

4. Changeover vector $

Finally, we briefly discuss the change_vector$$ column.

Both insert and delete operations are recordset, that is, insert and delete affect the entire record.

While the update operation is a set of fields, the update operation may update all fields of the entire record, or only individual fields.

Whether in terms of performance or data consistency, materialized views should be refreshed based on field sets.

Oracle uses the change_vector$$ column to record which fields have changed for each record.

Materialized view logs based on primary key, rowid, and object id are slightly different on change_vector$$, but the overall design idea is the same.

Change_vector$$ columns are of type raw. In fact, oracle uses each bit bit to map a column.

For example, the first column is updated to 02, or 00000010.

The second column is set to 04, or 00000100

The third column is set to 08, or 00001000.

When the first column and the second column are updated at the same time, it is set to 0600000110.

If all three columns are updated, set to 0ecenture 00001110.

And so on, column 4 is updated with 0x10, column 5 0x20, column 6 0x40, column 7 0x80, column 8 0x100.

When the 1000th column is updated, the length of the change_vector$$ is 1000 and the length is 252.

In addition to fields that can represent update, you can also represent insert and delete. The delete operation change_vector$$ is listed as all zero, and the specific number is determined by the number of columns in the base table.

The lowest bit of insert operation is fe, and if there are a large number of base table columns and there are high bits, all the high bits are ff.

If the insert operation is caused by the update operation updating the primary key as discussed earlier, the change_vector$$ corresponding to the insert operation is listed as full ff.

As you can see, as analyzed above, the insert is 00 for fe,delete, the update for the first column is 02, the second column is 04, and the second and third columns are updated to 0c. It is important to note that normally, the first column starts at 02.

However, if a truncate operation is performed on the mlog$ table, or the materialized view log is rebuilt, the start of the first column may be offset.

This result is basically the same as the rowid type, except that if the primary key is updated, the update operation will be recorded as a delete and an insert in the materialized view log, but in this case the value of the change_vector$$ for insert is ff.

This result is also basically consistent with the rowid type, and it is important to note that because the object table contains two implicit columns, id is no longer the first field, but the third, so the corresponding value is 08.

Finally, let's look at an example with a large number of columns. The only thing to note is that the low position is on the left and the high position is on the right.

5. Oracle materialized view

1. Materialized view mv_test_id

Create materialized view mv_test_id refresh fast on commit as

Select * from test_id;-- the materialized view is refreshed when commit

two。 Materialized view mv_test_rowid

Create materialized view mv_test_rowid refresh fast as

Select name, count (*) from test_rowid group by name

-- ORA-32401: the materialized view log on "TT". "TEST_ROWID" has no new value

Alter materialized view log on test_rowid add including new values

Create materialized view mv_test_rowid refresh fast as

Select name, count (*) from test_rowid group by name

-- ORA-12033: you cannot use the filter column in the materialized view log on "TT". "TEST_ROWID"

Alter materialized view log on test_rowid add (name)

Create materialized view mv_test_rowid refresh fast as

Select name, count (*) from test_rowid group by name

3. Materialized view mv_test_objid

Create materialized view mv_test_objid refresh fast as

Select * from test_objid

-- ORA-12014: table 'TEST_OBJID' does not contain primary key constraints

Alter table test_objid add constraint pk_test_objid primary key (id);-- add primary key

Create materialized view mv_test_objid refresh fast as

Select * from test_objid

-- ORA-23415: the materialized view log of "TT". "TEST_OBJID" does not record primary keys

Alter materialized view log on test_objid add (id)

Alter materialized view log on test_objid add primary key (id)

Drop materialized view log on test_objid

Create materialized view log on test_objid tablespace ttts with primary key including new values

Create materialized view mv_test_objid refresh fast as

Select * from test_objid

4. Materialized view mv_test_sq

Create materialized view mv_test_sq refresh fast as

Select name, count (*) from test_sq group by name;-need to refresh with exec dbms_mview.refresh ('mv_test_sq')

5. Materialized view refresh

Exec dbms_mview.refresh ('mv_test_rowid')

Exec dbms_mview.refresh ('mv_test_objid')

Exec dbms_mview.refresh ('mv_test_sq')

The log table record is emptied after the materialized view is refreshed.

Materialized view refresh when refresh fast as calls exec dbms_mview.refresh ('mv_ basic table')

Refresh fast on commit as refreshes the materialized view during commit

Refresh fast on demand timed materialized view refresh

Create materialized view mv_test_sq2 refresh fast on demand

With rowid start with to_date ('22-04-2011 1615 30 next 01mm, 'dd-mm-yyyy hh34:mi:ss') next / * 1:hrs*/ sysdate + 1 / (24060)

As select id,count (*) from test_sq group by id

6. Error prompt:

-- ORA-32401: the materialized view log on "TT". "TEST_ROWID" has no new value

Alter materialized view log on test_rowid add including new values

-- ORA-12033: you cannot use the filter column in the materialized view log on "TT". "TEST_ROWID"

Alter materialized view log on test_rowid add (name)

-- ORA-12014: table 'TEST_OBJID' does not contain primary key constraints

Alter table test_objid add constraint pk_test_objid primary key (id);-- add primary key

-- ORA-23415: the materialized view log of "TT". "TEST_OBJID" does not record primary keys

Drop materialized view log on test_objid

Create materialized view log on test_objid tablespace ttts with primary key including new values

7. Related grammar:

Create {materialized view | snapshot} log on [tablespace] [storage (…)] [pctfree] [pctused] [initrans] [maxtrans] [logging | nologging] [cache | nocache] [noparallel | parallel []] [partition...] [lob...] [using index...] [with [primary key] [, rowid] [([,...])] ] [{including | excluding} new values]

Alter {materialized view | snapshot} log on [add [primary key] [, rowid] [([,...])] ] [...]

Drop {materialized view | snapshot} log on

Create {materialized view | snapshot} [tablespace] [storage (…)] [pctfree] [pctused] [initrans] [maxtrans] [logging | nologging] [cache | nocache] [noparallel | parallel []] [cluster ([, …])] [lob...] [partition...] [build {immediate | deferred}] [on prebuilt table [{with | without} reduced precision]] [using index …] [refresh [fast | complete | force] [on commit | on demand] [start with''] [next''] [with {primary key | rowid}] [using [default] [master | local] rollback segment []] | never refresh] [for update] [{enable | disable} query rewrite] as

Alter {materialized view | snapshot}... [compile]

Drop {materialized view | snapshot}

8. Give examples

Connect pubr/bit@datasource

Drop materialized view log on pubr.allactive;-deletes the materialized view log

Create materialized view log

On pubr.allactive tablespace logts with primary key;-create materialized view log

Connect ttowb/bit

Drop materialized view allactive_tt;-deletes materialized views

Create materialized view allactive_tt

Refresh fast

As select ID,CATEGORY,FLOWID,MASTATUS,BASTATUS,APPLYDATETIME,CREATEDATETIME,COMMITDATETIME,BITSPNO,ARCHIVETIME

DESCRIPTION,OPERTYPE,ISVALID,INVALIDREASON,INVALIDDATETIME,INVALIDPNO,ACTIVETABLENAME,PARENTID,STANID,REALTYPEID

CORRECTID,to_date ('1900-01-01') allactive_rtime from pubr.allactive@pubrowb;-create a materialized view

After reading the above, have you mastered how to create materialized views in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.