Добавленный вами индекс бесполезен. Зачем?

Недавно в офисе:

Боб: Я посмотрел на этот медленный вопрос, о котором ты мне говорил вчера, Алиса. Я добавил индексы, которые вы хотели. Теперь все должно быть хорошо

Алиса: Спасибо, Боб. Я быстро проверю… Нет, Боб, все еще медленно, похоже, не сработало

Боб: Ты прав, Алиса! Похоже, что Oracle не получает индекс для вашего запроса, даже если я добавлю /*+INDEX(...)*/подсказку. Я не знаю, что пошло не так !?

И так, история продолжается. Алиса расстроена, потому что ее функция не работает вовремя, Боб расстроен, потому что он думает, что Oracle не работает правильно.

Правдивая история!

Боб забыл про Oracle и NULL

Бедный Боб забыл (или не знал), что Oracle не помещает значения NULL в «обычные» индексы . Подумайте об этом таким образом:

CREATE TABLE person (
  id            NUMBER(38)   NOT NULL PRIMARY KEY,
  first_name    VARCHAR2(50) NOT NULL,
  last_name     VARCHAR2(50) NOT NULL,
  date_of_birth DATE             NULL
);
 
CREATE INDEX i_person_dob ON person(date_of_birth);

Теперь Боб считает, что его индекс решает все проблемы, потому что он проверил, работает ли индекс, используя следующий запрос:

SELECT *
FROM   person
WHERE  date_of_birth > DATE '1980-01-01';

(конечно, вы вообще не должны SELECT *)

И план выполнения выглядел хорошо:

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSON       |
|*  2 |   INDEX RANGE SCAN          | I_PERSON_DOB |
----------------------------------------------------

Это потому, что предикат Боба не полагается на то, NULLчтобы быть частью I_PERSON_DOBиндекса. К сожалению, запрос Алисы выглядел больше так (упрощенная версия):

SELECT 1
FROM   dual
WHERE  DATE '1980-01-01' NOT IN (
  SELECT date_of_birth FROM person
);

Таким образом, по сути, запрос Алисы проверил, была ли у кого-либо дата рождения на данную дату. Ее план выполнения выглядел так:

-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |
|*  3 |   TABLE ACCESS FULL| PERSON |
-------------------------------------

Как видите, ее запрос сделал TABLE ACCESS FULLоперацию, минуя индекс. Зачем? Это просто:

• Oracle не помещает NULLзначения в индексы
• NOT IN (a, b, NULL, c, d) всегда дает NULL

Даже если наше DATE '1980-01-01'значение находится в индексе или нет, нам все равно придется проверить всю таблицу, чтобы увидеть, NULLсодержится ли в date_of_birthстолбце одно значение . Потому что, если былоNULL значение, предикат в запросе Алисы никогда не дают или , но .NOT INTRUEFALSENULL

SELECT 1
FROM   dual
WHERE  NOT EXISTS (
  SELECT 1
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'
);

>------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |
|*  3 |   INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------

ALTER TABLE person
MODIFY date_of_birth DATE NOT NULL;

SELECT
  i.table_name,
  i.index_name,
  LISTAGG(
    LPAD(i.column_position,  2) || ': ' ||
    RPAD(i.column_name    , 30) || ' '  ||
    DECODE(t.nullable, 'Y', '(NULL)', '(NOT NULL)'),
    ', '
  ) WITHIN GROUP (ORDER BY i.column_position)
    AS "NULLABLE columns in indexes"
FROM user_ind_columns i
JOIN user_tab_cols t
ON (t.table_name, t.column_name) =
  ((i.table_name, i.column_name))
WHERE EXISTS (
  SELECT 1
  FROM user_tab_cols t
  WHERE (t.table_name, t.column_name, t.nullable) =
       ((i.table_name, i.column_name, 'Y'       ))
)
GROUP BY i.table_name, i.index_name
ORDER BY i.index_name ASC;

TABLE_NAME | INDEX_NAME   | NULLABLE columns in indexes
-----------+--------------+----------------------------
PERSON     | I_PERSON_DOB | 1: DATE_OF_BIRTH (NULL)