在Oracle 11g中使用SDO几何创建实例化视图时，无法使用ORDER BY(Can not use ORDER BY when creating materialized view with S

在Oracle 11g中使用SDO几何创建实例化视图时，无法使用ORDER BY(Can not use ORDER BY when creating materialized view with SDO geometry in Oracle 11g)

我在客户端上使用Oracle 11g 2.0.1.0与Spatial和Oracle SQL Developer。 我有一个表具有主键ID和视图的位置具有tw列的Coordinates ：引用位置中的帖子的ID和SDO几何Point 。

我想用以下SQL创建一个物化视图：

CREATE MATERIALIZED VIEW PlaceCoordinates NOCACHE NOPARALLEL BUILD IMMEDIATE USING INDEX REFRESH ON DEMAND COMPLETE DISABLE QUERY REWRITE AS SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID ORDER BY Places.ID

这给了我这个错误：

ORA-30373：此上下文不支持对象数据类型

无论我选择什么（即使它只是像傻事一样），我也会得到同样的错误。 但是，如果我删除ORDER BY语句，它可以正常工作。 如果我只是在不创建物化视图的情况下执行普通SELECT它也可以正常排序。

为什么我不能排序？ 反正这个问题呢？

I am using Oracle 11g 2.0.1.0 with Spatial and Oracle SQL Developer on the client. I have a table Places with primary key ID and a view Coordinates with tw columns: ID referencing a post in Places, and the SDO geometry Point.

I want to create a materialized view with the following SQL:

CREATE MATERIALIZED VIEW PlaceCoordinates NOCACHE NOPARALLEL BUILD IMMEDIATE USING INDEX REFRESH ON DEMAND COMPLETE DISABLE QUERY REWRITE AS SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID ORDER BY Places.ID

This gives me this error:

ORA-30373: object data types are not supported in this context

No matter what I sort on (even if it is just something silly like 1) I get the same error. However, if I remove the ORDER BY statement it works fine. It also works fine with sorting if I just do an ordinary SELECT without creating a materialized view.

Why wouldn't I be able to sort? Is there anyway around this problem?

最满意答案

关键是物化视图中的ORDER BY毫无意义。

在封面下，物化视图实际上只是一个表，它在基于它的表更新时自动更新。 但作为一张桌子意味着无法保证订购。 即使初始MV以所需顺序存储，也不能保证在应用更新后它将保持不变。 确保以正确顺序获得结果的唯一方法是在从MV中选择时使用显式ORDER BY。

您可以在视图（不是物化视图）中包含ORDER BY，并且在使用该视图时将应用ORDER BY：从视图中选择，然后不需要任何ORDER BY。 但这是一个非常糟糕的做法。 这意味着应用程序可能在不知不觉中依赖于视图提供的某些假定顺序 - 直到有人决定从视图中删除ORDER BY并且所有地狱都松散。

关键是：如果应用程序需要特定顺序的结果，那么它必须在它发出的SELECT中通过包含ORDER BY来说明。

也就是说，看看你的MV定义，它似乎永远不会更新，因为基表（PLACES和COORDINATES）发生了变化：你说它是“REEMESH ON DEMAND COMPLETE”。 换句话说，您（或某些自动过程）会定期触发完全刷新。 这与创建新表完全相同。 你不妨这样做：

CREATE TABLE PlaceCoordinates AS SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID;

并在每次要刷新PLACECOORDINATES表时（在删除旧表之后）运行此命令。 它比MV机械更简单，更有效。 另一种方法是创建一次表，然后在必要时截断并填充它：

CREATE TABLE PlaceCoordinates ( ID NUMBER PRIMARY KEY, Point SDO_GEOMETRY );

和

TRUNCATE TABLE PlaceCoordinates; INSERT INTO PlaceCoordinates (ID, Point) SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID;

这允许您指定ID是主键 - 总是一个好主意。 当然，不要忘记在POINT列上定义适当的空间索引（假设您要在地图上显示点或查询它们）。 然后，良好的做法是在刷新内容之前首先删除该索引并在之后重新创建它（您还需要MV方法）。

无论选择何种方法（指定MV或表），PLACECOORDINATES都不会反映PLACES和COORDINATES表的实时状态。 它只会反映上次手动完全刷新MV或重新加载表时的状态。 如果这是可以接受的，那么你就完全了。

如果你希望PLACECOORDINATES更接近其他两个表的状态，而不必完全刷新/重新加载它，比如说每分钟，那么你需要定义MV，这样它只能从源表中的更改中刷新。 这意味着您需要在这些表上需要一个MATERIALIZED VIEW LOG，其中将记录更改以便应用于MV。 但是，这只会在您指定的时间间隔或手动请求刷新时发生。 但合理的不是每分钟。 当然不是每一秒。

如果PLACECOORDINATES必须反映当地点和坐标发生时的所有变化（=“实时”），那么唯一的方法是保证它是一个表并且在PLACES和COORDINATES上有触发器会自动对这些表上的变化进行PLACECOORDINATES当他们发生。

也许在这种情况下，你最好直接从基表读取。

The key thing is that an ORDER BY in a materialized view makes no sense.

Under the covers, a materialized view is really just a table that gets automatically updated when the tables it is based on get updated. But being a table means that no ordering can ever be guaranteed. Even though the initial MV gets stored in the desired order, there is no guarantee it will remain so after updates are applied. The only way to make sure you get the results in the proper order is to use an explicit ORDER BY when you select from the MV.

You can include an ORDER BY into a view (not a materialized view), and that will be applied when you do use that view: selects from the view then do not need any ORDER BY. But this is a VERY BAD practice. It means that applications may unknowingly depend on some assumed order provided by a view - until someone decides to remove the ORDER BY from the view and all hell breaks loose.

The key is: if an application requires a result in a specific order, then it must say so in the SELECTs it issues, by including an ORDER BY.

That said, looking at your MV definition, it looks like it will never be updated as changes take place on the base tables (PLACES and COORDINATES): you say that it is "REFRESH ON DEMAND COMPLETE". In other words you (or some automatic process) triggers a full refresh at regular intervals. This is exactly the same as creating a new table. You might as well do this:

CREATE TABLE PlaceCoordinates AS SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID;

and run this every time you want to refresh your PLACECOORDINATES table (after dropping the old table). It will be simpler and more efficient than the MV machinery. Another approach is to create the table once, then truncate and fill it when necessary:

CREATE TABLE PlaceCoordinates ( ID NUMBER PRIMARY KEY, Point SDO_GEOMETRY );

and

TRUNCATE TABLE PlaceCoordinates; INSERT INTO PlaceCoordinates (ID, Point) SELECT Places.ID, Coordinates.Point FROM Places LEFT OUTER JOIN Coordinates ON Places.ID = Coordinates.ID;

That lets you specify that ID is a primary key - always a good idea. And of course, don't forget to define the proper spatial index on the POINT column (assuming you want to show the points on a map or query them). Good practice then is to first drop that index before you refresh the content and recreate it afterwards (you need that also for the MV approach).

Whatever the chosen approach (MV as you specify it, or table), PLACECOORDINATES will not reflect the real-time state of the PLACES and COORDINATES tables. It will only reflect the state at the last time you manually fully refreshed the MV or re-loaded the table. If this is acceptable, then you are all set.

If you want that PLACECOORDINATES is closer to the state of the other two tables, without having to fully refresh/reload it, say every minute, then you need to define the MV so it is refreshed only from the changes in the source tables. That means that you need a MATERIALIZED VIEW LOG on those tables where changes will be recorded in order to be applied on the MV. That however will only happen at the intervals you specify or you when manually request a refresh. But reasonably not more than every minute. Certainly not every second.

If PLACECOORDINATES must reflect all changes in PLACES and COORDINATES as they happen (= in "real time"), then the only way to guarantee that is to make it a table and have triggers on PLACES and COORDINATES automatically apply changes on those tables on PLACECOORDINATES as they happen.

Maybe in that case you are better off just reading directly from the base tables.