How to efficiently transfrom narrow table into wide in Oracle?

I have a narrow table with the following columns:
<Customer ID> <Field ID> <Value>, all of them are numbers.

I want to reshape this table into the wide format:

<Customer ID> <Field1> <Field2> <Field3> ...

I have a separate dictionary table DIC_FIELDS that translates Field ID into Field Name.

I work on EXADATA server. The narrow table has 2.5 billion records, and we have about 200 fields.

The obvious simple solution below badly fills up all temporary space on our EXADATA server.

create table WIDE_ADS as (
   CUSTOMERID
  ,max(case when FIELDID = 1 then VALUE end) as GENDER
  ,max(case when FIELDID = 2 then VALUE end) as AGE
  ,max(case when FIELDID = 3 then VALUE end) as EDUCATION
from NARROW_ADS
group by CUSTOMERID
);

We tried also a cleverer and manual method:

create index index1
      on SZEROKI_ADS(CUSTOMERID);


DECLARE
    rowidWide rowid;
    type tColNames is table of STRING(32000) index by pls_integer ;
    arrColNames tColNames;
    x_CustomerID number;
    strColName varchar2(32);
    strColvalue varchar2(32000);
    strSQL varchar2(200);
    lngCounter pls_integer;
    lngFieldID pls_integer;
BEGIN
    lngCounter := 0;
    -- we pre-load the dictionary arrColNames to speedup lookup.
    for DIC_EL in (select * from DIC_FIELDS order by FIELDID) LOOP
        lngFieldID := to_number(DIC_EL.FIELDID);
        arrColNames(lngFieldID) := DIC_EL.FIELDNAME;
    END LOOP;

    FOR NARROW_REC IN (SELECT * FROM NARROW_ADS where VALUE is not null ) LOOP 
        strColName := arrColNames(NARROW_REC.FIELDID);
        strColvalue := NARROW_REC.VALUE;
        x_IDKlienta := NARROW_REC.CUSTOMERID;
        BEGIN
            select rowid into rowidWide from WIDE_ADS
                where CUSTOMERID = NARROW_REC.CUSTOMERID;
            strSQL := 'update :1 set :2 = :3 where rowid = :4';
            execute immediate strSQL using WIDE_ADS, strColName, strColvalue, rowidWide;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
                strSQL := 
                'insert into '|| WIDE_ADS ||' (CUSTOMERID, '|| strColName ||')
                 values 
                    (:1, :2)';
                execute immediate strSQL using  x_CustomerID, to_number(strColvalue) ;
        END;
        IF lngCounter=10000 THEN
            COMMIT;
            lngCounter:=0;
            dbms_output.put_line('Clik...');
        ELSE
            lngCounter:=lngCounter+1;
        END IF;    
    END LOOP;
END;

Although it doesn’t take a temp, it fails miserably performance-wise; it processes 10 000 records in 50 sec – that is about 1000 times slower, then expected.

What can we do to speed up the process?


Source: oracle

Leave a Reply