pipelined always better?

From: Thorsten Kettner <thorsten.kettner_at_web.de>
Date: 11 Sep 2003 01:39:36 -0700
Message-ID: <74a9c367.0309110039.27bf3518@posting.google.com>

I have written a small function which returns a table of numbers. However there are two approaches as to return the table: either pipelined or not.

My Oracle book tells me that pipelining is better, because it delivers the rows immediately instead of waiting for the whole collection to be complete. That makes sense for example if I select with "where rownum < ...". But elsewise? Isn't data transfered from the db server in big chunks anyhow? Can the non-pipelined way be more efficient than the pipeline way hence?

---

1. approach returning a pipelined table

   ---

  Function GetNumbers(p_min IN number

,p_max IN number) RETURN t_numbers pipelined IS

  BEGIN
    for i in p_min .. p_max loop
      pipe row(i);
    end loop;   

    return;
  END; -- Function GetNumbers

---

2. approach returning a non-pipelined table

---

  Function GetNumbers(p_min IN number

,p_max IN number) RETURN t_numbers IS

    v_numbers t_numbers;

  BEGIN
    v_numbers := t_numbers();
    for i in p_min .. p_max loop

      v_numbers.extend;
      v_numbers(v_numbers.count) := i;

    end loop;   

    return v_numbers;
  END; -- Function GetNumbers Received on Thu Sep 11 2003 - 03:39:36 CDT