Минусы использования "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 - полю связи родительской с вложенной

Дальше »