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

Question

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

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

Создайте a 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 
---------------------------------------------------------------------------------

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

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

12

sql performance oracle indexing

задан Peter Lang 2 February 2010 в 16:34

4 ответа

Обновление: Попробуйте сделать Col Column не 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.orcle.com/docs/cd/b19306_01/server.102/b14211/hintsref.htm «Указание одного из этих советов приводит к тому, что оптимизатор выбрал указанный путь доступа только в том случае, если путь доступа доступен в зависимости от существования индекса или кластера и на синтаксических конструкциях оператора SQL. Если подсказка указывает недоступный путь доступа, Затем оптимизатор игнорирует его. "

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

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

13

ответ дан 2 December 2019 в 05:27

Вы забыли эту действительно важное значение: COL не является нулевым

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

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
--------------------------------------------------------------------------------

10

ответ дан 2 December 2019 в 05:27

индекс растрового изображения также подойдет

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 |       |       |            |          |
---------------------------------------------------------------------------------------

0

ответ дан 2 December 2019 в 05:27

Другие вопросы по тегам:

sql performance oracle indexing

Похожие вопросы:

score 5 · Accepted Answer

Я прогнал оригинальный материал Питера и воспроизвел его результаты. Затем я применил предложение 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, поскольку в ней используется алгоритм HASH GROUP BY, а не более старый алгоритм SORT (GROUP BY).