(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;
/
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';
********************************************************************************
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)
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';
********************************************************************************
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)
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
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.