具有相同名称的实例化视图和表 [英] Materialized view and table with the same name

问题描述

我有点理解物化视图，并且之前曾与它们一起工作过.最近出现了一个问题，即为什么特定报告没有显示最新数据，所以我调查了这个问题.显然，他们之前有一个临时表，其中装有crontab，后来又切换到了物化视图.

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.

我的问题是，物化视图是否实际上创建了一个具有相同名称的表，如果是，当我执行SELECT时，数据从何处来(表或MView)?

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)?

还是仅仅是早些时候剩下的表格?如果是这样，Oracle是否允许使用相同名称的不同类型的对象? (我真的为此感到难过，因为我认为每个对象都必须有一个唯一的名称！).

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!).

奇怪的是，如果它们是2个对象，则在下面的SQL中使用哪个对象:

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.

更新基于@Alex和@Husqvik的回复: 在Mview定义中，我看到以下内容:

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  

这是否意味着它应该每天(明天在此更新)?明天刷新后，开始于会更改为11月11日吗?

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?

是否有任何阻止刷新的事情，因为用户抱怨使用此MView在报表中看不到最新数据，所以这就是为什么我首先要看它的原因?

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?

这是MView的完整DDL:

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;

推荐答案

创建实例化视图时，Oracle数据库将在实例化视图的架构中创建一个内部表和至少一个索引，并可能创建一个视图. Oracle数据库使用这些对象来维护物化视图数据.

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.

因此具有相同名称的表和实例化视图是正常的. MV需要将数据存储在某个地方，因此拥有一个表是有意义的.然后，MV本身定义如何维护表数据.

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.

您可以使用 ON PREBUILT TABLE子句在现有表上创建视图，我假设这是他们之前有一个临时表...后来又切换到了物化视图"的意思.

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.

您还可以使用 DROP MATERIALIZED VIEW ... PRESERVE TABLE选项，将基础表留在后面.

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

SELECT * FROM TEMP_DATA;时，您正在查询基础表，但是区别并不重要，因为它们是指相同的组合对象.

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.

这篇关于具有相同名称的实例化视图和表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！