Database Software

Hi,

How do you split multiploygon records into many polygon records.

The SQL below performs what I want to do for a single feature:

SELECT id,
mdsys.sdo_util.Extract(i.g3e_geometry,level,0),
level
FROM (select * from parcel_view_geo1 p where p.g3e_geometry.get_gtype() = 7 and p.id = 287413011) i
CONNECT BY LEVEL <= mdsys.sdo_util.GetNumElem(i.g3e_geometry);

But when I remove the id filter the query runs extremely slow and seems to do a full table scan for each level (i.e. if there is a 60 part multiplepolygon feature the query will do 60 full tables scans).

SELECT id,
mdsys.sdo_util.Extract(i.g3e_geometry,level,0),
level
FROM (select * from parcel_view_geo1 p where p.g3e_geometry.get_gtype() = 7) i
CONNECT BY LEVEL <= mdsys.sdo_util.GetNumElem(i.g3e_geometry);

It is probably not good to put a function call in the 'connect by', but is there a way to give a hint in the query to improve the performance?

I thought about using a pipelined function, but am not sure if it is suitable. Is there another approach altogether?

Thanks for any suggestions.