Associative Arrays (Index By Pl Sql Table) With Cursor

user10799880

Hi ,
My Requirement is that I want to use associative arrays (Or Index By Pl Sql Table) and value of subscript should match with the value of column1 value in cursor. This cursor is actually used to populate associative collection.

Please note I specifically want to use the associative array(Index By table) due to below mentioned 2 reasons :-
<ol>
<li>Only In Associative arrays can be non sequential.</li>
<li>Only in Associative arrays I can directly reference values (If i know subscript) and I don't need to scan the entire array to reach to desired subscript in array.
</li>
</ol>
<p>

Below is the Code I am using and<em><strong> <font color="#ff0000">my requirement is Associative_Array(Index).Col1 = Index</font></strong></em>
</p>
<p>
----------------------------------------------------------------------------------
</p>
<p>
CREATE OR REPLACE PROCEDURE GDWAPPADM.Collection_Test_Ver2
IS
TYPE recIndividualBehaviour IS RECORD
(
INB_IN_ID_FK INB_INDIVIDUAL_BEHAVIOUR_DM.INB_IN_ID_FK%TYPE,
INB_ENR_ALERTREG_TOT INB_INDIVIDUAL_BEHAVIOUR_DM.INB_ENR_ALERTREG_TOT%TYPE,
INB_ENR_ALERTREG_DATE INB_INDIVIDUAL_BEHAVIOUR_DM.INB_ENR_ALERTREG_DATE%TYPE
) ;


TYPE tabIndividualBehaviour IS TABLE OF recIndividualBehaviour INDEX BY BINARY_INTEGER ;

TYPE RefCursor IS REF CURSOR ;

vProc_Msg_Cntr NUMBER :=0;
Counter1 NUMBER :=0;
LoopCounter NUMBER;
pIndividualBehaviour tabIndividualBehaviour;
vSql VARCHAR2(30000);
cRefCursor RefCursor;

--------------------------------------------------------------------------------------------------------------------------
vIn_Id_Fk INB_INDIVIDUAL_BEHAVIOUR_DM.INB_IN_ID_FK%TYPE;
vEnr_AlertReg_Tot INB_INDIVIDUAL_BEHAVIOUR_DM.INB_ENR_ALERTREG_TOT%TYPE;
vMax_Enr_Start_Date INB_INDIVIDUAL_BEHAVIOUR_DM.INB_ENR_ALERTREG_DATE%TYPE;

--------------------------------------------------------------------------------------------------------------------------

Cursor cCursor Is
Select
DISTINCT CI_IN_ID_FK INB_IN_ID_FK, NULL INB_ENR_ALERTREG_TOT, NULL INB_ENR_ALERTREG_DATE From CI_COMPOSITE_INDIVIDUAL_DM;


BEGIN
Select CI_IN_ID_FK Into vIn_Id_Fk From CI_COMPOSITE_INDIVIDUAL_DM Where Rownum=1;
DBMS_Output.Put_Line('Value Of vIn_Id_Fk ::' || vIn_Id_Fk );

Open cCursor;
Loop
FETCH cCursor BULK COLLECT INTO
pIndividualBehaviour
Limit 25000;
<em><font color="#ff0000">----- Please Note I have already tried to use " Fetch cCursor Bulk Collect Into pIndividualBehaviour(cCursor.INB_IN_ID_FK) limit 25000; " But I am getting error as invalid cursor reference.</font></em>
</p>
<p>

EXIT WHEN cCursor%NOTFOUND;
END LOOP;
CLOSE cCursor;

DBMS_Output.Put_Line('Value Of Added In_Id_Fk At Position pIndividualBehaviour('||vIn_Id_Fk ||') Is ::' || pIndividualBehaviour(vIn_Id_Fk ).INB_IN_Id_Fk);
END;
/
</p>
<p>
<font color="#ff0000">==&gt; If I comment the last DBS_Output then the procedure works fine but not with it.</font>
</p>
<p>
<font color="#ff0000">==&gt; I always want to ensure that for every record in Associative Array pIndividualBehaviour </font><font color="#0000ff"><strong><font color="#000000">"subscript/index value"</font> should be same as value of <font color="#000000">"pIndividualBehaviour(vIn_Id_Fk ).INB_IN_Id_Fk
"</font></strong></font>
</p>
<p>
Thanks In advance,
</p>
<p>
Manish
</p>

Andreas Weiden

you can do so, but only without using bulk collect.

Define an additional var

rRecord recIndividualBehaviour;

And in the cursor loop do a

Open cCursor;
Loop
  FETCH cCursor INTO rRecord;
  pIndividualBehaviour(rRecord.INB_IN_ID_FK):=rRecord;
  EXIT WHEN cCursor%NOTFOUND;
END LOOP;
CLOSE cCursor;

user10799880

Thanks Andreas...But is there any way that I can use this feature with Bulk Collect since I have 3 Million records (H/w have enough memory) and thus I want to make it more tuned.

Andreas Weiden

Why do you have to reads 3m of records into an array?

user10799880

Let Me explain here :-

I have a table Tab1 As ( Key_Column, Col1,Col2.....Col30);

This table contains 3 Million records. In order to update Col2 I need to join Tab1 with Tab2 on Key_Column and then after computing Min/Max/Count or some other logic from Tab2 I will update Col2 In table1. Same will be done for Col3 but this time Table1 need to be join with Table3. ...And So On.....for every column i need to join with another table to get it populated......if i do 30 updates serially one after another then it will tak ehuge amount of time....so idea is I will keep on updating values in associative arrays and then in a single shot i will write all of this in a table for users.

Andreas Weiden

There is a general rule: Pure SQL is (nearly) always faster then using PL/SQL.

Did you try the SQL-approach?

user10799880

yes I tried ....but update for each column is taking more than 30 minutes....I believe update is hitting me very hard.......so instead of table update I want to do array update and then want to try insert array into table.........So what do u suggest....do we have any other way to do bulk collect ??

Andreas Weiden

No idea for the bulk, did you try to get an SQL-approach in this forum?

user10799880

As I said there are 30 updates in the existing procedure which runs for 17 hrs so i din't try that approach. But when we looked at each sql they are using necessary hints/indexes and they are pretty straight fwd updates.....like

UPDATE INB_INDIVIDUAL_BEHAVIOUR_DM
SET (inb_enr_Alertreg_tot, inb_enr_alertreg_date) =
(SELECT COUNT(*), MAX(enr_start_date) FROM ENR_ENROLLMENT_FACT
WHERE enr_orig_in_id_fk = inb_in_id_fk
AND enr_typ_cd = 'ALERTREG'
AND enr_chnl_cd = 'WEBGCOM'
AND enr_status_cd = 'ENABLED'
)
WHERE EXISTS
(SELECT enr_orig_in_id_fk
FROM ENR_ENROLLMENT_FACT
WHERE enr_orig_in_id_fk = inb_in_id_fk
AND enr_typ_cd = 'ALERTREG'
);
--

DBA's hasve given up for tunning ......so now i want to try this array approach.

Andreas Weiden

I still would give the SQL-approach a try.

Try changing the thread-title in "Need help on tuning large update" and see that the tuning guys can advice you.

You'll need to post the table-structures, the explain-plan for your update.

And it would be helpful to see if the other Updates are similar.