Pivoting on table with huge number of records

Question

I have the following tables:

Create Table A
(rpt_id number,
Acct_id number,
type vatchar2(10));


Create Table 2
(rpt_id number,
Acct_id number,
tp varchar2(10),
information varchar2(100));


Insert into A1 (RPT_ID,ACCT_ID,TYPE) values (1,11,'type1');
Insert into A1 (RPT_ID,ACCT_ID,TYPE) values (2,22,'type2');


Insert into A2 (RPT_ID,ACCT_ID,TP,INFORMATION) values (1,11,'billnum','2341');
Insert into A2 (RPT_ID,ACCT_ID,TP,INFORMATION) values (1,11,'billname','abcd');

I need to take information as below:

RPT_ID ACCT_ID billnum billname
------ ------- ------- --------
1       11      2341    abcd

This table will have a huge amount of data, around 200000 records in A1 and related records in A2 - atleast 4 to 5 rows for each RPT_ID.

Should I be creating a pivot direct from these two joins to improve performance?

So far I have used this approach:

Insert into t3
as select a2.*
     from a1
     join a2 on a1.rpt_id = a2.rpt_id and a1.ACCT_ID = a2.ACCT_ID
    where a1.type = 'type1';

Pivot on t3 to make the following structure and insert into t4 to use it later in the code.

RPT_ID ACCT_ID billnum billname
------ ------- ------- --------
1      11      2341    abcd

This is going full scan for the A2 table. Are there any ways to avoid a full scan? Will pivot have performance issues with huge data?

Answer 1

This is going full scan for A2 table, is there anything we can about this ot avoid full scan

Have you created any indexes on the tables in question? If not, then a full table scan is the only option!

And remember: a full table scan can be the fastest way to get the rows. To see if that's the case, you need to get the execution plan for your query.

That said, the current process of loading the join into a third table, then pivoting the results into a forth is convoluted. And likely to be a lot slower than just running query.

If you want to pre-compute the pivot, you're better off with a materialized view. This stores the result of your query. And - provided you can make it fast refresh on commit - the database will update it after you run DML.

For example:

Create Table A1 (
  rpt_id number,
  Acct_id number,
  type varchar2(10)
);  

Create Table A2 (rpt_id number,
  Acct_id number,
  tp varchar2(10),
  information varchar2(100)
);

Insert into A1 (RPT_ID,ACCT_ID,TYPE) values (1,11,'type1');
Insert into A1 (RPT_ID,ACCT_ID,TYPE) values (2,22,'type2');

Insert into A2 (RPT_ID,ACCT_ID,TP,INFORMATION) values (1,11,'billnum','2341');
Insert into A2 (RPT_ID,ACCT_ID,TP,INFORMATION) values (1,11,'billname','abcd');

commit;

create materialized view log on a1 
  with rowid, sequence ( rpt_id,acct_id,type )
  including new values;
create materialized view log on a2
  with rowid, sequence ( rpt_id,acct_id,tp,information )
  including new values;

create materialized view mv 
refresh fast on commit
as
with rws as (
  select a1.type, a2.*
  from   a1
  join   a2 on a1.rpt_id = a2.rpt_id 
  and    a1.ACCT_ID = a2.ACCT_ID
)
  select type, rpt_id, acct_id, 
         max ( case when tp = 'billnum' then information end ) billnum, 
         max ( case when tp = 'billname' then information end ) billname,
         count(*)
  from   rws
  group  by type, rpt_id, acct_id;

Insert into A2 (RPT_ID,ACCT_ID,TP,INFORMATION) values (2,22,'billname','abcd');

commit;

select * from mv;

TYPE     RPT_ID    ACCT_ID    BILLNUM    BILLNAME    COUNT(*)   
type1            1         11 2341       abcd                  2 
type2            2         22 <null>     abcd                  1 

If necessary you can create indexes on the materialized view itself, further improving performance.

NB - Oracle Database does have a pivot clause, but this doesn't work with fast refresh on commit. You need the old-fashioned version.