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

i using oracle 11g 2.0.1.0 spatial , oracle sql developer on client. have table places primary key id , view coordinates tw columns: id referencing post in places, , sdo geometry point.

i want create materialized view following sql:

create materialized view placecoordinates nocache noparallel build immediate using index refresh on demand complete  disable query rewrite  select places.id, coordinates.point places left outer join coordinates on places.id = coordinates.id order places.id 

this gives me error:

ora-30373: object data types not supported in context

no matter sort on (even if silly 1) same error. however, if remove order by statement works fine. works fine sorting if ordinary select without creating materialized view.

why wouldn't able sort? there anyway around problem?

the key thing order in materialized view makes no sense.

under covers, materialized view table gets automatically updated when tables based on updated. being table means no ordering can ever guaranteed. though initial mv gets stored in desired order, there no guarantee remain after updates applied. way make sure results in proper order use explicit order when select mv.

you can include order view (not materialized view), , applied when use view: selects view not need order by. very bad practice. means applications may unknowingly depend on assumed order provided view - until decides remove order view , hell breaks loose.

the key is: if application requires result in specific order, must in selects issues, including order by.

that said, looking @ mv definition, looks never updated changes take place on base tables (places , coordinates): "refresh on demand complete". in other words (or automatic process) triggers full refresh @ regular intervals. same creating new table. might this:

create table placecoordinates select places.id, coordinates.point places left outer join coordinates on places.id = coordinates.id; 

and run every time want refresh placecoordinates table (after dropping old table). simpler , more efficient mv machinery. approach create table once, truncate , fill when necessary:

create table placecoordinates (   id number primary key,   point sdo_geometry ); 

and

truncate table placecoordinates; insert placecoordinates (id, point) select places.id, coordinates.point places left outer join coordinates on places.id = coordinates.id; 

that lets specify id primary key - idea. , of course, don't forget define proper spatial index on point column (assuming want show points on map or query them). practice first drop index before refresh content , recreate afterwards (you need mv approach).

whatever chosen approach (mv specify it, or table), placecoordinates not reflect real-time state of places , coordinates tables. reflect state @ last time manually refreshed mv or re-loaded table. if acceptable, set.

if want placecoordinates closer state of other 2 tables, without having refresh/reload it, every minute, need define mv refreshed changes in source tables. means need materialized view log on tables changes recorded in order applied on mv. happen @ intervals specify or when manually request refresh. reasonably not more every minute. not every second.

if placecoordinates must reflect changes in places , coordinates happen (= in "real time"), way guarantee make table , have triggers on places , coordinates automatically apply changes on tables on placecoordinates happen.

maybe in case better off reading directly base tables.