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>