Статьи

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

Декабрь 6, 2018

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

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

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

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

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

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

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

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

1
2
3
4
5
6
7
8
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);

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

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

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

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

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

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

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

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

1
2
3
4
5
6
7
8
-------------------------------------
| Id  | Operation          | Name   |
-------------------------------------
|   0 | SELECT STATEMENT   |        |
|*  1 |  FILTER            |        |
|   2 |   FAST DUAL        |        |
|*  3 |   TABLE ACCESS FULL| PERSON |
-------------------------------------

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

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

Алиса может решить эту проблему с NOT EXISTS

Алиса может легко решить ее самостоятельно, заменив предикат NOT IN на NOT EXISTS предикатом, который не страдает от своеобразной трехзначной логической логики SQL .

1
2
3
4
5
6
7
SELECT 1
FROM   dual
WHERE  NOT EXISTS (
  SELECT 1
  FROM   person
  WHERE  date_of_birth = DATE '1980-01-01'
);

Этот новый запрос теперь снова дает оптимальный план:

1
2
3
4
5
6
7
8
------------------------------------------
| Id  | Operation         | Name         |
------------------------------------------
|   0 | SELECT STATEMENT  |              |
|*  1 |  FILTER           |              |
|   2 |   FAST DUAL       |              |
|*  3 |   INDEX RANGE SCAN| I_PERSON_DOB |
------------------------------------------

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

Боб должен просто установить для столбца NOT NULL

Лучшее решение, однако, это просто установить для столбца NOT NULL :

1
2
ALTER TABLE person
MODIFY date_of_birth DATE NOT NULL;

С этим ограничением запрос NOT IN в точности эквивалентен запросу NOT EXISTS , и Боб и Алиса снова могут быть друзьями.

Вывод: как найти «плохие» столбцы?

Это просто. В следующем полезном запросе перечислены все индексы, в которых есть хотя бы один обнуляемый столбец.

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
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;

При запуске против схемы Боба и Алисы приведенный выше запрос дает:

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

Используйте этот запрос в своей собственной схеме сейчас и просмотрите результаты, тщательно оценивая, действительно ли вам нужно сохранять этот столбец обнуляемым. В 50% случаев вы этого не делаете. Добавив ограничение NOT NULL , вы сможете значительно ускорить ваше приложение!

Ссылка: Добавленный вами индекс бесполезен. Зачем? от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ .
37
Share: