Почему проверка на null замедляет этот запрос?

Я получил эту таблицу, содержащую 7000 записей

desc ARADMIN.V_PKGXMLCODE

Name Null Type --------------------- -------- ------------- REQUEST_ID NOT NULL VARCHAR2(15) AVAILABILITY VARCHAR2(69) XML_CODE CLOB PACKAGENAME_UNIQUE VARCHAR2(50) CATALOG NUMBER(15) CHILD VARCHAR2(255) CLASSIFICATION_SYSTEM NUMBER(15) E_MAIL VARCHAR2(69) 

Запрос

 SELECT COUNT(*) FROM ARADMIN.V_PKGXMLCODE WHERE (CATALOG <> 0 AND CATALOG <> 2) AND (NOT (CHILD IS NULL)); 

занимает менее одной секунды.

Запрос

 SELECT COUNT(*) FROM ARADMIN.V_PKGXMLCODE WHERE (CATALOG IS NULL OR (CATALOG <> 0 AND CATALOG <> 2)) AND (NOT (CHILD IS NULL)); 

занимает 23 секунды.

Объясните план, однако утверждает, что он должен идти очень быстро …

введите описание изображения здесь

Что я могу сделать?

Единственный способ, которым я могу думать, чтобы получить такую ​​разницу в скорости выполнения, будет: (a) иметь индекс в field4 и (b) иметь много пустых блоков данных; возможно, с высокой отметки воды, установленной очень высоко при повторных нагрузках прямого пути.

Первый запрос будет по-прежнему использовать индекс и выполнять так, как ожидалось. Но поскольку нулевые значения не индексируются, индекс не может быть использован для проверки or field4 is null условием, поэтому он вернется к полному сканированию таблицы.

Это само по себе не должно быть проблемой здесь, поскольку полное сканирование таблицы из 7000 строк не должно занять много времени. Но поскольку это занимает много времени, происходит что-то еще. Полное сканирование таблицы должно проверять каждый блок данных, выделенный для таблицы, чтобы увидеть, содержат ли они какие-либо строки, и время, которое он принимает, предполагает, что существует намного больше блоков, чем вам нужно иметь 7000 строк, даже с встроенным хранилищем CLOB.

Самый простой способ получить много пустых блоков данных – это иметь много данных, а затем удалить большинство из них. Но я считаю, что вы сказали в недавно удаленном комментарии к более раннему вопросу о том, что производительность была в порядке и ухудшилась. Это может произойти, если вы вставляете вставки прямого пути , особенно если вы обновляете данные, удалив их, а затем вставляя новые данные в режим прямого пути. Вы можете сделать это со вставками, которые имеют /*+ append */ hint; или параллельно; или через SQL * Loader. Каждый раз, когда вы делали это, знак высокой воды двигался, поскольку старые пустые блоки не могли быть повторно использованы; и каждый раз производительность запроса, который проверяет нулевые значения, немного ухудшится. После много итераций, которые действительно начнут складываться.

Вы можете проверить словарь данных, чтобы узнать, сколько места выделено вашей таблице ( user_segments и т. Д.), И сравнить это с размером данных, которые, по вашему мнению, у вас есть. Вы можете сбросить HWM, восстановив таблицу, например, выполнив:

 alter table mytable move; 

(желательно в окне обслуживания!)

В качестве демонстрации я запускал цикл для вставки прямого пути и удалял 7000 строк более ста раз, а затем выполнял оба ваших запроса. Первое заняло 0,06 секунды (большая часть из которых – служебные данные SQL Devleoper); второе заняло 1.260. (Я также управлял Гордоном, который получил аналогичное время, поскольку он все еще должен делать FTS). С большим количеством итераций разница станет еще более заметной, но у меня закончилось свободное пространство … Затем я сделал alter table move и повторил второй запрос, который затем занял 0,05 секунды.

Это интересно. Я ожидаю, что запрос будет иметь такую ​​же производительность, потому что у Oracle хороший оптимизатор и его не следует путать с помощью NULL .

Как эта версия имеет лучшую производительность?

 select x1.cnt + x2.cnt + x3.cnt from (select count(*) as cnt from MYTABLE where field4 = 1 and child is not null ) x1 cross join (select count(*) as cnt from MYTABLE where field4 = 4 and child is not null ) x2 cross join (select count(*) as cnt from MYTABLE where field4 is null and child is not null ) x3; 

Эта версия должна иметь возможность использовать индекс MYTABLE(field4, child) .