Is possible create the view materialized in DBMaker? (DBMR3096)
Materialized view is the physical copy of the original base tables. Unlike view, the materialized view is precomputed and stored on a disk, and they are not updated each time they are used. DBMaker doesn't support materialized view, so we give some assumptions for each workaround.
1. Do materialized view: "refresh complete" + "on commit"
(a) Try to create a temp/permanent table (mv1), if table exists, do (b).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) which delete all records from mv1, and do (b).
(d) After insert/update/delete trigger on table A or table B to call mvsp1.
2. Do materialized view: "refresh complete" + "start with・next"
(a) Try to create a temp/permanent table (mv1).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) which delete all records from mv1, and do (b).
(d) Create task on schedule daemon to execute call mvsp1.
3. Do materialized view: "refresh complete" + "on demand"
(a) Try to create a temp/permanent table (mv1).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) which delete all records from mv1, and do (b).
(d) Call mvsp1 on demand.
4. Do materialized view: "never refresh" + "on demand"
The same as 3, maybe user wants this.
5. Do materialized view: "refresh fast/refresh force" + "on commit" (PK cannot be updated)
(a) Try to create a temp/permanent table (mv1).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)
(d) After insert/update/delete trigger on table A or table B to call mvsp1.
6. Do materialized view: "refresh fast/refresh force" + "start with・next" (PK cannot be updated)
(a) Try to create a temp/permanent table (mv1).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)
(d) Create task on schedule daemon to execute call mvsp1.
7. Do materialized view: "refresh fast/refresh force" + "on demand" (PK cannot be updated)
(a) Try to create a temp/permanent table (mv1).
(b) As view definition, insert data to mv1 using "select into".
(c) Create a sp (mvsp1) to check insert/update/delete operations and insert/update/delete mv1. (not easy)
(d) Call mvsp1 on demand.