Alternative to my SQL based GetNumRings function

When creating a new PL/SQL procedure or function, where possible, I write the algorithm in SQL first. Once the SQL is done, it then becomes comparatively easy to encapsulate the SQL in a PL/SQL procedure or function for generic use.

This declarative approach is wonderful as the SQL statement can be developed independently of any programming language or framework, understood by SQL literate people who may not necessarily understand programming, and can shared with others by pasting it into an email to a colleague.

However, the SQL approach comes with a price: it is far slower than its more direct, programmatic version. This is due to the issues relating to SQL optimisation and the orchestrating of the execution between Oracle’s SQL and PL/SQL engines.

Recently I have had need to take previous, SQL based, functions and convert them to their programmatic equivalent. The GetNumRings function I previously blogged on is one I have re-written to be faster.

Here is the new version of the function.

 create or replace
 Function GetNumRings( p_geometry  in mdsys.sdo_geometry,
                        p_ring_type in integer default 0 /* 0 = ALL; 1 = OUTER; 2 = INNER */ )
   Return Number
 Is
    v_ring_count number := 0;
    v_ring_type  number := p_ring_type;
    v_elements   number;
    v_etype      pls_integer;
 Begin
    If ( p_geometry is null ) Then
       return 0;
    End If;
    If ( p_geometry.sdo_elem_info is null ) Then
       return 0;
    End If;
    If ( v_ring_type not in (0,1,2) ) Then
       v_ring_type := 0;
    End If;
    v_elements := ( ( p_geometry.sdo_elem_info.COUNT / 3 ) - 1 );
    <<element_extraction>>
    for v_i IN 0 .. v_elements LOOP
      v_etype := p_geometry.sdo_elem_info(v_i * 3 + 2);
      If  ( v_etype in (1003,1005,2003,2005) and 0 = v_ring_type )
       OR ( v_etype in (1003,1005)           and 1 = v_ring_type )
       OR ( v_etype in (2003,2005)           and 2 = v_ring_type ) Then
          v_ring_count := v_ring_count + 1;
      end If;
    end loop element_extraction;
    Return v_ring_count;
 End GetNumRings;

I have not yet run Oracle’s DBMS_PROFILER over the two functions but will do so when I get time (or someone out there reminds me of this).

I did conduct a comparison test with the previous version. The old version is called GetNumRingsSQL just for convenience.

 select a.id, 
         getnumringsSQL(a.geometry) as old_getnumrings,
         getnumrings(b.geometry) as new_getnumrings
  from Oracle_Test_Geometries a
       inner join
       Oracle_Test_Geometries b on ( b.id = a.id)
 where a.geometry is not null and a.geometry.sdo_elem_info is not null
   and getnumringsSQL(a.geometry) <> getnumrings(b.geometry) 
 order by 1 ;
  
 ID                     OLD_GETNUMRINGS        NEW_GETNUMRINGS 
 ---------------------- ---------------------- ----------------------

No rows… which is the right answer. In vestigating further.

 select a.id, 
         getnumringsSQL(a.geometry) as old_getnumrings,
         getnumrings(b.geometry) as new_getnumrings
  from Oracle_Test_Geometries a
       inner join
       Oracle_Test_Geometries b on ( b.id = a.id)
 where a.geometry is not null and a.geometry.sdo_elem_info is not null
   and getnumringsSQL(a.geometry) <> 0
 order by 1 ;
 
 ID OLD_GETNUMRINGS NEW_GETNUMRINGS      
 -- --------------- --------------- 
 11               1               1                    
 12               1               1                    
 13               1               1                    
 14               1               1                    
 15               1               1                    
 23               2               2                    
 24               2               2                    
 25               2               2                    
 26               2               2                    
 27               2               2                    
 28               2               2                    
 29               1               1                    
 30               1               1                    
 31               1               1                    
 32               3               3                    
 35               1               1                    
 36               2               2                    
 37               1               1                    
 39               2               2                    
 50               2               2                    
 51               2               2                    
 52               2               2                    
 54               1               1                    
 55               1               1                    
 59               1               1                    
 65               1               1                    
 66               1               1                    
 67               1               1                    
 141              2               2                    
 201              1               1                    
 202              9               9                    
 203             12              12                   
 204              3               3                    
 205              1               1                    
 
  34 rows selected 

I hope this helps someone.