Cardinality of table collection expression

Table collection expression has been in Oracle for more than 10 years. It is handy for passing an array to Oracle. Quite often, though, there is was a well-known problem with a default cardinality associated with table collection expressions used in the SQL.

The problem is Oracle considers a default cardinality based on the database’ block size:

create or replace type t_array as table of number;
/
drop table t1 cascade constraints purge;

create table t1 as 
with t as (select null from all_objects where rownum <= 1000)
select rownum id,
       lpad('x', 10, 'x') pad
  from t t11, t t12
 where rownum <= 100000;
 
create unique index t1_uq on t1(id);

exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, no_invalidate=>false);

explain plan for
select * from t1
 where id in (select * from table(cast(:1 as t_array)));
 
select * from table(dbms_xplan.display);

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     1 |    18 |   115   (2)| 00:00:02 |
|*  1 |  HASH JOIN RIGHT SEMI              |      |     1 |    18 |   115   (2)| 00:00:02 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL                | T1   |   100K|  1562K|    85   (2)| 00:00:02 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=VALUE(KOKBF$))

Depending on the db_block_size, cardinality estimate in the example above would be:

DB block size	Default cardinality
2K	2024
4K	4072
8K	8168
16K	16360
32K	? (please leave a comment if you know the number)

Due to default value for cardinality estimate, which seems to be too high, one does something like this (assuming that a collection would usually contain 10 values on average):

explain plan for
select * from t1
 where id in (select /*+ cardinality(t 10) */ * from table(cast(:1 as t_array)) t);
 
select * from table(dbms_xplan.display);

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |    10 |   180 |    35   (3)| 00:00:01 |
|   1 |  NESTED LOOPS                        |       |       |       |            |          |
|   2 |   NESTED LOOPS                       |       |    10 |   180 |    35   (3)| 00:00:01 |
|   3 |    SORT UNIQUE                       |       |    10 |    20 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|       |    10 |    20 |    29   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                 | T1_UQ |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID        | T1    |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ID"=VALUE(KOKBF$))

Oracle considers 10 values in the collection now and it can pick up different plan based on index (also see PS on plan shape). As always there are different ways to achieve similar effect, for more information refer to this excellent article by Adrian Billington.
EXPLAIN PLAN in this examples hides one of those nice features I could have missed: Oracle 11.2.0.1 peeks at the size of the collection at run time!

set serveroutput on
declare
    l_array t_array := t_array(1, 2, 3);
    l_tmp number;
begin
    select count(*) into l_tmp from table(l_array);
    for x  in (select * from table(dbms_xplan.display_cursor)) loop
        dbms_output.put_line(x.plan_table_output);
    end loop;
end;
/

SQL_ID  6pcf4quh5gksw, child number 0
-------------------------------------
SELECT COUNT(*) FROM TABLE(:B1 )
Plan hash value: 3309076612
-----------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |    29 (100)|          |
|   1 |  SORT AGGREGATE                    |      |     1 |            |          |
|   2 |   COLLECTION ITERATOR PICKLER FETCH|      |     3 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Thanks to the bug 7375077 it is here in 11.2.0.1 and will be in 10.2.0.5 (which is great):

SQL> select bugno, description, optimizer_feature_enable
  2    from v$session_fix_control
  3   where session_id = userenv('sid')
  4     and bugno = 7375077;

               BUGNO DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE
-------------------- ---------------------------------------------------------------- -------------------------
             7375077 use the number of elements in a collection as table cardinality  10.2.0.5

The new feature most likely is implemented as part of the bind variable peeking:

alter system flush shared_pool;
alter session set "_optim_peek_user_binds"=false;

set serveroutput on
declare
    l_array t_array := t_array(1, 2, 3);
    l_tmp number;
begin
    select count(*) into l_tmp from table(l_array);
    for x  in (select * from table(dbms_xplan.display_cursor)) loop
        dbms_output.put_line(x.plan_table_output);
    end loop;
end;
/

SQL_ID  6pcf4quh5gksw, child number 0
-------------------------------------
SELECT COUNT(*) FROM TABLE(:B1 )
Plan hash value: 3309076612
-----------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |    29 (100)|          |
|   1 |  SORT AGGREGATE                    |      |     1 |            |          |
|   2 |   COLLECTION ITERATOR PICKLER FETCH|      |  8168 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

It is working with thin JDBC driver versions 11.2.0.1 and 10.2.0.4 too:

import java.sql.*;
import oracle.sql.*;

public class testOraArray {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("oracle.jdbc.OracleDriver");
        return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:test11g2", "tim", "t");
    }    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = getConnection();
        
        PreparedStatement pstmt = conn.prepareStatement("select * from t1 where id in (select * from table(?))");
        pstmt.setObject(1, getARRAY(conn, Integer.parseInt(args[0])));
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            rs.getObject(1);
        }
        
        rs = conn.createStatement().executeQuery("select * from table(dbms_xplan.display_cursor)");
        while (rs.next()) {
            System.out.println(rs.getObject(1));
        }
    }
    private static ARRAY getARRAY(Connection conn, int size) throws SQLException {
        ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("T_ARRAY", conn);
        int[] data = new int[size];
        for (int i=0; i<size; i++) {
            data[i] = i;
        }
        return new ARRAY(arrayDesc, conn, data);
    }
}

(shared pool was flushed before running)

[oracle@localhost test]$ java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:. testOraArray 100
SQL_ID  16qq7y0x5b94y, child number 0
-------------------------------------
select * from t1 where id in (select * from table(:1 ))
 
Plan hash value: 3697464631
 
----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |    33 (100)|          |
|   1 |  NESTED LOOPS                        |       |       |       |            |          |
|   2 |   NESTED LOOPS                       |       |     3 |    54 |    33   (4)| 00:00:01 |
|   3 |    SORT UNIQUE                       |       |   100 |   200 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|       |   100 |   200 |    29   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                 | T1_UQ |     1 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID        | T1    |     1 |    16 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("ID"=VALUE(KOKBF$))

It seems like the feature is not considered in the adaptive cursor sharing code path and you’ll have the plan depending on the collection size at the bind peeking. Nevertheless, it’s great that Oracle finally did this little but useful enhancement.

PS. Oracle 11.2.0.1 builds a strange plan with two nested loops instead of just one as it was in the 10g probably because of a new nested loop batching optimization:

SQL> alter session set "_nlj_batching_enabled"=0;

Session altered.

SQL> explain plan for
  2  select * from t1
  3   where id in (select /*+ cardinality(t 10) */ * from table(cast(:1 as t_array)) t);

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4108064342

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |    10 |   180 |    35   (3)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID         | T1    |     1 |    16 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                       |       |    10 |   180 |    35   (3)| 00:00:01 |
|   3 |    SORT UNIQUE                       |       |    10 |    20 |    29   (0)| 00:00:01 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH|       |    10 |    20 |    29   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                 | T1_UQ |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ID"=VALUE(KOKBF$))

PPS. BTW, I’ve updated Tom Kyte in Moscow with links to audio recordings.