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

The main thing is that ORDER BY in materialized form does not make sense.

Under the covers, a materialized view is just a table that automatically updates when tables are based on an update. But the presence of the table means that no order can be guaranteed. While the original MV is kept in the desired order, there is no guarantee that it will remain the same once the updates are applied. The only way to make sure you get the result in the correct order is to use an explicit ORDER BY when selecting from MV.

You can include ORDER BY on a view (not a materialized view) and that will apply when you use that view: selects from the view and then doesn't require ORDER BY. But this practice is VERY BAD . This means that applications can unknowingly depend on some supposed ordering provided by the view - until someone decides to remove the ORDER BY from the view and all hell breaks down.

Key: if the application requires the result in a specific order, then it must say so in the SELECT it produces by including the ORDER BY.

However, looking at your definition of MV, it looks like it will never update as the underlying tables (PLACES and COORDINATES) change: you say it is "REFRESH ON DEMAND COMPLETE". In other words, you (or some kind of automated process) run a full update at regular intervals. This is exactly the same as creating a new table. You can also do this:

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

      

        
        
        
      

    

and run it every time you want to update the PLACECOORDINATES table (after dropping the old table). It will be easier and more efficient than MV equipment. Another approach is to create the table once, then truncate and fill it as needed:

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;

      

        
        
        
      

    

This allows you to specify that the identifier is the primary key - always a good idea. And, of course, don't forget to define the correct spatial index on the POINT column (assuming you want to show or query points on the map). It is good practice to drop this index first, before updating the content and recreating it afterwards (this is necessary for the MV approach as well).

Regardless of which approach you choose (MV, as you specify, or tables), PLACECOORORDINATES will not reflect the state of the PLACEES and COORDINATES tables in real time. It will only show the status of the last time you manually fully updated the MV or reloaded the table. If that's acceptable, then you're all set.

If you want PLACECOORDINATES closer to the state of the other two tables, without having to completely update / reload it, say every minute, then you need to define the MV so that it only updates from changes in the source tables. This means that you need a MATERIALIZED VIEW LOG on the tables where the changes will be recorded to be applied to the MV. This will only happen at the intervals you specified or when you manually request an update. But no more reasonable than every minute. Of course, not every second.

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

You might be better off just reading directly from the underlying tables in this case.