Sfrgttk

How can my powered armor quickly replace its ceramic plates?

Why did Luke use his left hand to shoot?

Why am I able to open Wireshark in macOS without root privileges?

How old is the day of 24 equal hours?

Is there a weight limit to Feather Fall?

Cat is tipping over bed-side lamps during the night

Can we harness gravitational potential energy?

Increment each digit in a number to form a new number

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

Using only 1s, make 29 with the minimum number of digits

What is the wife of a henpecked husband called?

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

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

Eww, those bytes are gross

Am I a Rude Number?

Play Zip, Zap, Zop

What are "industrial chops"?

What's a good word to describe a public place that looks like it wouldn't be rough?

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

If I delete my router's history can my ISP still provide it to my parents?

Nested word series [humans only]

Do theoretical physics suggest that gravity is the exchange of gravitons or deformation/bending of spacetime?

Is there any risk in sharing info about technologies and products we use with a supplier?

What to look for when criticizing poetry?

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♦ 7 mins 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♦ 7 mins 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