How to count the number of elements in all Oracle varrays tables?

Question

I have a table like this:

CREATE TABLE spatial_data ( id NUMBER PRIMARY KEY, geometry SDO_GEOMETRY); 

SDO_GEOMETRY has an sdo_ordinates field with the following type:

TYPE SDO_ORDINATE_ARRAY AS VARRAY (1048576) NUMBERS

I can get the number of points for the specified object:

 select count(*) from table( select s.geometry.sdo_ordinates from spatial_data s where s.id = 12345 ); 

How do I count the number of objects? Unable to use

 where s.id in (1, 2, 3, 4, 5) 

And I really care about performance. Maybe PL / SQL would be the right choice?

Answer 1

I think you can do this with a single request:

 select s.id, count(*) from spatial_data s, table(s.geometry.sdo_ordinates) group by s.id 

or you can write a simple plsql function that returns the count attribute for SDO_ORDINATE_ARRAY VARRAY:

 create or replace function get_count(ar in SDO_ORDINATE_ARRAY) return number is begin return ar.count; end get_count; 

or even better add a member function to SDO_GEOMETRY TYPE that return the count attribute