13

From the documentation: A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access ...


12

You need to use the NOEXPAND query hint (I'm assuming you're on Standard Edition of SQL Server, as Enterprise won't expand by default) in order for your query to use the Indexed View (instead of expanding it to the underlying table). This will solve your first issue. This is how you can use it in your query: SELECT TOP (100) * FROM Losses_CombinedPortfolio ...


7

This works as you want on Enterprise/Developer Edition: use tempdb go CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20)); GO CREATE OR ALTER VIEW dbo.V WITH SCHEMABINDING AS SELECT T.Id, T.txt FROM dbo.T AS T WHERE T.b = 1; GO CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id); GO UPDATE V SET txt = 'Foo'; The update has this ...


4

Regarding when you query the underlying table, perhaps the optimizer produces a plan that uses the view-index instead of the table? The execution plan should tell you, or try the EXPAND VIEWS hint.


4

Why bother at all with a new column, when you can just check LAST_REFRESH_DATE in ALL_MVIEWS before executing the actual query. ALL_MVIEWS And there is basic/advanced query rewrite as well.


3

ALTER TABLE ... SWITCH would allow you to switch in each table when you want to change the "live" table. The only requirement is to ensure both tables involved in the switch are in the same filegeoup, and have the same structure.


3

A unique constraint on a NOT NULL column (which you have, as I checked this table in a database where Maximo was installed) is a good enough substitute for a primary key in this case. SQL> create table t1(c1 number, c2 number); Table created. SQL> create materialized view log on t1; create materialized view log on t1 * ERROR at line 1: ORA-12014: ...


3

@Philᵀᴹ's second function attempt is vulnerable to SQL injection by using: SELECT refresh_mv_xxx('example_mview_name with data; TRUNCATE TABLE example_table_name CASCADE; REFRESH MATERIALIZED VIEW example_mview_name') where example_mview_name and example_table_name refer to any of your materialized views and tables, respectively. Which is dangerous in ...


2

Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index? Your view definition is being expanded. You'll need the NOEXPAND hint. Another Reason ...


2

The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation: "To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege." So ...


2

That's the way materialized views work. They essentially create a copy of the results of the query in the view. The base table(s) used in that query are automatically updated when the base table changes. That is implemented via a trigger on the base table that records all changes into a materialized view log associated to that base table. A subsequent ...


2

I have the Quantity indexed on both tables because I obviously wish to sort and filter on this column, but of course the index doesn't help if I want to sort and filter on vwThingAugmented. Based on your view and the comment on filtering I am going to assume queries like this: SELECT * FROM dbo.vwThingAugmented WHERE Quantity = 5; Where there is a filter ...


1

All your assumptions are correct. You need to ANALYZE the materialized view to get correct estimates – this should be done automatically by autovacuum, so I am not sure what could be wrong there. It also looks like your materialized view is terribly bloated. You would need to run VACUUM (FULL) on it to fix that. Both point in the direction of autovacuum not ...


1

So you just made me realize there's a use case for static Materialized Views due to the decoupling of dependencies in Oracle, resulting in reduced locking issues in a scenario such as your own. (Though when you refresh a materialized view in Oracle, I'm not sure how performant its process is vs loading a second staging table?) I concur with MBuschi's idea of ...


1

There is no such thing as a "nested table" in Postgres, but it seems you just want a JSON representation of the group assignments for each user. The following query does this: select u.user_id, u.title, jsonb_build_object( 'user_id', u.user_id, 'title', u.title, 'groups', jsonb_agg(...


1

If the controlling scheduled DBMS_JOB job fails, it may need to be manually reset/restarted. If you want more flexibility/resiliency with this, use Oracle Scheduler to control the refresh execution, rather than the default DBMS_JOB. Do this by leaving the start and next portions out of the MV definition: create materialized view target.wo_mv build immediate ...


1

Those are the remains of an ancient feature called updatable snaphots that later became updatable materialized views. Originally materialized views were called snapshots. The values for Update Trigger and Update Log come from the UPDATE_TRIG and UPDATE_LOG columns of ALL|DBA|USER_SNAPSHOTS, a view that was last documented properly in Oracle 8i: ALL_SNAPSHOTS....


1

As already demonstrated in your other question, a unique constraint on a NOT NULL column is good enough to create an mview log with "PK" values. That can be used for fast refresh. SQL> conn bp/bp Connected. SQL> create database link self connect to bp identified by bp using 'localhost:1521/RYMIN19'; Database link created. SQL> ...


1

Try basing your MV directly on the source table and not on an intermediate view. Include your "isgis=1" condition in the MV query. create materialized view primary_system.workorder_mv build immediate refresh force on demand start with sysdate next sysdate + (30/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, ...


1

Naming conflicts will result in an error and generally cause the object in the datapump file to be skipped. This can lead to all sorts of other dependency issues. The simplest way to avoid that is to drop the target application schema/account entirely and recreate it, using a script like the one here: https://oracle-base.com/dba/script?category=...


1

Updatable materialized view was an Advanced Replication feature. A writable materialized view was simply an updatable materialized view that was not a member of a materialized view (replication) group. Desupport of Advanced Replication Starting in Oracle Database 12c release 2 (12.2), the Advanced Replication feature of Oracle Database is desupported. The ...


1

For that kind of need, I like to build a SSIS package that will copy the content of the view to a different SQL DB (on a different instance if I want to make sure their reporting won't hurt the prod DB). You can then schedule the data refresh when you want and you can also create index that would make their reports run faster.


1

Thinking about the requirements: "quickly get access to the data without overloading the DB" "pull them what was made static [at] the time the view was created" Especially the part about the data being static captured at a moment in time, I doubt a view is what you want. I think you may want something more like CDC, but that wasn't ...


Only top voted, non community-wiki answers of a minimum length are eligible