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

Question

In the following example, I always get "1, 2", or can I get "2, 1", and can you tell me where in the documentation you see, what guarantee is there if it exists?

If the answer is yes, it means that without ORDER BY and ORDER SIBLINGS there is a way to make sure that the result set is ordered in the 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()); 

Thanks.

Answer 1

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

There exists an old Tom Kyte theme from 2003 (it may be outdated), which says that relying on implicit order is not recommended, for the same reasons that you will not rely on order in regular SQL.

1st: the order of the rows returned from the table function inside the SQL statement is the exact same order in which the records were “skipped” to the internal collection (so that the order by clause is not required)?

...

Follow-up May 18, 2003 - 10:00 UTC:

1) maybe, maybe not, I would not count on it. You should not count the order of the rows in the result set without order. If you are joining or doing something more complicated, then just select "select" from table (f (x)) ", the rows could return in a different order.

empirically - they seem to return when they are piped. I do not believe that this is documented, that it is.

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

To be safe, you should do as always, in the request, specify an explicit ORDER BY if you want the results of the request to be ordered.

Having said that, I took your function and launched 10 million iterations to check if the implicit order was broken; this is not true.

 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 jx <> 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. 

Answer 2

This procedural logic works differently with table-based queries. The reason you cannot rely on orders in a selection from a table is because you cannot rely on the order in which the RDBMS will identify rows as part of the required set. This is partly due to a change in execution plans, and partly because there are very few situations where the physical order of the rows in the table is predictable.

However, here you select a function that guarantees the order in which strings are emitted from the function. In the absence of joins, aggregates, or anything else (i.e., for direct selection ... from a table (function) "), I would be sure that the row order is deterministic.

This advice does not apply if there is a table, if there is no explicit order, so if you load the pl / sql collection from a query that does not use order, then, of course, the order of the rows in the collection is not deterministic.