Handling aggregated strings in SQL reports

Well, it’s been a long time without a new post from me so I thought I’d get the ball rolling again!

Having been surfing about and happened upon Adrian Billington’s site again, I spotted something pretty nifty. With a bit of rewriting, I started to see a possible benefit to the APEX community – mostly for the handling of multiselect list values in reports.

A common approach to dealing with them is to concatenate colons to the multiselect list value returned in the bind variable and then concatenating colons to the column being searching and using INSTR. Whilst this is okay for small sets of data, it can become problematic for large ones because it will negate the index on the searched column.

Consider this example of the that approach…

SELECT *
FROM  emp
WHERE INSTR(':'||:LIST_ITEM||':',':'||empno||':')>0

Here, If you have an index on empno, this won’t be used and a full table scan will occur.

Now consider this (and this is seriously quick)…

WITH t_str AS
(
 SELECT  :LIST_ITEM||':'                                              AS str,
 (LENGTH(:LIST_ITEM) - LENGTH(REPLACE(:LIST_ITEM,':'))) + 1 AS no_of_elements
 FROM    dual
),   t_n_rows AS
(
 SELECT  LEVEL AS i
 FROM    dual
 CONNECT BY LEVEL <= (SELECT SUM(no_of_elements) FROM t_str)
),   t_in_list AS
(
 SELECT SUBSTR(str,start_pos,(next_pos - start_pos)) AS element_value
 FROM   (
 SELECT t_str.str,
 nt.i AS element_no,
 INSTR(t_str.str,':',DECODE(nt.i,1,0,1),
 DECODE(nt.i,1,1,nt.i - 1)) + 1 AS start_pos,
 INSTR(t_str.str,':',1,DECODE(nt.i,1,1,nt.i))       AS next_pos
 FROM   t_str JOIN t_n_rows nt
 ON nt.i <= t_str.no_of_elements
 )
)
SELECT *
FROM   emp
WHERE  empno IN (SELECT TO_NUMBER(element_value) FROM t_in_list);

Voila! The index on empno will be used! I know there’s more lines of code here but the performance benefits of it are huge (most of it’s done just using fast dual in the factored subquery). All you need to do is save it in your toolbox and copy & paste when the need arises!

Hope you enjoyed this, more posts coming soon.

 

PS: Sorry – forgot to link to the actual article by Adrian – hit the word ‘nifty’ now!

About these ads