Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Davide.

Asked: June 30, 2016 - 4:03 pm UTC

Last updated: July 03, 2016 - 3:07 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked


hi,

i write relating the 71th statment of the following link.
https://livesql.oracle.com/apex/livesql/s/dif16pb1eyri6hmzxxr1wg1zb

i've to speed up this cursor:
cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim)  is 
select 
    a1.prod_c, 
    a1.contr_n, 
    a1.IMP_CAPIT_VERS, 
    a2.controvalore 
from 
    table(c1) a1, 
    table(c2) a2 
where 
    a1.get_key()=a2.get_key(); 

i'm going to do someting like that:
create table redd_tmp_pv (
  chiave varchar2(128),
  valore redd_rtPatrim
);

create index idx on Sogg_Areatecn.redd_tmp_pv(chiave);

but i've seen that i can't insert data on this table using the complex type:
            insert into redd_tmp_pv
            select fa.get_key(), fa
            from (table(fondiaperti)) fa;


there is a smarter way to increase the performance of this operation?


tnkz a lot!


and Connor said...

One option you could try is retaining the key as part of the object, rather than having derive it all the time. But in either case, I'm not seeing much performance issues


SQL> create or replace type redd_rtTotali is object (
  2          "PROD_C" CHAR(11),
  3          "CONTR_N" CHAR(20),
  4          "PROD_C_FONDO" CHAR(11),
  5          "CONTR_N_POLIZ" CHAR(11),
  6          "DT_INIZIO" date,
  7          "DT_FINE" date,
  8          "IMP_CAPIT_VERS" NUMBER(18,3),
  9          "IMP_CAPIT_REINV" NUMBER(18,3),
 10          "IMP_CAPIT_INVEST" NUMBER(18,3),
 11          "IMP_RIMBO_CONTO_CORR" NUMBER(18,3),
 12          "IMP_RIMBO_REINV" NUMBER(18,3),
 13          "IMP_PROVENTI_DISTR" NUMBER(18,3),
 14          "IMP_DECUMU" NUMBER(18,3),
 15      MEMBER FUNCTION get_key RETURN varchar2
 16  );
 17  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY redd_rtTotali AS
  2    MEMBER FUNCTION get_key RETURN varchar2 IS
  3    BEGIN
  4      RETURN trim(PROD_C)||'-'||trim(CONTR_N)||'-'||trim(CONTR_N_POLIZ)||'-'||trim(PROD_C_FONDO);
  5    END;
  6  END;
  7  /

Type body created.

SQL>
SQL> create or replace type redd_rtPatrim is object (
  2    "PROD_C" CHAR(11),
  3    "CONTR_N" CHAR(20),
  4    "PROD_C_FONDO" CHAR(11),
  5    "CONTROVALORE" NUMBER(18,3),
  6    "QTA_QUOTE" NUMBER(18,3),
  7    "CONTR_N_POLIZ" CHAR(11),
  8      MEMBER FUNCTION get_key RETURN varchar2
  9  );
 10  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY redd_rtPatrim AS
  2    MEMBER FUNCTION get_key RETURN varchar2 IS
  3    BEGIN
  4      RETURN trim(PROD_C)||'-'||trim(CONTR_N)||'-'||trim(CONTR_N_POLIZ)||'-'||trim(PROD_C_FONDO);
  5    END;
  6  END;
  7  /

Type body created.

SQL>
SQL> create or replace type redd_tbPatrim as table of redd_rtPatrim
  2  /

Type created.

SQL>
SQL> create or replace type redd_tbTotali as table of redd_rtTotali
  2  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2  table1 redd_tbTotali;
  3  table2 redd_tbPatrim;
  4  x pls_integer := 0;
  5  t timestamp;
  6  cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim)  is
  7  select
  8      a1.prod_c,
  9      a1.contr_n,
 10      a1.IMP_CAPIT_VERS,
 11      a2.controvalore
 12  from
 13      table(c1) a1,
 14      table(c2) a2
 15  where
 16      a1.get_key()=a2.get_key();
 17  crec v3%ROWTYPE;
 18  begin
 19
 20   select c1 bulk collect into table1
 21   from (
 22     select redd_rtTotali('a','b','c','d', current_date, current_date, 1,2,3,4,5,6,7) c1 from dual union all
 23     select redd_rtTotali('b','b','c','d', current_date, current_date, 21,22,23,24,25,26,27) from dual union all
 24     select redd_rtTotali('c','b','c','d', current_date, current_date, 31,32,33,34,35,36,37) from dual union all
 25     select redd_rtTotali('d','b','c','d', current_date, current_date, 41,42,43,44,45,46,47) from dual ),
 26       ( select 1 from dual connect by level <= 1000 );
 27
 28   select c2 bulk collect into table2
 29   from (
 30      select redd_rtPatrim('a','b','c',81,82,'d') c2 from dual union all
 31      select redd_rtPatrim('c','b','c',5,52,'d') from dual union all
 32      select redd_rtPatrim('d','b','c',61,62,'d') from dual union all
 33      select redd_rtPatrim('e','b','c',71,72,'d') from dual  ),
 34      ( select 1 from dual connect by level <= 1000 );
 35
 36      dbms_output.put_line(table1.COUNT);
 37      dbms_output.put_line(table2.COUNT);
 38
 39   t := systimestamp;
 40      for i in v3(table1, table2)
 41      LOOP
 42         x := x + 1;
 43      END LOOP;
 44  dbms_output.put_line(x||' recs, time='||(systimestamp-t));
 45
 46  end;
 47  /
4000
4000
3000000 recs, time=+000000000 00:00:04.824000000

PL/SQL procedure successfully completed.

SQL>


So 4.8secs for 4000x4000 yielding 3,000,000 rows. And the following slight improvement by holding the key as part of the definition


SQL> create or replace type redd_rtTotali is object (
  2          "PROD_C" CHAR(11),
  3          "CONTR_N" CHAR(20),
  4          "PROD_C_FONDO" CHAR(11),
  5          "CONTR_N_POLIZ" CHAR(11),
  6          "DT_INIZIO" date,
  7          "DT_FINE" date,
  8          "IMP_CAPIT_VERS" NUMBER(18,3),
  9          "IMP_CAPIT_REINV" NUMBER(18,3),
 10          "IMP_CAPIT_INVEST" NUMBER(18,3),
 11          "IMP_RIMBO_CONTO_CORR" NUMBER(18,3),
 12          "IMP_RIMBO_REINV" NUMBER(18,3),
 13          "IMP_PROVENTI_DISTR" NUMBER(18,3),
 14          "IMP_DECUMU" NUMBER(18,3),
 15       stored_key varchar2(100),
 16      MEMBER FUNCTION get_key RETURN varchar2
 17  );
 18  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY redd_rtTotali AS
  2    MEMBER FUNCTION get_key RETURN varchar2 IS
  3    BEGIN
  4      RETURN trim(PROD_C)||'-'||trim(CONTR_N)||'-'||trim(CONTR_N_POLIZ)||'-'||trim(PROD_C_FONDO);
  5    END;
  6  END;
  7  /

Type body created.

SQL>
SQL> create or replace type redd_rtPatrim is object (
  2     "PROD_C" CHAR(11),
  3     "CONTR_N" CHAR(20),
  4     "PROD_C_FONDO" CHAR(11),
  5     "CONTROVALORE" NUMBER(18,3),
  6     "QTA_QUOTE" NUMBER(18,3),
  7     "CONTR_N_POLIZ" CHAR(11),
  8       stored_key varchar2(100),
  9      MEMBER FUNCTION get_key RETURN varchar2
 10  );
 11  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE BODY redd_rtPatrim AS
  2    MEMBER FUNCTION get_key RETURN varchar2 IS
  3    BEGIN
  4      RETURN trim(PROD_C)||'-'||trim(CONTR_N)||'-'||trim(CONTR_N_POLIZ)||'-'||trim(PROD_C_FONDO);
  5    END;
  6  END;
  7  /

Type body created.

SQL>
SQL> create or replace type redd_tbPatrim as table of redd_rtPatrim
  2  /

Type created.

SQL>
SQL> create or replace type redd_tbTotali as table of redd_rtTotali
  2  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2  table1 redd_tbTotali;
  3  table2 redd_tbPatrim;
  4  x pls_integer := 0;
  5  t timestamp;
  6  cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim)  is
  7  select
  8      a1.prod_c,
  9      a1.contr_n,
 10      a1.IMP_CAPIT_VERS,
 11      a2.controvalore
 12  from
 13      table(c1) a1,
 14      table(c2) a2
 15  where
 16      a1.stored_key=a2.stored_key;
 17  crec v3%ROWTYPE;
 18  begin
 19
 20
 21   select c1 bulk collect into table1
 22   from (
 23     select redd_rtTotali('a','b','c','d', current_date, current_date, 1,2,3,4,5,6,7,'abcd') c1 from dual union all
 24     select redd_rtTotali('b','b','c','d', current_date, current_date, 21,22,23,24,25,26,27,'bbcd') from dual union all
 25     select redd_rtTotali('c','b','c','d', current_date, current_date, 31,32,33,34,35,36,37,'cbcd') from dual union all
 26     select redd_rtTotali('d','b','c','d', current_date, current_date, 41,42,43,44,45,46,47,'dbcd') from dual ),
 27       ( select 1 from dual connect by level <= 1000 );
 28
 29   select c2 bulk collect into table2
 30   from (
 31      select redd_rtPatrim('a','b','c',81,82,'d','abcd') c2 from dual union all
 32      select redd_rtPatrim('c','b','c',5,52,'d','cbcd') from dual union all
 33      select redd_rtPatrim('d','b','c',61,62,'d','dbcd') from dual union all
 34      select redd_rtPatrim('e','b','c',71,72,'d','ebcd') from dual  ),
 35      ( select 1 from dual connect by level <= 1000 );
 36
 37
 38      dbms_output.put_line(table1.COUNT);
 39      dbms_output.put_line(table2.COUNT);
 40
 41   t := systimestamp;
 42      for i in v3(table1, table2)
 43      LOOP
 44         x := x + 1;
 45      END LOOP;
 46  dbms_output.put_line(x||' recs, time='||(systimestamp-t));
 47
 48  end;
 49  /
4000
4000
3000000 recs, time=+000000000 00:00:03.833000000

PL/SQL procedure successfully completed.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Davide Golinelli, July 01, 2016 - 8:25 am UTC

maybe can I use an associative array to store the link between the records keys and each position in the array.
did you know if the algorithm has a sublinear complexity (like hash)?
on the oracle doc is described only as "with fast lookup".

TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64)
Connor McDonald
July 02, 2016 - 1:41 am UTC

The point I was trying to get across was - how fast do actually need it ?

The demo I gave gives you 3,000,000 rows in just over 3 seconds, and thats because the join conditions are near cartesian.

If the truth is more granular, ie, the join is 1-to-1, then for example:

SQL> declare
  2  table1 redd_tbTotali;
  3  table2 redd_tbPatrim;
  4  x pls_integer := 0;
  5  t timestamp;
  6  cursor v3(c1 redd_tbTotali, c2 redd_tbPatrim)  is
  7  select
  8      a1.prod_c,
  9      a1.contr_n,
 10      a1.IMP_CAPIT_VERS,
 11      a2.controvalore
 12  from
 13      table(c1) a1,
 14      table(c2) a2
 15  where
 16      a1.stored_key=a2.stored_key;
 17  crec v3%ROWTYPE;
 18  begin
 19
 20      select c1 bulk collect into table1
 21      from (
 22        select redd_rtTotali('a','b','c','d', current_date, current_date, 1,2,3,4,5,6,7,rownum*10+1) c1 from dual connect by level <= 10000 union all
 23        select redd_rtTotali('b','b','c','d', current_date, current_date, 21,22,23,24,25,26,27,rownum*10+2) from dual connect by level <= 10000 union all
 24        select redd_rtTotali('c','b','c','d', current_date, current_date, 31,32,33,34,35,36,37,rownum*10+3) from dual connect by level <= 10000 union all
 25        select redd_rtTotali('d','b','c','d', current_date, current_date, 41,42,43,44,45,46,47,rownum*10+4) from dual connect by level <= 10000
 26        );
 27
 28      select c2 bulk collect into table2
 29      from (
 30         select redd_rtPatrim('a','b','c',81,82,'d',rownum*10+1) c2 from dual connect by level <= 10000 union all
 31         select redd_rtPatrim('c','b','c',5,52,'d',rownum*10+2) from dual connect by level <= 10000 union all
 32         select redd_rtPatrim('d','b','c',61,62,'d',rownum*10+3) from dual connect by level <= 10000 union all
 33         select redd_rtPatrim('e','b','c',71,72,'d',rownum*10+4) from dual connect by level <= 10000  );
 34
 35      dbms_output.put_line(table1.COUNT);
 36      dbms_output.put_line(table2.COUNT);
 37
 38   t := systimestamp;
 39      for i in v3(table1, table2)
 40      LOOP
 41         x := x + 1;
 42      END LOOP;
 43  dbms_output.put_line(x||' recs, time='||(systimestamp-t));
 44
 45  end;
 46  /
40000
40000
40000 recs, time=+000000000 00:00:00.194000000

PL/SQL procedure successfully completed.


That 40,000 rows joined in 0.2 seconds....Is that not enough ?

bulk limit

Davide Golinelli, July 02, 2016 - 9:02 am UTC

my algorithm works like this:
i first get a "bulk limit" data from a table of 1.2M of records and then i use a part of the selected columns as condition on a "select group" on a table of 180M of records.
ex:
select a, b, c, sum(d), sum(case ...end ), sum(case end)
from huge_table
where (a, b) in (select a, b from table(bulksizetable))
group by a, b, c


after that i make the join shown in the example.
the "select group" extracts about the same record number of the inputs one (bulk size).
i believe that this "select group" has a cost only partially related to the input size, so to maximize it's performance I've to keep the input size high.
but doing so i'm going to increase overall time of excecution.
i've done some runs of the code changing the "bulk limit" only and i've seen that with a bulk size about 10^3 i've a overall exectution time of 2-3 hours but if I increase it to 10^4 i reach the execution time of 9 hours.
i know that I can do more specialized query that i'm sure will increase the performance but i wish not do it in order to keep the code reusability high

Connor McDonald
July 03, 2016 - 3:07 am UTC

Even with sizes in the millions, you'd typically expect execution times in the seconds/minutes order of magnitude, rather than hours.

Try running through a trace, aka

dbms_monitor.session_trace_enable(waits=>true)

and set statistics_level to all, and see what you can glean from that.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library