On Apr 23, 4:38 pm, Jer <[email protected]> wrote: > Hi, > I am trying to rewrite an ugly and hard to maintain piece of dymanic > sql with a function. Basically the function takes as input the list > of columns to return/populate. > > This function retrieves a lot of data from the tables and due to the > complexity of the joins and outer joins I pass in a list of optional > columns which are retrieved from the DB and placed into the out row > only if the user asks for them. If they are not in the field list > then they are ignored and the joins are avoided, the tables being > joined across are also very large! > > So right now I have something that looks like - this is just a > simplified example: > > create or replace > FUNCTION "F_GET_DETAILS" > ( > in_input_value IN NUMBER, > in_field_list IN VARCHAR2 > ) > RETURN T_DETAILS_TABLE > PIPELINED PARALLEL_ENABLE > AS > security_cur sys_refcursor; > out_rec T_DETAIL_RECORD := T_DETAIL_RECORD ( > NULL,NULL, NULL, > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); > v_fields VARCHAR2(5000); > BEGIN > v_fields := UPPER(in_field_list); > > OPEN detail_cur FOR > SELECT x, > y, > z > FROM A_TABLE > WHERE A_COLUMN = in_input_value; > > LOOP > FETCH security_cur INTO > out_rec.a, out_rec.b, out_rec.c; > EXIT WHEN security_cur%NOTFOUND; > > IF (instr(v_fields, 'LHCLASS1') > 0) THEN > BEGIN > SELECT d > INTO out_rec.d > FROM ANOTHER_TABLE > WHERE TABLEID = out_rec.a > AND ANOTHERKEY = 'KEY1'; > EXCEPTION > WHEN no_data_found THEN > out_rec.d := null; > END; > end if; > > /* lots more optional columns here controlled by the > in_field_list > */ > > PIPE ROW(out_rec); > END LOOP; > > CLOSE security_cur; > > RETURN; > END F_GET_DETAILS; > > The problem here is that this is also somewhat inefficient as if the > initial select statement in the function returns a large rowset then > the subsequent optional joins happen once per row retrieved which can > add up to a lot of joins! While this does not take a lot of time it > still ends up being considerably slower than the monsterous dynamic > procedure... > > Is there any way to optionally update all the rows in the cursor at > once instead of the row by row approach here as I am guessing one join > into the Example ANOTHER_TABLE above would be considerably faster for > all data retrieved initially instead of many joins... > > Thanks in advance > > Jeremy. > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group > athttp://groups.google.com/group/Oracle-PLSQL?hl=en
You can investigate using BULK COLLECT and FORALL; using bulk collect into a collection allows you to load all of the data at once (one join, one pass through the tables) and the FORALL 'loop' processes the collection by iterating through the collection 'index'. Yes, it's still a 'row by row' approach but using a collection rather than multiple fetches can improve processing time considerably. David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
