Почему индекс не используется для этого запроса?
У меня был запрос, где индекс не использовался, когда я думал, что это возможно, поэтому я воспроизвел его из любопытства:
Создайте 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 | | | | |
---------------------------------------------------------------------------------------
