12

I kind of understand materialized views and have worked with them before. Recently a question came up as to why a particular report didn't show latest data, I looked into the issue. Apparently, they had a temp table loaded with crontab earlier and switched to Materialized view later.

When I looked into the database with the below query (name of the table changed):

SELECT * FROM all_objects WHERE object_name = 'TEMP_DATA';

This actually showed 2 objects in the same schema: one table and another materialized view

OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID LAST_DDL_TIME TIMESTAMP SCHEMA TEMP_DATA TABLE 110623 08/06/2013 15:38 2013-08-06:14:53:01 SCHEMA TEMP_DATA MATERIALIZED VIEW 10/30/2015 00:00 2013-08-06:14:56:33

And, when I try to alter the table to rename it, it said materialized view cannot be renamed.

My question is, whether a materialized view actually creates a table with the same name and if so, when I do SELECT where does the data come from (table or MView)?

Or is it just the leftover table from earlier times? If so, does Oracle allow different type of objects with the same name? (I was really stumped by this, as I thought every object had to have a unique name!).

And just curious, if they are 2 objects, which one is used in the below SQL:

SELECT * FROM TEMP_DATA;

Any insight into it, much appreciated.

UPDATE based on @Alex and @Husqvik's responses: In the Mview definition, I see below: 

BUILD IMMEDIATE REFRESH COMPLETE START WITH TO_DATE('06-Nov-2015','dd-mon-yyyy') NEXT trunc(sysdate) + 1 WITH PRIMARY KEY

Does that mean it should update everyday (here tomorrow)? Will the START WITH change to 07-Nov after tomorrow's refresh?

And, does it automatically refresh and if so, when does it do the refresh?

Is there anything that would prevent it from refreshing, because the user complained they don't see the latest data in a report using this MView and that's why I got this to look at this in the first place?

Here is the full DDL for the MView:

DROP MATERIALIZED VIEW SCHEMA.TEMP_DATA; CREATE MATERIALIZED VIEW SCHEMA.TEMP_DATA TABLESPACE ITS_DATASPACE PCTUSED 0 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOCACHE LOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH COMPLETE START WITH TO_DATE('06-Nov-2015','dd-mon-yyyy') NEXT trunc(sysdate) + 1 WITH PRIMARY KEY AS /* Formatted on 2015/11/05 09:35 (Formatter Plus v4.8.8) */ SELECT * FROM SCHEMA.h_case_data WHERE status LIKE 'M%'; COMMENT ON MATERIALIZED VIEW SCHEMA.TEMP_DATA IS 'snapshot table for snapshot SCHEMA.TEMP_DATA'; CREATE INDEX SCHEMA.CASE_ID_IDX ON SCHEMA.TEMP_DATA (CASE_ID) LOGGING TABLESPACE ITS_DATASPACE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL; CREATE INDEX SCHEMA.STATUS_IDX ON SCHEMA.TEMP_DATA (STATUS) LOGGING TABLESPACE ITS_DATASPACE PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL;
Improve this question

2 Answers 2

Reset to default
14

From the documentation:

When you create a materialized view, Oracle Database creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle Database uses these objects to maintain the materialized view data.

So having the table and materialized view with the same name is normal. The MV needs to store the data somewhere, so having a table makes sense; the MV itself then defines how the table data is maintained.

You can use the ON PREBUILT TABLE clause to create a view over an existing table, which I assume is what "they had a temp table earlier ... and switched to Materialized view later" refers to.

You can also go the other way, with the DROP MATERIALIZED VIEW ... PRESERVE TABLE option, which leaves the underlying table behind.

When you SELECT * FROM TEMP_DATA; you're querying the underlying table, but the distinction isn't really important as they refer to the same combined object.

Based on the definition to added to the question later, it will refresh every day at midnight.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Oracle doesn't create any index just because of materialized view.
@Husqvik - I'm just quoting the docs *8-) The 12c version still says the same thing. But no, it doesn't always; it will if you specify refresh with rowid I think.
Thanks Alex and Husqvik for the quick response. In the Mview definition, I see below:
Now, I know something about MViews, I have a few more questions. if I may, can you please help me understand a bit more? Sorry my comment became too long, so I updated my original question. Thank you both!
@SamV - it will refresh every day at midnight. Did you recreate it today - and if so do you have the previous definition? Could it have been [altered]() to manual refresh, maybe? Otherwise, can you see sample data in the view to compare to the report - maybe it's an old report and the creation of the report itself has a problem. Just a thought.
|
3

There are two schema objects. Physically the materialized view is a table. The materialized view objects contains the metadata about the refresh mode, type, method and other properties. If you check the ALL_OBJECTS you see that MATERIALIZED VIEW object doesn't have its segment. Data dictionary secures that you cannot treat the underlying table as normal table otherwise it can break the consistency between these coupled objects.

You also can create a materialized view on top of a prebuilt table.

UPDATE:

START WITH will update with every refresh. NEXT START is evaluated when view is created or refreshed.

Refresh can fail on any error that can normally occur in the database, e. g, not enough space, lock timeout, changes of the query underlying objects.

Improve this answer

1 Comment

Thanks! I still had the nagging question about how that refresh is kicked off. I see it now. SELECT log_user, last_date, next_date, INTERVAL, what FROM dba_jobs WHERE schema_user = 'SCHEMA'; LOG_USER LAST_DATE NEXT_DATE INTERVAL WHAT SCHEMA 11/05/2015 00:00:03 11/06/2015 00:00:00 trunc(sysdate) + 1 dbms_refresh.refresh('"SCHEMA"."TEMP_DATA"'); Thanks a lot again. I learned a few things today!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.