SELECT * FROM TABLE(pipelined function): can I be sure of the order of the rows in the result?

Question

In the following example, will I always get “1, 2”, or is it possible to get “2, 1” and can you tell me where in the documentation you see that guarantee if it exists?

If the answer is yes, it means that without ORDER BY nor ORDER SIBLINGS there is a way to be sure of the result set order in a SELECT statement.

CREATE TYPE temp_row IS OBJECT(x number);
/

CREATE TYPE temp_table IS TABLE OF temp_row;
/

CREATE FUNCTION temp_func
RETURN temp_table PIPELINED
IS
BEGIN
    PIPE ROW(temp_row(1));
    PIPE ROW(temp_row(2));
END;
/

SELECT * FROM table(temp_func());

Thank you.

Answer 1

I don't think that there's anywhere in the documentation that guarantees the order that data will be returned in.

There's an old Tom Kyte thread from 2003 (so might be out of date) which states that relying on the implicit order would not be advisable, for the same reasons as you would not rely on the order in ordinary SQL.

1st: is the order of rows returned from the table function within a SQL statement the exact same order in which the entries were "piped" into the internal collection (so that no order by clause is needed)?

...

Followup May 18, 2003 - 10am UTC:

1) maybe, maybe not, I would not count on it. You should not count on the order of rows in a result set without having an order by. If you join or do something more complex then simply "select * from table( f(x) )", the rows could well come back in some other order.

empirically -- they appear to come back as they are piped. I do not believe it is documented that this is so.

In fact, collections of type NESTED TABLE are documented to explicitly not have the ability to preserve order.

To be safe, you should do as you always would in a query, state an explicit ORDER BY, if you want the query results ordered.

Having said that I've taken your function and run 10 million iterations, to check whether the implicit order was ever broken; it wasn't.

SQL> begin
  2    for i in 1 .. 10000000 loop
  3      for j in ( SELECT a.*, rownum as rnum FROM table(temp_func()) a ) loop
  4
  5         if j.x <> j.rnum then
  6            raise_application_error(-20000,'It broke');
  7         end if;
  8      end loop;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.