Sunday, January 11, 2026

Blog List

Posted by at

Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1

(I)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1       (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2

In this Blog, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions, and explore their complexity.

In next Blog: (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions for nested table column.

Note: Tested in Oracle 19.27


1. MEMBER vs. IN Performance Difference


We create two nested tables of number, and run query on them with MEMBER and IN Conditions respectively: 

---------------- test Number MEMBER vs. IN Comparison Conditions Performance ----------------

drop type t_num_tab;

create or replace noneditionable type t_num_tab is table of number;
/

create or replace procedure test_NUM_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
  l_t_num_tab_1    t_num_tab := t_num_tab();
  l_t_num_tab_2    t_num_tab := t_num_tab();
  l_start_time     number;
  l_ret_cnt        number;
begin
  select level bulk collect into l_t_num_tab_1 from dual connect by level <= p_size_1;
  select level bulk collect into l_t_num_tab_2 from dual connect by level <= p_size_2;
 
  dbms_output.put_line('l_t_num_tab_1.count   = '||cardinality(l_t_num_tab_1));
  dbms_output.put_line('l_t_num_tab_2.count   = '||l_t_num_tab_2.count);
   
  if p_sel in ('A', 'M') then
        --================= MEMBER Comparison =================
        l_start_time := dbms_utility.get_time;
       
        select /*+ Test_NUM_MEMBER */ count(*) into l_ret_cnt
          from table(cast (l_t_num_tab_1 as t_num_tab)) t1
         where t1.column_value member of cast(l_t_num_tab_2 as t_num_tab);
        
        dbms_output.put_line('=============== MEMBER Condition ===============');
        dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
        dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
  end if;
 
  if p_sel in ('A', 'I') then
        --================= IN Comparison =================
        l_start_time := dbms_utility.get_time;
       
        select /*+ Test_NUM_IN */ count(*) into l_ret_cnt
          from table(cast (l_t_num_tab_1 as t_num_tab)) t1
         where t1.column_value in (select column_value from table(cast(l_t_num_tab_2 as t_num_tab)));
       
        dbms_output.put_line('=============== IN Condition ===============');
        dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
        dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
      end if;
end;
/
The test result shows that the elapsed time for MEMBER is about 100 times greater than for IN. 

SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 1000);

      l_t_num_tab_1.count   = 100000
      l_t_num_tab_2.count   = 1000
      =============== MEMBER Condition ===============
      Elapsed(centi)        = 226
      l_ret_cnt             = 1000
      =============== IN Condition ===============
      Elapsed(centi)        = 2
      l_ret_cnt             = 1000
Here both xplan: 

SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
  SQL_ID  9v3d6bbpjs0x2, child number 0  Plan hash value: 3309076612
  -------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |      |       |       |    30 (100)|          |
  |   1 |  SORT AGGREGATE                    |      |     1 |     2 |            |          |
  |*  2 |   COLLECTION ITERATOR PICKLER FETCH|      |    50 |   100 |    30   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
    

SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
  SQL_ID  2z2buk4wrs0gs, child number 0  Plan hash value: 4146508332
  --------------------------------------------------------------------------------------------
  | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                    |      |       |       |    60 (100)|          |
  |   1 |  SORT AGGREGATE                     |      |     1 |     4 |            |          |
  |*  2 |   HASH JOIN RIGHT SEMI              |      |    10 |    40 |    60   (0)| 00:00:01 |
  |   3 |    COLLECTION ITERATOR PICKLER FETCH|      |   300 |   600 |    30   (0)| 00:00:01 |
  |   4 |    COLLECTION ITERATOR PICKLER FETCH|      |  1000 |  2000 |    30   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access(VALUE(KOKBF$)=VALUE(KOKBF$))
Here bpftrace on kopi2csaccess for both MEMBER and IN Comparison.
For MEMBER, count() = 10,094,950 is about (l_t_obj_tab_1.count * l_t_obj_tab_2.count), which is quadratic performance.
For IN, count() = 100102 is about (l_t_obj_tab_1.count + l_t_obj_tab_2.count), which is linear performance. 

--------------------- MEMBER Comparison ---------------------- 
SQL > exec test_NUM_MEMBE,R_vs_IN_Conditions(100000, 100, 'M');
        l_t_num_tab_1.count   = 100000
        l_t_num_tab_2.count   = 100
        =============== MEMBER Condition ===============
        Elapsed(centi)        = 1199
        l_ret_cnt             = 100
 
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
      @[
          kopi2csaccess+2
          qerocFetch+201
      ]: 100001
      @[
          kopi2csaccess+2
          expeal+59
      ]: 10094950
 
 
--------------------- IN Comparison --------------------------
SQL > exec test_NUM_MEMBER_vs_IN_Conditions(100000, 100, 'I');
        l_t_num_tab_1.count   = 100000
        l_t_num_tab_2.count   = 100
        =============== IN Condition ===============
        Elapsed(centi)        = 13
        l_ret_cnt             = 100
 
$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kopi2csaccess+2 /pid == 1129837/ {@[ustack(2)] = count();}'
      @[
          kopi2csaccess+2
          qerocFetch+201
      ]: 100102


2. MEMBER vs. IN complexity


We create nested table of objects with map member function to record number of Comparisons in MEMBER and IN queries in order to evaluate the complexity of applied algorithm, similar to the approaches of previous Blog:
     Performance of Oracle Object Collection Comparisons - Part1
     Performance of Oracle Object Collection Comparisons - Part2 

---------------- test Object MEMBER vs. IN Comparison Conditions Performance ----------------

create or replace package cmp_counter as
  p_cnt number := 0;
end;
/

create or replace package body helper as
  procedure format(p_name varchar2, p_value number) as
  begin
    dbms_output.put_line(rpad(p_name, 40) ||' = '|| lpad(p_value, 10));
  end format;
end helper;
/

drop type t_obj_tab force;

drop type t_obj force;

create or replace type t_obj as object (
  p_num number,
  map member function comp return integer);
/

create or replace type body t_obj as
  map member function comp return integer is 
    begin 
      cmp_counter.p_cnt := cmp_counter.p_cnt + 1;
      return p_num;
    end; 
end;
/

create or replace type t_obj_tab as table of t_obj;
/

create or replace procedure test_OBJ_MEMBER_vs_IN_Conditions (p_size_1 number, p_size_2 number, p_sel varchar2 := 'A') as
  l_t_obj_tab_1     t_obj_tab := t_obj_tab();
  l_t_obj_tab_2     t_obj_tab := t_obj_tab();
  l_start_time     number;
  l_ret_cnt        number;
begin
  select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_1 from dual connect by level <= p_size_1; 
  select cast(collect(t_obj(level)) as t_obj_tab) into l_t_obj_tab_2 from dual connect by level <= p_size_2; 
  
  dbms_output.put_line('l_t_obj_tab_1.count   = '||cardinality(l_t_obj_tab_1));
  dbms_output.put_line('l_t_obj_tab_2.count   = '||l_t_obj_tab_2.count);
    
  if p_sel in ('A', 'M') then
    --================= MEMBER Comparison =================
    cmp_counter.p_cnt := 0;
    l_start_time := dbms_utility.get_time;
    
    select /*+ Test_OBJ_MEMBER */ count(*) into l_ret_cnt 
      from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1 
     where t_obj(t1.p_num) member cast(l_t_obj_tab_2 as t_obj_tab);
     
    dbms_output.put_line('=============== MEMBER Condition ===============');
    dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
    dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
    dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt);
  end if;
       
  if p_sel in ('A', 'I') then
    --================= IN Operator =================
    cmp_counter.p_cnt := 0;
    l_start_time := dbms_utility.get_time;
    
    select /*+ Test_OBJ_IN */ count(*) into l_ret_cnt 
      from table(cast (l_t_obj_tab_1 as t_obj_tab)) t1 
     where t_obj(t1.p_num) in (select t_obj(t2.p_num) from table(cast(l_t_obj_tab_2 as t_obj_tab)) t2);
    
    dbms_output.put_line('=============== IN Condition ===============');
    dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
    dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
    dbms_output.put_line('Number of Comparisons = '||cmp_counter.p_cnt); 
  end if;
end;
/
We run the test: 

SQL> exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000);    

     l_t_obj_tab_1.count   = 10000
     l_t_obj_tab_2.count   = 1000
     =============== MEMBER Condition ===============
     Elapsed(centi)        = 4503
     l_ret_cnt             = 1000
     Number of Comparisons = 19001000
     =============== IN Condition ===============
     Elapsed(centi)        = 4
     l_ret_cnt             = 1000
     Number of Comparisons = 11000
The test result shows that the elapsed time for MEMBER is about 1000 times greater than for IN, and the number of Comparisons is more than 1000 times (elapsed time is proportional to number of Comparisons).

For MEMBER, the Number of Comparisons is about (l_t_obj_tab_1.count * l_t_obj_tab_2.count), which is quadratic performance.
For IN, the Number of Comparisons is about (l_t_obj_tab_1.count + l_t_obj_tab_2.count), which is linear performance.
Here both xplan: 

SELECT /*+ Test_NUM_MEMBER */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE MEMBER OF CAST(:B2 AS T_NUM_TAB);
  SQL_ID  9v3d6bbpjs0x2, child number 0  Plan hash value: 3309076612
  -------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |      |       |       |    30 (100)|          |
  |   1 |  SORT AGGREGATE                    |      |     1 |     2 |            |          |
  |*  2 |   COLLECTION ITERATOR PICKLER FETCH|      |    50 |   100 |    30   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - filter(VALUE(KOKBF$)MEMBER OFCAST(:B2 AS "T_NUM_TAB") )
    

SELECT /*+ Test_NUM_IN */ COUNT(*) FROM TABLE(CAST (:B1 AS T_NUM_TAB)) T1 WHERE T1.COLUMN_VALUE IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B2 AS T_NUM_TAB)));
  SQL_ID  2z2buk4wrs0gs, child number 0  Plan hash value: 4146508332
  --------------------------------------------------------------------------------------------
  | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                    |      |       |       |    60 (100)|          |
  |   1 |  SORT AGGREGATE                     |      |     1 |     4 |            |          |
  |*  2 |   HASH JOIN RIGHT SEMI              |      |    10 |    40 |    60   (0)| 00:00:01 |
  |   3 |    COLLECTION ITERATOR PICKLER FETCH|      |   300 |   600 |    30   (0)| 00:00:01 |
  |   4 |    COLLECTION ITERATOR PICKLER FETCH|      |  1000 |  2000 |    30   (0)| 00:00:01 |
  --------------------------------------------------------------------------------------------
  Predicate Information (identified by operation id):
  ---------------------------------------------------
     2 - access(VALUE(KOKBF$)=VALUE(KOKBF$))


3. bpftrace on PICKLER FETCH Calls


The Oracle subroutine for PICKLER FETCH is implemented by "kodpunp" (kernel objects data manager (pickler) unpickle an image into an object). We can use bpftrace to count the number of such calls.


3.1 MEMBER Comparison


The bpftrace count on kodpunp (from kgmtimob): 19001000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 19001000. 

SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'M');

        l_t_obj_tab_1.count   = 10000
        l_t_obj_tab_2.count   = 1000
        =============== MEMBER Condition ===============
        Elapsed(centi)        = 7526
        l_ret_cnt             = 1000
        Number of Comparisons = 19001000
        
        Elapsed: 00:01:15.28

$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
      @[
          kodpunp+2
          kpcocaup+599
      ]: 2
      @[
          kodpunp+2
          kopp2uattr+7010
      ]: 11000
      @[
          kodpunp+2
          kgmtimob+1002
      ]: 19001000


3.2 IN Comparison


The bpftrace count on kodpunp (from kgmtimob): 11000 matches Sqlplus(pid == 1128134) output: Number of Comparisons = 11000. 

SQL > exec test_OBJ_MEMBER_vs_IN_Conditions(10000, 1000, 'I');

        l_t_obj_tab_1.count   = 10000
        l_t_obj_tab_2.count   = 1000
        =============== IN Condition ===============
        Elapsed(centi)        = 5
        l_ret_cnt             = 1000
        Number of Comparisons = 11000
        
        Elapsed: 00:00:00.09

$ > bpftrace -e 'uprobe:/orabin/app/oracle/product/19.27.0.0.250415-212/bin/oracle:kodpunp+2 /pid == 1128134/ {@[ustack(2)] = count();}'
      @[
          kodpunp+2
          kpcocaup+599
      ]: 2
      @[
          kodpunp+2
          kgmtimob+1002
      ]: 11000
      @[
          kodpunp+2
          kopp2uattr+7010
      ]: 11000
Voila full Plsql callstack: 

#0    kodpunp ()
#1    kokoupkl ()
#2    kpcocaup ()
#3    kprcdt ()
#4    kprccu ()
#5    opifcr ()
#6    qergsFetch ()
#7    opifch2 ()
#8    opiefn0 ()
#9    opipls ()
#10   opiodr ()
#11   rpidrus ()
#12   skgmstack ()
#13   rpidru ()
#14   rpiswu2 ()
#15   rpidrv ()
#16   psddr0 ()
#17   psdnal ()
#18   pevm_EXECC ()
#19   pfrinstr_EXECC ()
#20   pfrrun_no_tool ()
#21   pfrrun ()
#22   plsql_run ()
Posted by at

Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2

(I)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1      (II)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part2

Continuing with last Blog: (I)-Oracle SQL MEMBER vs. IN Comparison Condition Performance - Part1,
in this Blog, we will demonstrate the performance difference of MEMBER and IN Comparison Conditions for nested table column,
and performance improvement by creating index on nested child table.

Note: Tested in Oracle 19.27


1. MEMBER vs. IN Performance Difference on Nested Table Column


We create a table containing one nested table column, and run tests with MEMBER vs. IN Conditions. 

drop table test_table;

create table test_table (id number, nt t_num_tab) nested table nt store as test_table_nt;

insert into test_table(id, nt) select level, t_num_tab(level, -level) from dual connect by level <= 1e5; 

commit; 

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

select table_name, num_rows, blocks, avg_row_len from dba_tables where table_name in ('TEST_TABLE', 'TEST_TABLE_NT');
  TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
  --------------- ---------- ---------- -----------
  TEST_TABLE          100000        496          22
  TEST_TABLE_NT       200000        874          22
 
select table_name, index_name, uniqueness, leaf_blocks, num_rows, distinct_keys from dba_indexes where table_name in ('TEST_TABLE', 'TEST_TABLE_NT');
  TABLE_NAME      INDEX_NAME                UNIQUENESS   LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS
  --------------- ------------------------- ------------ ----------- ---------- -------------
  TEST_TABLE      SYS_C00144532             UNIQUE               338     100000        100000
  TEST_TABLE_NT   SYS_FK0005992996N00002$   NONUNIQUE            702     200000        102008
 
select segment_name, bytes, blocks from dba_segments where segment_name in ('TEST_TABLE', 'TEST_TABLE_NT', 'SYS_C00142541', 'SYS_FK0005992996N00002$');
  SEGMENT_NAME                   BYTES     BLOCKS
  ------------------------- ---------- ----------
  TEST_TABLE                   4194304        512
  TEST_TABLE_NT                7340032        896
  SYS_C00144532                3145728        384
  SYS_FK0005992996N00002$      6291456        768

create or replace procedure test_TAB_MEMBER_vs_IN_Conditions (p_size number) as
  l_num_tab        t_num_tab := t_num_tab();
  l_start_time     number;
  l_ret_cnt        number;
begin
  select level bulk collect into l_num_tab from dual connect by level <= p_size;
  
  --================= MEMBER Comparison =================
  l_start_time := dbms_utility.get_time;
  
  select /*+ Test_TAB_MEMBER */ count(c.column_value) into l_ret_cnt 
    from test_table t, table(t.nt) c 
   where c.column_value member of cast(l_num_tab as t_num_tab);
   
  dbms_output.put_line('=============== MEMBER Condition ===============');
  dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
  dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);

  --================= IN Comparison =================
  l_start_time := dbms_utility.get_time;
  
  select /*+ Test_TAB_IN */ count(c.column_value) into l_ret_cnt 
    from test_table t, table(t.nt) c 
   where c.column_value in (select column_value from table(cast(l_num_tab as t_num_tab)));
  
  dbms_output.put_line('=============== IN Condition ===============');
  dbms_output.put_line('Elapsed(centi)        = '||(dbms_utility.get_time - l_start_time));
  dbms_output.put_line('l_ret_cnt             = '||l_ret_cnt);
end;
/
The test result shows that the elapsed time for MEMBER is about 100 times longer than for IN. 

SQL > exec test_TAB_MEMBER_vs_IN_Conditions(1000);

  =============== MEMBER Condition ===============
  Elapsed(centi)        = 449
  l_ret_cnt             = 1000
  =============== IN Condition ===============
  Elapsed(centi)        = 3
  l_ret_cnt             = 1000


2. MEMBER vs. IN Performance Difference on Lookup


Blog: MEMBER OF comparison of PL/SQL and SQL (Kim Berg Hansen June 23, 2014) shows the performance difference of single row select in PL/SQL and SQL, and demonstrate SQL performance improvement by creating index on nested child table.

Based on it, we will make tests to show the Performance Difference of lookup on nested table column with and without index on it by MEMBER or IN Comparison Conditions.

Note that this is not applicable to varray array column because it has no storage_table clause,
whereas nested table column has storage_table clause to store it as a normal heap table.


2.1 Single Key Lookup


First we make tests by single key lookup: 

alter session set tracefile_identifier = 'nt_single';
alter session set events '10046 trace name context forever, level 12'; 
 				
VARIABLE lookup_key NUMBER;
exec :lookup_key := 999;

------------- No Index ------------
drop index test_table_nt_indx;

select /*+ Test_TAB_MEMBER_LOOKUP_Single_NO_INDX */ count(t.id) from test_table t where :lookup_key member of cast(t.nt as t_num_tab);
select /*+ Test_TAB_IN_LOOKUP_Single_NO_INDX */ count(t.id) from test_table t, table(t.nt) c where c.column_value = :lookup_key;

------------- Index on column_value ------------
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

--MEMBER Comparison does not use INDEX 
select /*+ Test_TAB_MEMBER_LOOKUP_Single_INDX_Col_1 */ count(t.id) from test_table t where :lookup_key member of cast(t.nt as t_num_tab);
select /*+ Test_TAB_IN_LOOKUP_Single_INDX_Col_1 */ count(t.id) from test_table t, table(t.nt) c where c.column_value = :lookup_key;

------------- Index on (column_value, nested_table_id) ------------
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value, nested_table_id);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

select /*+ Test_TAB_IN_LOOKUP_Single_INDX_Col_2 */ count(t.id) from test_table t, table(t.nt) c where c.column_value = :lookup_key;

alter session set events '10046 trace name context off';
Here the SQL Trace (10046) output: 

********************************************************************************
SQL ID: f90kvg63f33fr Plan Hash: 440919791

select /*+ Test_TAB_MEMBER_LOOKUP_Single_NO_INDX */ count(t.id) 
from test_table t where :lookup_key member of cast(t.nt as t_num_tab)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.35       0.35          0     212122          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.36       0.36          0     212122          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=212122 pr=0 pw=0 time=359533 us starts=1)
         1  FILTER  (cr=212122 pr=0 pw=0 time=359523 us starts=1)
    100000   TABLE ACCESS FULL TEST_TABLE (cr=394 pr=0 pw=0 time=7314 us starts=1 cost=143 size=2200000 card=100000)
    200000   TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=211728 pr=0 pw=0 time=179841 us starts=100000 cost=2 size=44 card=2)
    200000    INDEX RANGE SCAN SYS_FK0005992996N00002$ (cr=111678 pr=0 pw=0 time=100304 us starts=100000 cost=2 size=0 card=2)(object id 5992998)

********************************************************************************
SQL ID: 3b57pxgx3ka1r Plan Hash: 299443699

select /*+ Test_TAB_IN_LOOKUP_Single_NO_INDX */ count(t.id) 
from test_table t, table(t.nt) c where c.column_value = :lookup_key

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        792          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        792          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=792 pr=0 pw=0 time=4927 us starts=1)
         1  NESTED LOOPS  (cr=792 pr=0 pw=0 time=4919 us starts=1 cost=252 size=44 card=1)
         1   NESTED LOOPS  (cr=791 pr=0 pw=0 time=4910 us starts=1 cost=252 size=44 card=1)
         1    TABLE ACCESS FULL TEST_TABLE_NT (cr=789 pr=0 pw=0 time=4900 us starts=1 cost=251 size=22 card=1)
         1    INDEX UNIQUE SCAN SYS_C00144532 (cr=2 pr=0 pw=0 time=7 us starts=1 cost=1 size=0 card=1)(object id 5992999)
         1   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=1 pr=0 pw=0 time=8 us starts=1 cost=1 size=22 card=1)
********************************************************************************

SQL ID: 5pyx85dkay5fz Plan Hash: 440919791

select /*+ Test_TAB_MEMBER_LOOKUP_Single_INDX_Col_1 */ count(t.id) 
from test_table t where :lookup_key member of cast(t.nt as t_num_tab)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.35       0.35          0     212122          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.35       0.35          0     212122          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=212122 pr=0 pw=0 time=357398 us starts=1)
         1  FILTER  (cr=212122 pr=0 pw=0 time=357388 us starts=1)
    100000   TABLE ACCESS FULL TEST_TABLE (cr=394 pr=0 pw=0 time=8078 us starts=1 cost=143 size=2200000 card=100000)
    200000   TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=211728 pr=0 pw=0 time=178477 us starts=100000 cost=2 size=44 card=2)
    200000    INDEX RANGE SCAN SYS_FK0005992996N00002$ (cr=111678 pr=0 pw=0 time=98691 us starts=100000 cost=2 size=0 card=2)(object id 5992998)
    
********************************************************************************

SQL ID: 77x73ayp09c9p Plan Hash: 1013981520

select /*+ Test_TAB_IN_LOOKUP_Single_INDX_Col_1 */ count(t.id) 
from test_table t, table(t.nt) c where c.column_value = :lookup_key

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=40 us starts=1)
         1  NESTED LOOPS  (cr=6 pr=0 pw=0 time=33 us starts=1 cost=2 size=44 card=1)
         1   NESTED LOOPS  (cr=5 pr=0 pw=0 time=26 us starts=1 cost=2 size=44 card=1)
         1    TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=3 pr=0 pw=0 time=18 us starts=1 cost=1 size=22 card=1)
         1     INDEX RANGE SCAN TEST_TABLE_NT_INDX (cr=2 pr=0 pw=0 time=14 us starts=1 cost=1 size=0 card=1)(object id 5993191)
         1    INDEX UNIQUE SCAN SYS_C00144532 (cr=2 pr=0 pw=0 time=6 us starts=1 cost=1 size=0 card=1)(object id 5992999)
         1   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=1 pr=0 pw=0 time=5 us starts=1 cost=1 size=22 card=1)
         
********************************************************************************

SQL ID: 535h2a2yb77z0 Plan Hash: 2041402069

select /*+ Test_TAB_IN_LOOKUP_Single_INDX_Col_2 */ count(t.id) 
from test_table t, table(t.nt) c where c.column_value = :lookup_key

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=6 pr=0 pw=0 time=32 us starts=1)
         1  NESTED LOOPS  (cr=6 pr=0 pw=0 time=25 us starts=1 cost=3 size=44 card=1)
         1   NESTED LOOPS  (cr=5 pr=0 pw=0 time=18 us starts=1 cost=3 size=44 card=1)
         1    INDEX RANGE SCAN TEST_TABLE_NT_INDX (cr=3 pr=0 pw=0 time=12 us starts=1 cost=2 size=22 card=1)(object id 5993192)
         1    INDEX UNIQUE SCAN SYS_C00144532 (cr=2 pr=0 pw=0 time=5 us starts=1 cost=1 size=0 card=1)(object id 5992999)
         1   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=1 pr=0 pw=0 time=6 us starts=1 cost=1 size=22 card=1)
Above test output shows the MEMBER Comparisons make 212122 block gets (query=212122), mostly on TEST_TABLE_NT and its foreign key index: SYS_FK0005992996N00002$ although both together has only about 1600 blocks.

Whereas IN Comparisons has query=792 when no index, and query=6 when there is the index on nested table column.


2.2 Multi Key Lookup


Now we run tests of lookup with multiple keys: 

alter session set tracefile_identifier = 'nt_multi';
alter session set events '10046 trace name context forever, level 12'; 
 				
drop table test_keys; 
create table test_keys as select level k from dual connect by level <= 100; 
begin
   dbms_stats.gather_table_stats(user, 'TEST_KEYS',    cascade=>true);
end;
/

------------- No Index ------------
drop index test_table_nt_indx;

select /*+ Test_TAB_MEMBER_LOOKUP_Multi_NO_INDX */ count(t.id) from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab);
select /*+ Test_TAB_IN_LOOKUP_Multi_NO_INDX */ count(t.id) from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

------------- Index on column_value ------------
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

--MEMBER Comparison does not use INDEX  
select /*+ Test_TAB_MEMBER_LOOKUP_Multi_INDX_Col_1 */ count(t.id) from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab);
select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_1 */ count(t.id) from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

------------- Index on (column_value, nested_table_id) ------------
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value, nested_table_id);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_2 */ count(t.id) from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

alter session set events '10046 trace name context off';
Here SQL Trace (10046) output: 

********************************************************************************

SQL ID: a9wc0w7m86gr3 Plan Hash: 2034372327

select /*+ Test_TAB_MEMBER_LOOKUP_Multi_NO_INDX */ count(t.id) 
from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     36.91      37.14          0   21173295          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     36.91      37.15          0   21173295          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=21173295 pr=0 pw=0 time=37148408 us starts=1)
       100  FILTER  (cr=21173295 pr=0 pw=0 time=36949905 us starts=1)
  10000000   MERGE JOIN CARTESIAN (cr=396 pr=0 pw=0 time=2089627 us starts=1 cost=14177 size=250000000 card=10000000)
       100    TABLE ACCESS FULL TEST_KEYS (cr=2 pr=0 pw=0 time=126 us starts=1 cost=3 size=300 card=100)
  10000000    BUFFER SORT (cr=394 pr=0 pw=0 time=1116011 us starts=100 cost=14174 size=2200000 card=100000)
    100000     TABLE ACCESS FULL TEST_TABLE (cr=394 pr=0 pw=0 time=8323 us starts=1 cost=142 size=2200000 card=100000)
  20000000   TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=21172899 pr=0 pw=0 time=18230528 us starts=10000000 cost=2 size=44 card=2)
  20000000    INDEX RANGE SCAN SYS_FK0005992996N00002$ (cr=11167899 pr=0 pw=0 time=10198247 us starts=10000000 cost=2 size=0 card=2)(object id 5992998)
********************************************************************************

SQL ID: ftr39jncgyj2r Plan Hash: 768666713

select /*+ Test_TAB_IN_LOOKUP_Multi_NO_INDX */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0        900          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0        900          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=900 pr=0 pw=0 time=20282 us starts=1)
       100  NESTED LOOPS  (cr=900 pr=0 pw=0 time=787 us starts=1 cost=304 size=4700 card=100)
       100   NESTED LOOPS  (cr=800 pr=0 pw=0 time=583 us starts=1 cost=304 size=4700 card=100)
       100    HASH JOIN  (cr=791 pr=0 pw=0 time=375 us starts=1 cost=254 size=2500 card=100)
       100     TABLE ACCESS FULL TEST_KEYS (cr=2 pr=0 pw=0 time=45 us starts=1 cost=3 size=300 card=100)
    200000     TABLE ACCESS FULL TEST_TABLE_NT (cr=789 pr=0 pw=0 time=23571 us starts=1 cost=251 size=4400000 card=200000)
       100    INDEX UNIQUE SCAN SYS_C00144532 (cr=9 pr=0 pw=0 time=47 us starts=100 cost=1 size=0 card=1)(object id 5992999)
       100   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=100 pr=0 pw=0 time=50 us starts=100 cost=1 size=22 card=1)
********************************************************************************

SQL ID: gqxbmzjcrt5p1 Plan Hash: 2034372327

select /*+ Test_TAB_MEMBER_LOOKUP_Multi_INDX_Col_1 */ count(t.id) 
from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2     36.68      36.92          0   21173295          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4     36.69      36.92          0   21173295          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=21173295 pr=0 pw=0 time=36926973 us starts=1)
       100  FILTER  (cr=21173295 pr=0 pw=0 time=35871619 us starts=1)
  10000000   MERGE JOIN CARTESIAN (cr=396 pr=0 pw=0 time=2062528 us starts=1 cost=14177 size=250000000 card=10000000)
       100    TABLE ACCESS FULL TEST_KEYS (cr=2 pr=0 pw=0 time=130 us starts=1 cost=3 size=300 card=100)
  10000000    BUFFER SORT (cr=394 pr=0 pw=0 time=1081777 us starts=100 cost=14174 size=2200000 card=100000)
    100000     TABLE ACCESS FULL TEST_TABLE (cr=394 pr=0 pw=0 time=7557 us starts=1 cost=142 size=2200000 card=100000)
  20000000   TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=21172899 pr=0 pw=0 time=18120678 us starts=10000000 cost=2 size=44 card=2)
  20000000    INDEX RANGE SCAN SYS_FK0005992996N00002$ (cr=11167899 pr=0 pw=0 time=10102918 us starts=10000000 cost=2 size=0 card=2)(object id 5992998)
********************************************************************************

SQL ID: 3hww8x82t497g Plan Hash: 3838096326

select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_1 */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        120          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        120          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=120 pr=0 pw=0 time=336 us starts=1)
       100  NESTED LOOPS  (cr=120 pr=0 pw=0 time=1052 us starts=1 cost=103 size=4700 card=100)
       100   NESTED LOOPS  (cr=20 pr=0 pw=0 time=849 us starts=1 cost=103 size=4700 card=100)
       100    NESTED LOOPS  (cr=11 pr=0 pw=0 time=544 us starts=1 cost=53 size=2500 card=100)
       100     TABLE ACCESS FULL TEST_KEYS (cr=2 pr=0 pw=0 time=132 us starts=1 cost=3 size=300 card=100)
       100     TABLE ACCESS BY INDEX ROWID BATCHED TEST_TABLE_NT (cr=9 pr=0 pw=0 time=115 us starts=100 cost=1 size=22 card=1)
       100      INDEX RANGE SCAN TEST_TABLE_NT_INDX (cr=8 pr=0 pw=0 time=74 us starts=100 cost=1 size=0 card=1)(object id 5993194)
       100    INDEX UNIQUE SCAN SYS_C00144532 (cr=9 pr=0 pw=0 time=46 us starts=100 cost=1 size=0 card=1)(object id 5992999)
       100   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=100 pr=0 pw=0 time=48 us starts=100 cost=1 size=22 card=1)
********************************************************************************

SQL ID: 5ghun4988xh7w Plan Hash: 2752258125

select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_2 */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0        136          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0        136          0           1

Rows (1st) Row Source Operation
---------- ---------------------------------------------------
         1 SORT AGGREGATE (cr=136 pr=0 pw=0 time=275 us starts=1)
       100  NESTED LOOPS  (cr=136 pr=0 pw=0 time=837 us starts=1 cost=103 size=4700 card=100)
       100   NESTED LOOPS  (cr=36 pr=0 pw=0 time=831 us starts=1 cost=103 size=4700 card=100)
       100    NESTED LOOPS  (cr=27 pr=0 pw=0 time=528 us starts=1 cost=53 size=2500 card=100)
       100     TABLE ACCESS FULL TEST_KEYS (cr=2 pr=0 pw=0 time=121 us starts=1 cost=3 size=300 card=100)
       100     INDEX RANGE SCAN TEST_TABLE_NT_INDX (cr=25 pr=0 pw=0 time=83 us starts=100 cost=1 size=22 card=1)(object id 5993195)
       100    INDEX UNIQUE SCAN SYS_C00144532 (cr=9 pr=0 pw=0 time=44 us starts=100 cost=1 size=0 card=1)(object id 5992999)
       100   TABLE ACCESS BY INDEX ROWID TEST_TABLE (cr=100 pr=0 pw=0 time=47 us starts=100 cost=1 size=22 card=1)
Above test output shows for 100 keys, the MEMBER Comparisons make 21,173,295 block gets (query=21173295), mostly on TEST_TABLE_NT and its foreign key index: SYS_FK0005992996N00002$, which is about 100 times of single key lookup (query=212,122)

Whereas IN Comparisons has query=900 when no index, and query=120/136 when there is the index on nested table column, which are linear to number of lookup keys.

Sqltraces shows that for MEMBER Comparison, it is much slower than IN Comparison (37.15 vs. 0.02 seconds)
because there are much higher block gets (query = 21173295 vs. 900), and index on nested table column is not used.

When there are Index on (column_value) or (column_value, nested_table_id), the performance is further improved (query = 900 vs. 120/136).

For Index on (column_value), index TEST_TABLE_NT_INDX and SYS_C00144532 are used, but still need TABLE ACCESS BY INDEX ROWID BATCHED on TEST_TABLE_NT.

For Index on (column_value, nested_table_id), index TEST_TABLE_NT_INDX and SYS_C00144532 are used, no more TEST_TABLE_NT access.

Here xplan with Predicate Information: 

--======================= No Index MEMBER Comparison =======================--
drop index test_table_nt_indx;

select /*+ Test_TAB_MEMBER_LOOKUP_Multi_NO_INDX */ count(t.id) 
from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab);

Plan hash value: 2034372327
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                         |     1 |    25 | 14179   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                       |                         |     1 |    25 |            |          |
|*  2 |   FILTER                              |                         |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN               |                         |    10M|   238M| 14177   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                 | TEST_KEYS               |   100 |   300 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT                       |                         |   100K|  2148K| 14174   (1)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                | TEST_TABLE              |   100K|  2148K|   142   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE_NT           |     2 |    44 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                  | SYS_FK0005992996N00002$ |     2 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("L"."K"MEMBER OFCAST("T"."NT" AS "T_NUM_TAB") )
   8 - access("NESTED_TABLE_ID"=:B1)

--======================= No Index IN Comparison =======================--
select /*+ Test_TAB_IN_LOOKUP_Multi_NO_INDX */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

Plan hash value: 768666713
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    47 |   304   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |               |     1 |    47 |            |          |
|   2 |   NESTED LOOPS                |               |   100 |  4700 |   304   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |               |   100 |  4700 |   304   (0)| 00:00:01 |
|*  4 |     HASH JOIN                 |               |   100 |  2500 |   254   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL        | TEST_KEYS     |   100 |   300 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL        | TEST_TABLE_NT |   200K|  4296K|   251   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | SYS_C00144532 |     1 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| TEST_TABLE    |     1 |    22 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("C"."COLUMN_VALUE"="L"."K")
   7 - access("C"."NESTED_TABLE_ID"="T"."SYS_NC0000200003$")
   
--======================= Index on (column_value), MEMBER Comparison =======================--
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

select /*+ Test_TAB_MEMBER_LOOKUP_Multi_INDX_Col_1 */ count(t.id) 
from test_table t, test_keys l where l.k member of cast(t.nt as t_num_tab);

Plan hash value: 2034372327
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                         |     1 |    25 | 14179   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE                       |                         |     1 |    25 |            |          |
|*  2 |   FILTER                              |                         |       |       |            |          |
|   3 |    MERGE JOIN CARTESIAN               |                         |    10M|   238M| 14177   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                 | TEST_KEYS               |   100 |   300 |     3   (0)| 00:00:01 |
|   5 |     BUFFER SORT                       |                         |   100K|  2148K| 14174   (1)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                | TEST_TABLE              |   100K|  2148K|   142   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE_NT           |     2 |    44 |     2   (0)| 00:00:01 |
|*  8 |     INDEX RANGE SCAN                  | SYS_FK0005992996N00002$ |     2 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("L"."K"MEMBER OFCAST("T"."NT" AS "T_NUM_TAB") )
   8 - access("NESTED_TABLE_ID"=:B1)

--======================= Index on (column_value), IN Comparison =======================--
select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_1 */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

Plan hash value: 3838096326
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |     1 |    47 |   103   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                         |                    |     1 |    47 |            |          |
|   2 |   NESTED LOOPS                          |                    |   100 |  4700 |   103   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                         |                    |   100 |  4700 |   103   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                    |   100 |  2500 |    53   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL                  | TEST_KEYS          |   100 |   300 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE_NT      |     1 |    22 |     1   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN                  | TEST_TABLE_NT_INDX |     1 |       |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN                   | SYS_C00144532      |     1 |       |     1   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID          | TEST_TABLE         |     1 |    22 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("C"."COLUMN_VALUE"="L"."K")
   8 - access("C"."NESTED_TABLE_ID"="T"."SYS_NC0000200003$")

--======================= Index on (column_value, nested_table_id), IN Comparison =======================--
drop index test_table_nt_indx;
create index test_table_nt_indx on test_table_nt(column_value, nested_table_id);

begin
   dbms_stats.gather_table_stats(user, 'TEST_TABLE',    cascade=>true);
   dbms_stats.gather_table_stats(user, 'TEST_TABLE_NT', cascade=>true);
end;
/

select /*+ Test_TAB_IN_LOOKUP_Multi_INDX_Col_2 */ count(t.id) 
from test_table t, table(t.nt) c, test_keys l where c.column_value = l.k;

Plan hash value: 2752258125
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |     1 |    47 |   103   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |                    |     1 |    47 |            |          |
|   2 |   NESTED LOOPS                |                    |   100 |  4700 |   103   (0)| 00:00:01 |
|   3 |    NESTED LOOPS               |                    |   100 |  4700 |   103   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |                    |   100 |  2500 |    53   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL        | TEST_KEYS          |   100 |   300 |     3   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN         | TEST_TABLE_NT_INDX |     1 |    22 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN         | SYS_C00144532      |     1 |       |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID| TEST_TABLE         |     1 |    22 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("C"."COLUMN_VALUE"="L"."K")
   7 - access("C"."NESTED_TABLE_ID"="T"."SYS_NC0000200003$")


3. Nested Table Internals


Referring to early Blog: Foreign Key on Nested Table, we can cross check with Oracle internal table and index DDL: 

select o.object_name table_name, c.name column_name, c.type# column_type
  from   dba_objects o, sys.col$ c
 where c.obj# = o.object_id
   and o.object_name in ('TEST_TABLE', 'TEST_TABLE_NT')
 order by o.object_name, c.name;
 
   TABLE_NAME      COLUMN_NAME          COLUMN_TYPE
  --------------- -------------------- -----------
  TEST_TABLE      ID                             2
  TEST_TABLE      NT                           122
  TEST_TABLE      SYS_NC0000200003$             23
  TEST_TABLE_NT   COLUMN_VALUE                   2
  TEST_TABLE_NT   NESTED_TABLE_ID               23

select index_name, table_name, column_name 
  from  dba_ind_columns 
 where table_name in ('TEST_TABLE', 'TEST_TABLE_NT');

  INDEX_NAME                TABLE_NAME      COLUMN_NAME
  ------------------------- --------------- --------------------
  SYS_C00144532             TEST_TABLE      NT
  TEST_TABLE_NT_INDX        TEST_TABLE_NT   COLUMN_VALUE
  TEST_TABLE_NT_INDX        TEST_TABLE_NT   NESTED_TABLE_ID
  SYS_FK0005992996N00002$   TEST_TABLE_NT   NESTED_TABLE_ID
Above xplan shows that for MEMBER Comparison, foreign key index SYS_FK0005992996N00002$ on TEST_TABLE_NT is used, and index on nested table column is not used.

For IN Comparison, when no index on nested table column, TABLE ACCESS FULL on TEST_TABLE_NT is used with table index SYS_C00144532.

When there is Index on (column_value), index TEST_TABLE_NT_INDX and SYS_C00144532 are used, but still need TABLE ACCESS BY INDEX ROWID BATCHED on TEST_TABLE_NT.

When there is Index on (column_value, nested_table_id), index TEST_TABLE_NT_INDX and SYS_C00144532 are used, no more TEST_TABLE_NT access.
Posted by at
Subscribe to: Comments (Atom)