So did I understand it correct that a simple

select rownum, column_value from table(manyrows)

without any where clause will ALWAYS have the same values in the two columns? The output from the TF will be strongly sequential? There is no internal optimizer which split the 100.000 iterations into two 50.000 loops?

with rws as (
  select column_value c 
  from   table(manyrows)
  order  by c desc
)
  select rownum, c from rws;

ROWNUM   C    
       1   10 
       2    9 
       3    8 
       4    7 
       5    6 
       6    5 
       7    4 
       8    3 
       9    2 
      10    1

Think I understand it.

In your examples you've changed the the resultset (order or where clause). That's why the rownum is not equal then the index inside the collection.

But when you do nothing, it seems that the loop index is equal to the rownum.

with rws as (
select rownum a, column_value b from table(manyrows)
)
select * from rws where  a!=b

Or in other words: It is not necessary to create a standalone type with an rowindex.

create or replace type mytype as object (
  rowindex    integer,
  textvalue   varchar2(4000)
);

A simple query with the usage of rownum and you get the same result...

create or replace type number_tabtyp as table of number;
/
create or replace function manyrows 
  return number_tabtyp authid definer 
  pipelined
is
begin

  for i in reverse 1..10 loop
    pipe row ( i ) ;
  end loop;
  
  return ;
  
end manyrows;
/

select rownum, column_value from table(manyrows);

ROWNUM   COLUMN_VALUE   
       1             10 
       2              9 
       3              8 
       4              7 
       5              6 
       6              5 
       7              4 
       8              3 
       9              2 
      10              1

I realized that my example with a table of NUMBER is not that good and can be confusing.
So if we make the same with a function that returns 26 characters in alphabetically order like so:

create or replace type vc2_tabtyp as table of varchar2(1 char);
/
create or replace function manyrows2 
  return vc2_tabtyp authid definer 
  pipelined
is
begin

  for i in 1..26 loop
    pipe row ( chr(64+i) ) ;
  end loop;
  
  return ;
  
end manyrows2;
/

My question is: Will the order of output (no sorting, no reverse indexing in the function) always be the alphabetically order if i query the tf in the most simple way like:

select * from table(manyrows2);

Suppose we have a TF returning the content of a collection in a normal loop (no reverse). Did we need a extra output column with the index (i) so that we can later order by this column to get exactly the same order like the original collecion had?

I think (hope) no.

Because if we query the TF without modifying anything, the rownum pseudocolumn will give the same information as the index from the loop which we have to pass before to an extra column. This means an extra object type (one column for the index/sortorder, one for the content). And this means (little) unnecessary overhead.

In Code:

----------------------------------
-- version 1 -- small, no overhead
----------------------------------
create or replace type number_tabtyp as table of number;
/
create or replace function manyrows return number_tabtyp authid definer is
  l_out number_tabtyp:=number_tabtyp();
begin
  l_out.extend(10);
  for i in 1..10 loop
    l_out(i):=i;
  end loop;
  return l_out;
end manyrows;
/
select rownum, column_value from table(manyrows);
---------------------------------------------------
-- version 2 -- not that big, but a little overhead
---------------------------------------------------
create or replace type num_typ as object(
  sort_order number,
  value_num number
);
/
create or replace type number_tt as table of num_typ;
/
create or replace function manyrows return number_tt authid definer is
  l_out number_tt:=number_tt();
  l_row num_typ:=num_typ(null, null);
begin
  l_out.extend(10);
  for i in 1..10 loop
    l_row.sort_order:=i;
    l_row.value_num:=i;
    l_out(i):=l_row;
  end loop;
  return l_out;
end manyrows;
/
select rownum, sort_order, value_num from table(manyrows);

"To guarantee you get rows back in a given sequence, you must have an order by."

Is this also valid for table functions???

"Why are you asking? Why is this relevant to the problem you're trying to solve?"

Imagine a simple split function (not the apex_string.split):
In that case i need the order from the splitted pieces in the TF exactly the same sequence there were splitted.

Did you understand what I mean???

I ask that way:

Is the order of the output from a tablefunction (which use internally a normal loop over a collection) the same as the order/collectionindex inside the collection from the tablefunction?

create or replace type v1_typ as object (
  srt   number,
  val   varchar2(50 char)
);
/
create or replace type v1_vc2_tt as
  table of v1_typ;
/
create or replace type v2_vc2_tt as
  table of varchar2(50 char);
/
create or replace type split_result_typ as
  table of varchar2(50 char);
/
create or replace function v1_manyrows return v1_vc2_tt
  authid definer
is
  l_out   v1_vc2_tt := v1_vc2_tt ();
  l_row   v1_typ := v1_typ(null, null);
  -- we "mock" the result from a split function that returns a collection...
  l_str   split_result_typ := split_result_typ('Ask', 'The', 'Oracle', 'Masters');
begin
  for i in 1..l_str.count loop
    l_row.srt := i;
    l_row.val := l_str(i);
    l_out.extend ();
    l_out(i) := l_row;
  end loop;
  return l_out;
end v1_manyrows;
/
create or replace function v2_manyrows return v2_vc2_tt
  authid definer
is
  l_out   v2_vc2_tt := v2_vc2_tt ();
  -- we "mock" the result from a split function that returns a collection...
  l_str   split_result_typ := split_result_typ('Ask', 'The', 'Oracle', 'Masters');
begin
  for i in 1..l_str.count loop
    l_out.extend ();
    l_out(i) := l_str(i);
  end loop;
  return l_out;
end v2_manyrows;
/
create or replace function v1_manyrows_pipe return v1_vc2_tt
  authid definer
  pipelined
is
  l_row   v1_typ := v1_typ(null, null);
  -- we "mock" the result from a split function that returns a collection...
  l_str   split_result_typ := split_result_typ('Ask', 'The', 'Oracle', 'Masters');
begin
  for i in 1..l_str.count loop
    l_row.srt := i;
    l_row.val := l_str(i);
    pipe row ( l_row );
  end loop;
  return;
end v1_manyrows_pipe;
/
create or replace function v2_manyrows_pipe return v2_vc2_tt
  authid definer
  pipelined
is
  -- we "mock" the result from a split function that returns a collection...
  l_str   split_result_typ := split_result_typ('Ask', 'The', 'Oracle', 'Masters');
begin
  for i in 1..l_str.count loop
    pipe row ( l_str(i) );
  end loop;
  return;
end v2_manyrows_pipe;
/
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
select           srt,                 val from table ( v1_manyrows ); -- returntype is table of object
-- same as?
select rownum as srt, column_value as val from table ( v2_manyrows ); -- returntype is table of varchar2
--------------------------------------------------------------------------------
select           srt,                 val from table ( v1_manyrows_pipe ); -- returntype is table of object
-- same as?
select rownum as srt, column_value as val from table ( v2_manyrows_pipe ); -- returntype is table of varchar2