Почему индекс не используется для этого запроса?

У меня был запрос, где индекс не использовался, когда я думал, что это возможно, поэтому я воспроизвел его из любопытства:

Создать test_table с 1.000.000 строк (10 различных значений в col500 байтов данных в some_data).

CREATE TABLE test_table AS (
  SELECT MOD(ROWNUM,10) col, LPAD('x', 500, 'x') some_data
  FROM dual
  CONNECT BY ROWNUM <= 1000000
);

Создайте индекс и соберите статистику таблицы:

CREATE INDEX test_index ON test_table ( col );

EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE' );

Попробуйте получить разные значения col и COUNT:

EXPLAIN PLAN FOR
  SELECT col, COUNT(*)
  FROM test_table
  GROUP BY col;

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |    30 | 15816   (1)| 00:03:10 
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15816   (1)| 00:03:10 
|   2 |   TABLE ACCESS FULL| TEST_TABLE |   994K|  2914K| 15755   (1)| 00:03:10 
---------------------------------------------------------------------------------

Индекс не используется, если подсказка не изменит этого.

Я думаю, индекс не может быть использован в этом случае, но почему?

Источник

4 ответа

Решение

Я проверил оригинальный материал Питера и воспроизвел его результаты. Затем я применил предложение dcp...

SQL> alter table test_table modify col not null;

Table altered.

SQL> EXEC dbms_stats.gather_table_stats( user, 'TEST_TABLE' , cascade=>true)

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR
  2    SELECT col, COUNT(*)
  3    FROM test_table
  4    GROUP BY col;

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
Plan hash value: 2099921975

------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |    10 |    30 |   574   (9)| 00:00:07 |
|   1 |  HASH GROUP BY        |            |    10 |    30 |   574   (9)| 00:00:07 |
|   2 |   INDEX FAST FULL SCAN| TEST_INDEX |  1000K|  2929K|   532   (2)| 00:00:07 |
------------------------------------------------------------------------------------

9 rows selected.

SQL>

Причина, по которой это имеет значение, заключается в том, что значения NULL не включены в обычный индекс B-TREE, но GROUP BY должен включить NULL в качестве "значения" группировки в вашем запросе. Сообщая оптимизатору, что в NULL нет col он может использовать гораздо более эффективный индекс (у меня было истекшее время почти 3,55 секунды с FTS). Это классический пример того, как метаданные могут влиять на оптимизатор.

Кстати, это, очевидно, база данных 10 г или 11 г, потому что она использует алгоритм HASH GROUP BY вместо более старого алгоритма SORT (GROUP BY).

Источник

ОБНОВЛЕНИЕ: попробуйте сделать столбец col не пустым. Вот почему он не использует индекс. Когда это не нуль, вот план.

SELECT STATEMENT, GOAL = ALL_ROWS           69  10  30
                    HASH GROUP BY           69  10  30
 INDEX FAST FULL SCAN   SANDBOX TEST_INDEX  56  98072   294216

Если оптимизатор определит, что индекс НЕ эффективнее использовать (возможно, из-за переписывания запроса), он не будет. Подсказки оптимизатора - это подсказки, которые сообщают Oracle индекс, который вы хотите использовать. Вы можете думать о них как о предложениях. Но если оптимизатор определит, что индекс лучше не использовать (опять же, например, в результате перезаписи запроса), он не будет использоваться.

Ссылка на эту ссылку: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm "Указание одного из этих советов заставляет оптимизатор выбирать указанный путь доступа, только если путь доступа доступно на основе существования индекса или кластера и синтаксических конструкций оператора SQL. Если подсказка указывает недоступный путь доступа, оптимизатор игнорирует его ".

Поскольку вы выполняете операцию count(*), оптимизатор определил, что более эффективно сканировать всю таблицу и хэш, а не использовать ваш индекс.

Вот еще одна полезная ссылка на подсказки: http://www.dba-oracle.com/t_hint_ignored.htm

Источник

Вы забыли эту действительно важную информацию: COL не нуль

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

SQL> ALTER TABLE test_table MODIFY (col NOT NULL);

Table altered
SQL> EXPLAIN PLAN FOR
  2  SELECT col, COUNT(*) FROM test_table GROUP BY col;

Explained
SQL> SELECT * FROM table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1077170955
--------------------------------------------------------------------------------
| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |    10 |    30 |  1954   (1)| 00:00:2
|   1 |  SORT GROUP BY NOSORT|            |    10 |    30 |  1954   (1)| 00:00:2
|   2 |   INDEX FULL SCAN    | TEST_INDEX |   976K|  2861K|  1954   (1)| 00:00:2
--------------------------------------------------------------------------------
Источник

Растровый индекс будет делать то же самое

План выполнения ------------------------------------------------ ---------- Хэш-значение плана: 2200191467

---------------------------------------------------------------------------------
| Id  | Операция | Имя | Ряды | Байт | Стоимость (% ЦП)| Время | -------------------------------------------------- ------------------------------- | 0 | ВЫБРАТЬ ЗАЯВЛЕНИЕ |            |    10 |    30 | 15983   (2)| 00:03:12 |
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15983   (2)| 00:03:12 |
|   2 | СТОЛ ДОСТУП ПОЛНЫЙ | TEST_TABLE |  1013K|  2968K| 15825   (1)| 00:03:10 |
---------------------------------------------------------------------------------

SQL> создать растровый индекс test_index для test_table(col); Индекс создан.

SQL> EXEC dbms_stats.gather_table_stats( 'MY_SCHEMA', 'TEST_TABLE'); Процедура PL/SQL успешно завершена.

SQL> SELECT col, COUNT(*)
  2    FROM test_table
  3    GROUP BY col
  4  / План выполнения ------------------------------ ---------------------------- Хэш-значение плана: 238193838

---------------------------------------------------------------------------------------
| Id  | Операция | Имя | Ряды | Байт | Стоимость (% ЦП)| Время | -------------------------------------------------- ------------------------------------- | 0 | ВЫБРАТЬ ЗАЯВЛЕНИЕ |            |    10 |    30 |   286   (0)| 00:00:04 |
|   1 | Сортировать по NOSORT    |            |    10 |    30 |   286   (0)| 00:00:04 |
|   2 |   BITMAP КОНВЕРСИОННЫЙ СЧЕТ |            |  1010K|  2961K|   286   (0)| 00:00:04 |
|   3 | БИТМАП ИНДЕКС ПОЛНЫЙ СКАН | TEST_INDEX |       |       |            |          |
---------------------------------------------------------------------------------------
Источник
Другие вопросы по тегам