Optimized approach to addressing LISTAGG() and the ORA-01489 error

Two previous posts have discussed the topic of LISTAGG() and the ORA-01489: result of string concatenation is too long error. This occurs when aggregations of strings exceed the 32767 limit of the VARCHAR2 datatype. The first article, Oracle LISTAGG() — ORA-01489: result of string concatenation is too long, provides a way to “truncate” aggregations being passed to LISTAGG() to show as much data as possible without exceeding the VARCHAR2 size limit. The second, ORA-01489 – Aggregating using CLOBs to handle long strings, provides a user-defined aggregation function that works on CLOBs. This approach addresses the size limitations of VARCHAR2, but suffers heavily in performance due to CLOB processing. This article presents one additional alternative. This user-defined aggregate function is designed to operate on VARCHAR2 datatypes, but relies on CLOBs internally to move beyond the VARCHAR2 size limitation. A significant performance improvement is realized thanks to faster processing and appending of VARCHAR2s when available. CLOBs are only used once the length of data exceeds the VARCHAR2 limit.

SQL> CREATE OR REPLACE
  2  TYPE VarCharAggregator
  3  AS OBJECT
  4  (
  5    separator VARCHAR2( 20 ),     -- Delimeter for the fields
  6    separator_length NUMBER,      -- Length of the delimeter
  7    buffer VARCHAR2( 4000 ),      -- Used to aggregate strings until the maximum VARCHAR2 length is exceeded
  8    buffer_length NUMBER,         -- Current length of the data in buffer
  9    aggregate_clob CLOB,          -- The aggregated CLOB value into which the buffer is moved as it exceeds the VARCHAR2 length
 10    has_clob_data VARCHAR2( 1 ),  -- Flag to indicate whether any data has been moved to the aggregate_clob
 11
 12    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
 13      RETURN NUMBER,
 14    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 15      RETURN NUMBER,
 16    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number )
 17      RETURN NUMBER,
 18    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 19      RETURN NUMBER
 20  );
 21  /

Type created.


SQL> CREATE OR REPLACE
  2  TYPE BODY VarCharAggregator
  3  IS
  4
  5    STATIC FUNCTION ODCIAggregateInitialize( sctx IN OUT VarCharAggregator )
  6    RETURN NUMBER
  7    IS
  8    BEGIN
  9
 10      sctx := VarCharAggregator( ',', LENGTH( ',' ), '', 0, NULL, 'N' );
 11      DBMS_LOB.CREATETEMPORARY( sctx.aggregate_clob, TRUE, DBMS_LOB.CALL );
 12
 13      RETURN ODCIConst.Success;
 14
 15    END;
 16
 17    MEMBER FUNCTION ODCIAggregateIterate( self IN OUT VarCharAggregator, value IN VARCHAR2 )
 18    RETURN NUMBER
 19    IS
 20      value_length NUMBER;
 21    BEGIN
 22
 23      value_length := LENGTH( value );
 24
 25      -- Move "buffer" into aggregate_clob once appending "value" to "buffer" will exceed the maximum VARCHAR2 length
 26      IF ( self.buffer_length > 0 ) AND ( value_length + self.buffer_length > ( 4000 - self.separator_length ) ) THEN
 27
 28        IF self.has_clob_data = 'Y' THEN
 29          DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.separator ) );
 30        END IF;
 31
 32        DBMS_LOB.APPEND( self.aggregate_clob, TO_CLOB( self.buffer ) );
 33        self.has_clob_data := 'Y';
 34        self.buffer := '';
 35        self.buffer_length := 0;
 36
 37      END IF;
 38
 39      -- Insert a separator value if we already have data in the buffer
 40      IF self.buffer_length > 0 THEN
 41        self.buffer := self.buffer || self.separator;
 42      END IF;
 43
 44      -- Append "value" to our buffer
 45      self.buffer := self.buffer || value;
 46      self.buffer_length := self.buffer_length + value_length;
 47
 48      RETURN ODCIConst.Success;
 49
 50    END;
 51
 52    MEMBER FUNCTION ODCIAggregateTerminate( self IN VarCharAggregator, returnValue OUT CLOB, flags IN number)
 53    RETURN NUMBER
 54    IS
 55    BEGIN
 56
 57      returnValue := self.aggregate_clob;
 58
 59      IF LENGTH( self.buffer ) > 0 THEN
 60
 61        IF self.has_clob_data = 'Y' THEN
 62          DBMS_LOB.APPEND( returnValue, self.separator );
 63        END IF;
 64
 65        DBMS_LOB.APPEND( returnValue, self.buffer );
 66
 67      END IF;
 68
 69      RETURN ODCIConst.Success;
 70
 71    END;
 72
 73    MEMBER FUNCTION ODCIAggregateMerge( self IN OUT VarCharAggregator, ctx2 IN VarCharAggregator )
 74    RETURN NUMBER
 75    IS
 76    BEGIN
 77
 78      DBMS_LOB.APPEND( self.aggregate_clob, self.separator );
 79      DBMS_LOB.APPEND( self.aggregate_clob, ctx2.aggregate_clob );
 80
 81      RETURN ODCIConst.Success;
 82
 83    END;
 84
 85  END;
 86  /

Type body created.


SQL> CREATE OR REPLACE
  2  FUNCTION VARCHARAGG( input VARCHAR2 )
  3  RETURN CLOB
  4  PARALLEL_ENABLE AGGREGATE
  5  USING VarCharAggregator;
  6  /

Function created.

When aggregating 500 rows, this revised version runs approximately 2.5 times faster than the CLOB-only function of the previous article. When aggregating 1000 rows, it runs approximately twice as fast. An example of usage follows:

SQL> SELECT VARCHARAGG( value )
  2  FROM test_strings;

VARCHARAGG(VALUE)
---------------------------------
test1,test2,test3
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s