conversations for speed dating - Updating a materialized view in oracle

by  |  13-Feb-2020 11:37

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV

There are two types of materialized view logs: timestamp-based materialized view logs and commit SCN-based materialized view logs.If PCT refresh is possible, it will occur automatically and no user intervention is required in order for it to occur.When updating an existing record, is it deleted from a materialized view and then re-inserted?A master table can have only one materialized view log defined on it.Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. Usually, a fast refresh takes less time than a complete refresh.

"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV

There are two types of materialized view logs: timestamp-based materialized view logs and commit SCN-based materialized view logs.

If PCT refresh is possible, it will occur automatically and no user intervention is required in order for it to occur.

When updating an existing record, is it deleted from a materialized view and then re-inserted?

A master table can have only one materialized view log defined on it.

"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA[[

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

||

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

]]"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV[[

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

||

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

]]"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA[[

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

||

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

]]"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV[[

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

||

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

]]"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV[[

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option? Having a fact table that is partitioned offers two additional benefits for materialized views.There is much seeded functionality around monitoring tables which the materialized view is dependent on using materialized view log files (explicitly designed for this purpose).

||

@lastplan PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- SQL_ID 9wwp2am6pm4dz, child number 2 ------------------------------------- select channel_id,sum(amount_sold) from sales group by channel_id Plan hash value: 2525395710 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 18 (100)| | | 1 | VIEW | | 363 | 9438 | 18 (23)| | | 2 | UNION-ALL | | | | | | |* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 7 (15)| | |* 4 | HASH JOIN FULL OUTER | | 100 | 4300 | 7 (15)| | | 5 | VIEW | | 5 | 160 | 3 (0)| | | 6 | MAT_VIEW ACCESS FULL | MV1 | 5 | 60 | 3 (0)| | | 7 | VIEW | | 100 | 1100 | 4 (25)| | | 8 | HASH GROUP BY | | | | 4 (25)| | |* 9 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | | 10 | VIEW | | 263 | 6838 | 11 (28)| | | 11 | UNION-ALL | | | | | | |* 12 | FILTER | | | | | | | 13 | NESTED LOOPS OUTER | | 250 | 16000 | 4 (25)| | | 14 | VIEW | | 100 | 5200 | 4 (25)| | |* 15 | FILTER | | | | | | | 16 | HASH GROUP BY | | | | 4 (25)| | |* 17 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | |* 18 | INDEX UNIQUE SCAN | I_SNAP$_MV1 | 3 | 36 | 0 (0)| | | 19 | MERGE JOIN | | 13 | 871 | 7 (29)| | | 20 | MAT_VIEW ACCESS BY INDEX ROWID| MV1 | 5 | 60 | 2 (0)| | | 21 | INDEX FULL SCAN | I_SNAP$_MV1 | 5 | | 1 (0)| | |* 22 | FILTER | | | | | | |* 23 | SORT JOIN | | 100 | 5500 | 5 (40)| | | 24 | VIEW | | 100 | 5500 | 4 (25)| | | 25 | SORT GROUP BY | | | | 4 (25)| | |* 26 | TABLE ACCESS FULL | MLOG$_SALES | 2 | 74 | 3 (0)| | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("AV$0"."OJ_MARK" IS NULL) 4 - access(SYS_OP_MAP_NONNULL("SNA$0"."CHANNEL_ID")=SYS_OP_MAP_NONNULL("AV$0"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

]]"."GB0")) 9 - filter("MAS$"."SNAPTIME$$"TO_DATE(' 2017-01-05 ', 'syyyy-mm-dd hh24:mi:ss')) 12 - filter(CASE WHEN ROWID IS NOT NULL THEN 1 ELSE NULL END IS NULL) 15 - filter(SUM(DECODE(DECODE("MAS$"."OLD_NEW$$",'N','I','D'),'I',1,(-1))) Isn’t it nice that also the good old stuff gets enhanced instead of only the fancy new things like the In-Memory Option?

The fact that only some partitions have changed, due to a DML or a partition maintenance operation, is useful for: Partitioning and Query Rewrite When a certain partition of the detail table is updated, only specific sections of the materialized view are marked stale.

The materialized view must have information that can identify the partition of the table corresponding to a particular row or group of the materialized view.

Timestamp-based materialized view logs use timestamps and require some setup operations when preparing to refresh the materialized view.

Commit SCN-based materialized view logs use commit SCN data rather than timestamps, which removes the need for the setup operations and thus can improve the speed of the materialized view refresh.

To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.

Community Discussion