oracle - SELECT * FROM TABLE(流水线函数): can I be sure of the order of the rows in the result?

Question

在下面的例子中，我会总是得到“1, 2”，还是有可能得到“2, 1”，你能告诉我你在文档中的哪个地方看到了保证是否存在？

如果答案是肯定的，则表示没有ORDER BY也不是 ORDER SIBLINGS有一种方法可以确定 SELECT 语句中的结果集顺序。

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());

谢谢你。

Answer 1

我认为文档中没有任何地方可以保证数据返回的顺序。

有一个旧的Tom Kyte thread从 2003 年开始(所以可能已经过时)，它指出依赖隐式顺序是不可取的，原因与您不依赖普通 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.

为安全起见，如果您希望查询结果排序，您应该像往常一样在查询中声明一个明确的 ORDER BY。

话虽如此，我已经使用了您的函数并运行了 1000 万次迭代，以检查隐式顺序是否被破坏；不是。

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.