oracle 物化视图和同名表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33552237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Materialized view and table with the same name
提问by SamV
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.
我有点理解物化视图并且以前曾使用过它们。最近出现了一个关于为什么特定报告没有显示最新数据的问题，我调查了这个问题。显然，他们之前有一个加载了 crontab 的临时表，后来切换到了物化视图。
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
这实际上显示了同一模式中的 2 个对象：一个表和另一个物化视图
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 SELECTwhere does the data come from (table or MView)?
我的问题是，物化视图是否真的创建了一个同名的表，如果是这样，当我做SELECT数据时，数据来自哪里（表或 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!).
或者它只是早期的剩余桌子？如果是这样，Oracle 是否允许具有相同名称的不同类型的对象？（我真的被这难住了，因为我认为每个对象都必须有一个唯一的名称！）。
And just curious, if they are 2 objects, which one is used in the below SQL:
只是好奇，如果它们是 2 个对象，那么在下面的 SQL 中使用了哪一个：
SELECT * FROM TEMP_DATA;
Any insight into it, much appreciated.
对它的任何见解，非常感谢。
UPDATEbased on @Alex and @Husqvik's responses: In the Mview definition, I see below:
UPDATE基于@Alex和@ Husqvik的回应：在MView的定义，我见下图：
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 WITHchange to 07-Nov after tomorrow's refresh?
这是否意味着它应该每天更新（明天在这里）？请问START WITH变化到07 - 11月明天的刷新之后？
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?
有没有什么会阻止它刷新，因为用户抱怨他们没有看到使用这个 MView 的报告中的最新数据，这就是为什么我首先要查看这个？
Here is the full DDL for the MView:
这是 MView 的完整 DDL：
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;
回答by Alex Poole
从文档：
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.
创建物化视图时，Oracle 数据库会创建一个内部表和至少一个索引，并且可能会创建一个视图，所有这些都在物化视图的模式中。Oracle 数据库使用这些对象来维护物化视图数据。
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.
所以有同名的表和物化视图是正常的。MV 需要将数据存储在某处，所以有一个表是有意义的；然后 MV 本身定义了如何维护表数据。
You can use the ON PREBUILT TABLEclauseto 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.
您可以使用该ON PREBUILT TABLE子句在现有表上创建视图，我假设这是“他们之前有一个临时表......后来切换到物化视图”所指的。
You can also go the other way, with the DROP MATERIALIZED VIEW ... PRESERVE TABLEoption, which leaves the underlying table behind.
您也可以选择另一种方式，使用DROP MATERIALIZED VIEW ... PRESERVE TABLE选项，它将底层表抛在后面。
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.
当您SELECT * FROM TEMP_DATA;查询基础表时，但区别并不重要，因为它们引用相同的组合对象。
Based on the definition to added to the question later, it will refresh every day at midnight.
根据稍后添加到问题中的定义，它将在每天午夜刷新。
回答by Husqvik
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.
有两个模式对象。在物理上，物化视图是一张表。物化视图对象包含有关刷新模式、类型、方法和其他属性的元数据。如果您检查 ALL_OBJECTS，您会看到 MATERIALIZED VIEW 对象没有其段。数据字典确保您不能将底层表视为普通表，否则会破坏这些耦合对象之间的一致性。
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.
START WITH 将随着每次刷新而更新。创建或刷新视图时评估 NEXT START。
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.
刷新可能会因数据库中通常发生的任何错误而失败，例如。g、空间不足、锁超时、查询底层对象发生变化。
