Минусы использования "member of" с коллекциями и вложенными таблицами

Давно хотел написать о минусах использования "a member of b" перед обычными старыми вариантами вроде a in (select column_value from table(b)).
Резюме: "member of" в sql не умеет хорошо работать с индексами и использовать его желательно только в pl/sql - в этом случае по производительности практически одинаков с собственной функцией с циклом проверки(это покажу в самом конце).

Рассмотрим два варианта использования member of:

1. запрос с условием, где поле таблицы должно входить в коллекцию
2. запрос по таблице с вложенной таблицей и условием вхождения переменной во вложенную таблицу

И в первом и втором можно создать индексы, по которым отлично можно было бы искать необходимые поля с index range/unique scan, но в случае с member of ситуация гораздо хуже:

• В первом варианте можно добиться только index fast full scan
• Во втором еще хуже: будет сначала фулл скан по родительской таблице, от нее index range scan по вложенной с access только по полю nested_table_id - полю связи родительской с вложенной

Вариант 1. Поиск записей по вхождению поля в заданную коллекцию

Рассмотрим сначала первый вариант - поиск где поле входит в коллекцию.
Текст примера:

--drop table member_of_test1 purge;

create table member_of_test1(n primary key)
as
select level from dual connect by level<1e5
/
begin
  dbms_stats.gather_table_stats(user,'member_of_test1',cascade => true);
end;
/
explain plan for 
select * from member_of_test1 
where member_of_test1.n member of cast (:a as number_table)
/
select * from table(dbms_xplan.display)
/
explain plan for 
select * from member_of_test1 
where member_of_test1.n in (select column_value from table(cast (:a as number_table)))
/
select * from table(dbms_xplan.display)
/

План с member of:

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

Plan hash value: 3157173230

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |  5000 | 25000 |    49   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MEMBER_OF_TEST1 |  5000 | 25000 |    49   (7)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("MEMBER_OF_TEST1"."N"MEMBER OFCAST(:A AS "NUMBER_TABLE") )

План с "in (select * from table(:collection))":

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

Plan hash value: 3898737298

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |   255 |  1785 |    30   (4)| 00:00:01 |
|   1 |  NESTED LOOPS                       |              |   255 |  1785 |    30   (4)| 00:00:01 |
|   2 |   SORT UNIQUE                       |              |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH|              |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN                 | SYS_C0024557 |     1 |     5 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - access("MEMBER_OF_TEST1"."N"=VALUE(KOKBF$))

Разница очевидна: full table scan против index unique scan'a! Кроме того, в варианте с "n in (select * from table(:collection))" можно было бы еще и получить concatenation при использовании хинта precompute_subquery.

Вариант 2. Поиск по вложенной таблице

Cоздадим тестовую таблицу с nested table. sys.ku$_ObjNumSet - это table of number.
Кстати, с 11.2 в nested table уже автоматически создается индекс для nested_table_id, поэтому следовать совету из 11.1 "Design Considerations for Nested Tables" уже не нужно, если цель только быстро доставать чайлдов из nested table от родителя.
Но т.к. мы хотим проверить поиск именно от nested table к родительской, то создадим свой

create table nt_test
(
  id integer
 ,nt sys.ku$_ObjNumSet
 ,primary key(id) using index( create unique index pk_nt_test on nt_test(id) )
)
nested table nt 
store as nt_table(
   (
    column_value not null
   ,constraint pk_nt_table primary key(nested_table_id,column_value)
   )
)
/

На каждую запись из родительской вставим три во вложенной:

insert into nt_test 
select level
      ,sys.ku$_ObjNumSet(level,level+1,level+2)
from dual
connect by level<=1e4
/

еще создадим индекс, который по идее помог бы нам в поиске сначала по значениям в nested table:

create index ix_nt_table_good on nt_table(column_value,nested_table_id);

Т.к. мы знаем, что в родительской таблице автоматически создается индекс на поле связи с дочерней таблицей(nested_table_id), то попробуем его получить:

col column_name format a30

select i.index_name,ic.TABLE_NAME ,ic.COLUMN_POSITION,ic.COLUMN_NAME 
from user_indexes i
    ,user_ind_columns ic
where 
    i.table_name='NT_TEST'
and i.index_name=ic.INDEX_NAME;

Вы увидите, что название этого столбца якобы соответствует названию nested table, которое было в ddl создания таблицы. Однако стоит только попытаться создать индекс с ним, и мы поймем что такого просто нет. Зато можем его получить из dba_cons_columns и сохранить, чтобы затем создать индекс с ним:

column column_name new_val col_name;

select  c.constraint_name,c.constraint_type,c.table_name
       ,c.index_name
       ,cc.position
       ,cc.column_name
from user_constraints c
    ,user_cons_columns cc
where 
    c.owner=user
and c.table_name='NT_TEST'
and c.constraint_type='U'
and cc.owner=c.owner
and cc.constraint_name=c.constraint_name
;
-- создадим теперь полный индекс с этим полем связи:
create unique index ix_nt_test_parent on nt_test (&col_name, id);

Соберем статистику:

begin
-- параметр cascade не включает сбор по вложенной таблице
  dbms_stats.gather_table_stats( user
                                ,'NT_TEST'
                                ,estimate_percent => 10
                                ,method_opt => 'FOR ALL COLUMNS SIZE 1'
                                ,cascade => true
                                );
-- поэтому по ней тоже надо собирать:
  dbms_stats.gather_table_stats( user
                                ,'NT_TABLE'
                                ,estimate_percent => 10
                                ,method_opt => 'FOR ALL COLUMNS SIZE 1'
                                ,cascade => true
                                );
end;

Теперь наконец посмотрим какие планы получаются:

-- План с member of:
explain plan for
select *
from nt_test t
where :n1 member of(nt);

select * from table(dbms_xplan.display);
----------------------------------
-- План с in (select * from table(...))
explain plan for
select *
from nt_test t
where :n1 in (select * from table(nt));

select * from table(dbms_xplan.display);

Member of:

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------

Plan hash value: 3175250264

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  9918 |   203K|    15   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN  | PK_NT_TABLE |     3 |    63 |     2   (0)| 00:00:01 |
|*  2 |  FILTER            |             |       |       |            |          |
|   3 |   TABLE ACCESS FULL| NT_TEST     |  9918 |   203K|    13   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN | PK_NT_TABLE |     3 |    63 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

   1 - access("NESTED_TABLE_ID"=:B1)
   2 - filter(:N1MEMBER OF"NT")
   4 - access("NESTED_TABLE_ID"=:B1)

:value in (select * from table(nt)):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

Plan hash value: 3039725448

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     3 |   126 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN | PK_NT_TABLE       |     3 |    63 |     2   (0)| 00:00:01 |
|   2 |  NESTED LOOPS     |                   |     3 |   126 |     5   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| IX_NT_TABLE_GOOD  |     3 |    63 |     2   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN| IX_NT_TEST_PARENT |     1 |    21 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("NESTED_TABLE_ID"=:B1)
   3 - access("NT_TABLE"."COLUMN_VALUE"=TO_NUMBER(:N1))
   4 - access("NT_TABLE"."NESTED_TABLE_ID"="SYS_NC0000200003$")

Как видно, в варианте ":value in (select * from table(nt))" поиск происходит сразу по вложенной таблице с Index range scan и access по значению, в отличие от "member of", где сначала фулсканится родительская таблица и от нее уже по полю связи(NESTED_TABLE_ID) происходит lookup к вложенной таблице с фильтром по значению.

Вариант 3. "Member of" в PL/SQL

В PL/SQL особого смысла сравнивать, конечно, нет, но на всякий случай покажу, что использование member of вполне оправданно - по производительности лишь чуть-чуть проигрывает варианту с собственной функцией с компиляцией в native и plsql_optimize_level=3, зато универсальна и без лишних зависимостей. Код примера:

alter session set PLSQL_CODE_TYPE=NATIVE PLSQL_OPTIMIZE_LEVEL=3
/
create or replace function member_of2( p_element    integer
                                      ,p_collection sys.ku$_ObjNumSet
                                     ) 
                                     return boolean 
is
begin
  if p_element is null or p_collection is null then
    return null;
  else
    for i in p_collection.first..p_collection.last loop
      if p_element = p_collection(i) then
        return true;
      end if;
    end loop;
  end if;
exception when value_error then
  return false;
end;
/
declare
  a       sys.ku$_ObjNumSet:=sys.ku$_ObjNumSet();
  cnt     integer := 20000;
  b       integer := 0;
  l_timer integer := dbms_utility.get_time;
  procedure print(msg varchar2) is
  begin
    dbms_output.put_line(to_char((dbms_utility.get_time-l_timer)/100,'9999.99')||' '||msg);
    l_timer:=dbms_utility.get_time;
  end;
  
begin
  a.extend(cnt);
  for i in 1..cnt loop
    a(i):=i;
  end loop;

  print('Start');

  b:=0;  
  for i in 1..cnt loop
    if i member of a then
      b:=b+1;
    end if;
  end loop;
  print('1 finished. b='||b);
  
  b:=0;
  for i in 1..cnt loop
    if member_of2(i,a) then
      b:=b+1;
    end if;
  end loop;
  print('2 finished. b='||b);
end;
/

Результат:

• Member of - 8.75 сек.
• Собственная функция - 8.59.