I was recently looking to re-factor some rather nasty PL/SQL – granted it’s not something I would say I enjoy necessarily, but occasionally these things have to be done right… Anyway back to point, there was a rather nasty piece of dynamic SQL that was built up to query based on decoded values from another query. You have probably seen the sort of thing before – the example below shows the approach; select some encoded value (in this case a postcode based on spatial restriction) from one structure, then for each value found, decode it, and add another where clause restriction.

FOR rec IN (SELECT ps.sector
    FROM postcode_sectors ps, test_extent te
    WHERE te.key_id = p_key_id
    AND SDO_RELATE(ps.shape,  te.extent, 'mask = anyinteract') = 'TRUE')
LOOP
    utils.split_postcode(rec.sector, area, district, sector);
    postcodeWhere := postcodeWhere || '(area = ''' || area || '''' ||
          
        ' AND district = ''' || district || '''' ||
        ' AND sector = ''' || sector || '''' || ') OR ';
END LOOP;

One you have finished with the loop you would trim off the final Or and use the restriction in another query. This is often used as a solution when you have data from different sources with different encodings, it’s not pretty, but it works.

So I stumbled across an interesting feature in Oracle called pipelined functions that allows a PL/SQL function to be queried like a standard table. So in this case the function would present itself as the decoded values of postcode. For the meat of the code it was a really easy re-factor following the documentation – the following shows the inside of the loop after re-factor.

FOR rec IN (SELECT ps.sector
   FROM postcode_sectors ps, test_extent te
   WHERE te.key_id = p_key_id
   AND SDO_RELATE(ps.shape,  te.extent, 'mask = anyinteract') = 'TRUE')
LOOP
   UTILS.SPLIT_POSTCODE(rec.sector,area,district,sector);
   PIPE ROW( SPLIT_POSTCODE_TYPE(area,district,sector) );
END LOOP;

This worked really well, and whilst I left (for the first pass re-factor at least) the executed query as dynamic SQL, it looked far better, the intention was much clearer.

There was a downer however, it performed like an absolute pig. Thinking “first it giveth then taketh away” I had a quick look at he execution plan, and bit of a research to see if there was anything that could be done. Fortunately I found a very well written and thorough article describing the reasons for the performance issue and giving suggestions for setting cardinality of the pipelined functions to improve performance. I won’t describe the solution, other than saying I chose the DYNAMIC_SAMPLING optimizer hint!