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

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

Создайте test_table с 1.000.000 строками (10 различных значений в col, 500 байтов данных в 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 
---------------------------------------------------------------------------------

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

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

Ответ 1

Я запустил оригинальный материал Питера и воспроизвел его результаты. Затем я применил предложение 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 в качестве "значения" группировки в вашем запросе. Сообщая оптимизатору, что в col нет NULL, он может использовать гораздо более эффективный индекс (я получал истекшее время почти 3,55 секунды с FTS). Это классический пример того, как метаданные могут влиять на оптимизатор.

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

Ответ 2

UPDATE: Попробуйте сделать столбец столбца NOT NULL. Именно по этой причине он не использует индекс. Когда это не пустое, вот план.

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

Ответ 3

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

Если столбец 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
--------------------------------------------------------------------------------

Ответ 4

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

Execution Plan
----------------------------------------------------------
Plan hash value: 2200191467

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |    30 | 15983   (2)| 00:03:12 |
|   1 |  HASH GROUP BY     |            |    10 |    30 | 15983   (2)| 00:03:12 |
|   2 |   TABLE ACCESS FULL| TEST_TABLE |  1013K|  2968K| 15825   (1)| 00:03:10 |
---------------------------------------------------------------------------------

SQL> create bitmap index test_index on test_table(col);

Index created.

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

PL/SQL procedure successfully completed.

SQL> SELECT col, COUNT(*)
  2    FROM test_table
  3    GROUP BY col
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 238193838

---------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |    10 |    30 |   286   (0)| 00:00:04 |
|   1 |  SORT GROUP BY NOSORT    |            |    10 |    30 |   286   (0)| 00:00:04 |
|   2 |   BITMAP CONVERSION COUNT|            |  1010K|  2961K|   286   (0)| 00:00:04 |
|   3 |    BITMAP INDEX FULL SCAN| TEST_INDEX |       |       |            |          |
---------------------------------------------------------------------------------------