Can not use ORDER BY when creating materialized view with SDO geometry in Oracle 11g

Question

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?

Answer 1

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.