Questions tagged [materialized]

61 questions
0
votes
1 answer

Is it possible to list indexes created on materialized views in postgresql

I am able to to list all indexes on tables by using pg_indexes. This however, does not list the indexes created on the materialized view. Neither does the pgadmin3 frontend show the indexes on materialized views. Is there a way?
anil
  • 598
  • 1
  • 6
  • 20
0
votes
2 answers

postgres: partial refresh on materialized view?

I have this materialized view: Materialized view "public.kwh_tag" Column | Type | Collation | Nullable | Default | Storage | Stats target |…
fsp
  • 515
  • 6
  • 21
0
votes
0 answers

DBT {materialized = "view"} creates a table in the database

Trying to understand DBT Materialization strategies - Some of our models are using the {materialized = "view"} option, and still I see that an underlying table is created in the database (We are using AWS Redshift) Documentation says that: "When…
0
votes
1 answer

Can a materialized view in Netezza be created by the union of 2 base tables

I have 2 or more base tables representing subtype entities. The records in these are mutually exclusive - no intersection. I want to create a materialized view which represents the supertype entity. Can this be done in Netezza using a UNION ALL in…
0
votes
0 answers

Recreate Oracle Materialized View failed after drop

I have a Materialized View that is built by joining tables loaded by external party every day, with potential different structure (such as new columns). To adapt table structure changes, I built a daily batch that drops and recreates the…
0
votes
0 answers

Oracle Materialized Views and Extended Statistics (ORA-12048 error encountered while referencing materialized view)

We added a materialized view to our application to help speed up a grid that shows aggregate information. The view really improved performance dramatically, which was great. However, for some reason, Oracle (12c) creates a virtual column to one of…
PDM
  • 503
  • 2
  • 12
  • 27
0
votes
2 answers

ORA-00600 when Creating Materialized View

I'm trying to create a materialized view like using the following: CREATE MATERIALIZED VIEW orders_mv AS SELECT * FROM (SELECT * FROM table1 UNION SELECT * FROM table2 ORDER BY DBMS_RANDOM.VALUE) WHERE rownum <= 5; But…
Landon Statis
  • 683
  • 2
  • 10
  • 25
0
votes
0 answers

Connection refused when trying connecting to postgresql

#Edit Sorry its not connect to PostgreSQL but im trying to connect to materialized shell but it's getting an error like below psql: could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting …
Lazz
  • 1
  • 1
0
votes
1 answer

Materialized view creation is fast but refresh takes hours on Oracle 19c

Recently I need to create several materialized views on Oracle 19c, all of the base tables are locate on a remote oracle database, the query uses dblink to connect the tables, and fast refresh is not allowed. Most of them can complete refreshment in…
Shawn
  • 1
  • 3
0
votes
1 answer

Why is DDL for Materialized view giving error?

I am trying to create a materialized view as below and getting error, kindly advise. Oracle version: 12.2.0.1.0 CREATE MATERIALIZED VIEW EDW_TEST_MV REFRESH FAST ON DEMAND ENABLE QUERY REWRITE AS ( SELECT distinct TXN_REF, ACQ_ENT_KEY,…
Senthil
  • 11
  • 1
0
votes
1 answer

Akka Stream Materialized Value

I want to reference the materialized value from the flow. Below is the code snippet, but its not compiling, error: type mismatch; found : (akka.NotUsed, scala.concurrent.Future[akka.Done]) required: (Playground.DomainObj,…
0
votes
1 answer

Why the cost time is difference between those two case about postgresql meterialized view?

everyone. In postgresql 12, i have a materialized view like this: CREATE MATERIALIZED VIEW mv AS WITH cte AS (SELECT ...), cte2 AS (SELECT ...) SELECT ... WITH DATA; when i invoke REFRESH MATERIALIZED VIEW mv, or perform this command in linux…
0
votes
1 answer

for Materialized View in Cassandra, is it possible the read query returns different rows between by java driver and by cql

We have Materialized view in Cassandra, i am running a read query to get data from MV, however i find the result is different by using java driver and using cql, is it possible?
cmB
  • 13
  • 1
0
votes
2 answers

Creating Materialized View in Azure Data Warehouse SQL error

I'm getting the error: Cannot create the clustered index 'SalesByTerritory' on view 'Sample.wwi.SalesByTerritory' because it does not aggregate results. Consider changing the view definition. I'm trying to create a Materialized View in Azure SQL…
bloodfire1004
  • 493
  • 2
  • 8
  • 24
0
votes
1 answer

Oracle Database - MV Fast refresh, referencing view

I got few questions about creating materialized views with fast refresh. The problem is I need to have in the query count(*) function, which is illegal for fast refresh. I read somewhere that I make an "assistance" view, which will execute count().…
Orzelke
  • 11
  • 2