oracle results from tables using function

Question

I'm doing some testing to see if I can speed up a particular result set, but can't seem to get this particular solution working. I have data coming a few different tables and want to combine the data. I want to try this without using a union select to see if I get a performance improvement.

When I have a custom table/object type in a function, it seems to delete the existing data from the table when doing the subsequent select. Is there a way to do subsequent selects into the table without having the previous data deleted?

SQL Fiddle

You'd still be doing two selects, with the addition of a context switch to PL/SQL, so not sure why you'd expect it to be any faster. The only significant difference is that you avoid the sorting to remove the duplicates - which you could also avoid with the original by using union all. — Alex Poole
– Alex Poole, Commented May 30, 2013 at 22:33

user330315 · Accepted Answer · 2013-05-30 22:39:50Z

2

I don't think that approach will be faster, in fact I expect it to be much slower.

But if you do want to do it, you need to put the rows from the second select into an intermediate collection and then join both using multiset union.

Something like this:

create or replace function
  academic_history(p_student_id number)
  return ah_tab_type
  is
  result ah_tab_type;
  t ah_tab_type;
begin

  select ah_obj_type(student_id,course_code,grade)
     bulk collect into result
  from completed_courses
  where student_id = p_student_id;

  select ah_obj_type(student_id,course_code,'P')
    bulk collect into T
  from trans_courses
  where student_id = p_student_id;

  result := result multiset union t;

  return result;
end;
/

answered May 30, 2013 at 22:39

user330315

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2013-05-30 22:55:57Z

0

As well as the multiset approach, if you really wanted to do this you could also make it a pipelined function:

create or replace function
academic_history(p_student_id number)
return ah_tab_type pipelined
is
    T ah_tab_type;
begin
    select ah_obj_type(student_id,course_code,grade)
    bulk collect
    into T
    from completed_courses
    where student_id = p_student_id;

    for i in 1..T.count loop
        pipe row (T(i));
    end loop;

    select ah_obj_type(student_id,course_code,'P')
    bulk collect
    into T
    from trans_courses
    where student_id = p_student_id;

    for i in 1..T.count loop
        pipe row (T(i));
    end loop;

    return;
end;

SQL Fiddle.

answered May 30, 2013 at 22:55

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

Gordolio Over a year ago

I did look at the pipelined function. I assumed my returned dataset 30-50 rows would be small enough to not need it. But this is still very informative. Thank you.

Gordolio · Accepted Answer · 2013-05-31 16:56:09Z

Thanks a_horse_with_no_name for pointing out that doing the multiple selects one at a time will probably be slower. I was able to reduce the execution time by filtering each select by student_id and then union-ing (rather than union-ing everything then filtering). On the data set I'm working with this solution was the fastest taking less than 1/10 of a second...

create or replace function
  academic_history(p_student_id number)
  return ah_tab_type
  is
  T ah_tab_type;
begin

  select ah_obj_type(student_id,course_code,grade)
  bulk collect
  into T
  from (
    select student_id,course_code,grade
    from completed_courses
    where student_id = p_student_id
    union
    select student_id,course_code,'P'
    from trans_courses
    where student_id = p_student_id);

  return T;
end;
/
select *
from table(academic_history(1));

and this took 2-3 seconds to execute...

create view vw_academic_history
select student_id,course_code,grade
from completed_courses
union
select student_id,course_code,'P'
from trans_courses;

select *
from vw_academic_history
where student_id = 1;

SQLFiddle.

Collectives™ on Stack Overflow

oracle results from tables using function

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related