Cardinality of table functions - using dynamic_sampling(2) for correct cardinality estimation

1.test.sql

set echo on;
create or replace type number_table as table of number;
/
create or replace function get_nums(n int) return number_table
as
  res number_table;
begin
  res:=number_table();
  res.extend(n);
  for x in 1..n loop
    res(x):=x;
  end loop;
  return res;
end;
/
set feed on sql_id
select/*+ dynamic_sampling(2) */ * from table(get_nums(5));
select * from table(dbms_xplan.display_cursor('&&_SQL_ID',null,'typical'));

2.output.sql

SQL_ID  2kgpvvvjg7ujm, child number 0
-------------------------------------
select/*+ dynamic_sampling(2) */ * from table(get_nums(5))

Plan hash value: 1434798570

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |       |       |    11 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_NUMS |     5 |    10 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

3.output.full.sql

SQL> create or replace type number_table as table of number;
  2  /

Type created.

SQL_ID: 29pbdvqpnwd76
SQL> create or replace function get_nums(n int) return number_table
  2  as
  3    res number_table;
  4  begin
  5    res:=number_table();
  6    res.extend(n);
  7    for x in 1..n loop
  8      res(x):=x;
  9    end loop;
 10    return res;
 11  end;
 12  /

Function created.

SQL_ID: 8r8y8brfn8hyj
SQL> set feed on sql_id
SQL> select/*+ dynamic_sampling(2) */ * from table(get_nums(5));

COLUMN_VALUE
------------
           1
           2
           3
           4
           5

5 rows selected.

SQL_ID: 2kgpvvvjg7ujm
SQL> select * from table(dbms_xplan.display_cursor('&&_SQL_ID',null,'typical'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  2kgpvvvjg7ujm, child number 0
-------------------------------------
select/*+ dynamic_sampling(2) */ * from table(get_nums(5))

Plan hash value: 1434798570

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |       |       |    11 (100)|          |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| GET_NUMS |     5 |    10 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


17 rows selected.

SQL_ID: b0kgq8f2ty8vs