Индекс не используется в запросе оракула с коллекцией, где условие

Question

У меня есть таблица событий с именем MyTable, в этой таблице у меня есть столбец MyTableId, по которому мы создали индекс. Эта таблица имеет 70 миллионов строк. Теперь я создал процедуру очистки, которая очищает события на основе коллекции табличных типов MyTableCollection. коллекция имеет ограничение в 100 строк. Итак, в целом мы можем удалить 100 строк за раз. Но когда я запустил приведенный ниже запрос в процедуре, он застрял на 40 минут.

DELETE  FROM MyTable
            WHERE   MyTableId IN (SELECT MyTableId FROM TABLE(MyTableCollection))

Когда я запустил анализатор для этого запроса с жестко закодированными значениями, он показал сканирование по индексируемому диапазону

DELETE  FROM MyTable
                WHERE   MyTableId IN (10,20,30)

Является ли сбор данных в запросе за кулисами ролью не использования индекса в запросе? Я думаю, что оракул может запутаться из-за количества строк, извлекаемых в коллекции. Я прав? решение?

PS: я думаю о реализации FORALL для удаления строк.

Answer 1

Когда вы выполните это...

WHERE   MyTableId IN (10,20,30)

... оптимизатор достаточно умен, чтобы знать, что он попадет в три ряда. Но если ты сделаешь это...

WHERE   MyTableId IN (SELECT /*+ CARDINALITY(MyTableCollection 3) */MyTableId FROM TABLE(MyTableCollection))

Тогда он не знает, сколько строк в коллекции. Таким образом, предполагается, что имеется 8192 строки, и соответственно выбирает план выполнения.

Но вы можете сказать оптимизатору, сколько строк в коллекции, используя cardinality подсказка:

WHERE   MyTableId IN (SELECT /*+ CARDINALITY(MyTableCollection 100) */
                      MyTableId FROM TABLE(MyTableCollection))

"В этой таблице 70 миллионов строк"

Кстати, стоило бы проверить свежесть статистики вашей таблицы, потому что мы все равно ожидаем, что оптимизатор рассмотрит индекс для получения 8192/70000000 строк.