Incredibly slow Materialized View creation when using string aggregation, any performance suggestions?

Question

Hi,

I've got a load of materialized views, some of them take just a few seconds to create and refresh, whereas others can take me up to 40 minutes to compile, if SQLDeveloper doesn't crash before that.

I need to aggregate some strings in my query, and I have the following function

create or replace
function stragg
  ( input varchar2 )
  return varchar2
  deterministic
  parallel_enable
  aggregate using stragg_type
;

Then, in my MV I use a select statement such as

SELECT
  hse.refno,
  STRAGG (DISTINCT per.person_name) as PERSONS
FROM
 HOUSES hse,
 PERSONS per

This is great, because it gives me the following :

 refno        persons
 1            Dave, John, Mary
 2            Jack, Jill

Instead of :

 refno        persons
 1            Dave
 1            John
 1            Mary
 2            Jack 
 2            Jill

It seems that when I use this STRAGG function, the time it takes to create/refresh an MV increases dramatically. Is there an alternative method to achieve a comma separate list of values? I use this throughout my MVs so it is quite a required feature for me

Thanks

Answer 1

There are a number of techniques for string aggregation at the link below. They might provide better performance for you.

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php