Ygthkb

False written accusations not made public - is there law to cover this?

Odd 74HCT1G125 behaviour

Gear reduction on large turbofans

Use two 8s and two 3s to make the number 24

Mathematics and the art of linearizing the circle

Can a hotel cancel a confirmed reservation?

Should I reinstall Linux when changing the laptop's CPU?

What would be the rarity of this magic item(s)?

Early credit roll before the end of the film

When can a QA tester start his job?

Is using an 'empty' metaphor considered bad style?

Why is it that Bernie Sanders is always called a "socialist"?

Quickly creating a sparse array

Removing disk while game is suspended

Can we harness gravitational potential energy?

Why don't hotels mount air conditioning units outside the rooms?

kill -0 <PID> は何をするのでしょうか？

What does it mean for a caliber to be flat shooting?

Difference between i++ and (i)++ in C

How do I append a character to the end of every line in an Excel cell?

Why do neural networks need so many training examples to perform?

How does Leonard in "Memento" remember reading and writing?

How can I play a serial killer in a party of good PCs?

How old is the day of 24 equal hours?

Why create a Materialized View Log with Primary Key or RowID, etc?

Do I need to enumerate the columns used in a materialized view when creating the MV log?Materialized view taking too much timeCreate materialized view no refresh from existing view with indexCreate FAST Materialised View in Oracle 10g with ROWIDWriteable Oracle materialized view without FAST REFRESH requirementsRefresh materialize View fast on commit multiple tableFK to table with non-unique values via a on commit refreshing materialized view?Error creating aggregate materialized viewDeploy materialized view with database link using another userCreate a materialized view on commit with PIVOT function

2

I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t    

create materialized view log on t WITH PRIMARY KEY ;

create materialized view log on t WITH ROWID ;

create materialized view log on t WITH ROWID, PRIMARY KEY ;

create materialized view log on t  WITH SEQUENCE ;

create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY





CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1

    NOLOGGING

    CACHE

    BUILD IMMEDIATE 

    REFRESH FAST ON COMMIT 

    AS 

        SELECT *

        FROM LLATTRDATA D

        WHERE 

            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR

            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR

            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR

            (D.DEFID = 3072256 AND D.ATTRID = 5);



BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'me',

    tabname => 'MV_LLATTRDATA_TEST1');

END;

oracle oracle-11g-r2 view

share|improve this question

edited Feb 7 '18 at 23:14

Batman

asked Feb 7 '18 at 23:09

BatmanBatman

1198

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

2

I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t    

create materialized view log on t WITH PRIMARY KEY ;

create materialized view log on t WITH ROWID ;

create materialized view log on t WITH ROWID, PRIMARY KEY ;

create materialized view log on t  WITH SEQUENCE ;

create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY





CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1

    NOLOGGING

    CACHE

    BUILD IMMEDIATE 

    REFRESH FAST ON COMMIT 

    AS 

        SELECT *

        FROM LLATTRDATA D

        WHERE 

            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR

            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR

            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR

            (D.DEFID = 3072256 AND D.ATTRID = 5);



BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'me',

    tabname => 'MV_LLATTRDATA_TEST1');

END;

oracle oracle-11g-r2 view

share|improve this question

edited Feb 7 '18 at 23:14

Batman

asked Feb 7 '18 at 23:09

BatmanBatman

1198

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t    

create materialized view log on t WITH PRIMARY KEY ;

create materialized view log on t WITH ROWID ;

create materialized view log on t WITH ROWID, PRIMARY KEY ;

create materialized view log on t  WITH SEQUENCE ;

create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY





CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1

    NOLOGGING

    CACHE

    BUILD IMMEDIATE 

    REFRESH FAST ON COMMIT 

    AS 

        SELECT *

        FROM LLATTRDATA D

        WHERE 

            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR

            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR

            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR

            (D.DEFID = 3072256 AND D.ATTRID = 5);



BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'me',

    tabname => 'MV_LLATTRDATA_TEST1');

END;

oracle oracle-11g-r2 view

share|improve this question

edited Feb 7 '18 at 23:14

Batman

asked Feb 7 '18 at 23:09

BatmanBatman

1198

create materialized view log on t    

create materialized view log on t WITH PRIMARY KEY ;

create materialized view log on t WITH ROWID ;

create materialized view log on t WITH ROWID, PRIMARY KEY ;

create materialized view log on t  WITH SEQUENCE ;

create materialized view log on t WITH (column)

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY





CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1

    NOLOGGING

    CACHE

    BUILD IMMEDIATE 

    REFRESH FAST ON COMMIT 

    AS 

        SELECT *

        FROM LLATTRDATA D

        WHERE 

            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR

            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR

            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR

            (D.DEFID = 3072256 AND D.ATTRID = 5);



BEGIN

  DBMS_STATS.gather_table_stats(

    ownname => 'me',

    tabname => 'MV_LLATTRDATA_TEST1');

END;

share|improve this question

edited Feb 7 '18 at 23:14

Batman

asked Feb 7 '18 at 23:09

BatmanBatman

1198

share|improve this question

edited Feb 7 '18 at 23:14

Batman

asked Feb 7 '18 at 23:09

BatmanBatman

1198

asked Feb 7 '18 at 23:09

BatmanBatman

1198

asked Feb 7 '18 at 23:09

BatmanBatman

1198

1 Answer
1

active

oldest

votes

0

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

1. WITH ROWID


2. WITH PRIMARY KEY


3. WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm

share|improve this answer

answered Jan 24 at 16:31

VaibhavVaibhav

1

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f197344%2fwhy-create-a-materialized-view-log-with-primary-key-or-rowid-etc%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

1. WITH ROWID


2. WITH PRIMARY KEY


3. WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm

share|improve this answer

answered Jan 24 at 16:31

VaibhavVaibhav

1

add a comment | 

0

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

1. WITH ROWID


2. WITH PRIMARY KEY


3. WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm

share|improve this answer

answered Jan 24 at 16:31

VaibhavVaibhav

1

add a comment | 

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

1. WITH ROWID


2. WITH PRIMARY KEY


3. WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table.
In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below
CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES
This will ensure you have fast refresh working.

These are the oracle pages i have gone through
https://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6003.htm

share|improve this answer

answered Jan 24 at 16:31

VaibhavVaibhav

1

share|improve this answer

answered Jan 24 at 16:31

VaibhavVaibhav

1

answered Jan 24 at 16:31

VaibhavVaibhav

1

answered Jan 24 at 16:31

VaibhavVaibhav

1