Почему оракул игнорирует намекаемый виртуальный индекс?

Question

Я пытаюсь найти лучший способ оптимизации запроса, поэтому я создал виртуальный индекс отдельно от другого созданного индекса, поэтому теперь у меня есть один "реальный" индекс со всеми включенными полями и виртуальный. Когда я запускаю план выполнения, он использует "настоящий", поэтому я избегаю использовать его с такими подсказками:

select /*+INDEX (VIRTUAL_INDEX) */1,2,3,sum(4) ,5 
  from TABLE 
 group by 1,2,3,5 
HAVING SUM(4)>0

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

Благодарю.

Answer 1

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

select /*+INDEX (TABLE1 INDEX_NAME) */ *
  from TABLE1

Во-вторых, попробуйте включить _use_nosegment_indexes Параметр на вот так:

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Чтобы узнать больше о виртуальных индексах, используйте эту статью

Если бы я был тобой, я бы не стал тратить время на создание индекса использования оракула, если оракул не использует его, ему не нужен индекс. Во время синтаксического анализа и анализа оптимизатор оценивает все возможные способы извлечения строк и выбирает путь с наименьшими затратами. Оптимизатор выполняет эту оценку на основе статистики. Поэтому убедитесь, что у вас есть свежая статистика.

Answer 2

Подсказки работают с виртуальными индексами. Вот демо, сначала с реальным индексом:

SQL> CREATE TABLE test_table (
  2     ID NUMBER PRIMARY KEY,
  3     small_data NUMBER(2) NOT NULL,
  4     other_data NUMBER(3) NOT NULL,
  5     big_data CHAR(1000));
Table created
SQL> INSERT INTO test_table
  2     (SELECT ROWNUM, MOD(ROWNUM, 10), MOD(ROWNUM, 99), 'x'
  3        FROM dual CONNECT BY LEVEL <= 1000);
1000 rows inserted
SQL> CREATE INDEX test_real_idx ON test_table (small_data, other_data);
Index created
SQL> BEGIN
  2     dbms_stats.gather_table_stats(
  3        USER, 'TEST_TABLE',
  4        method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
  5        cascade => TRUE);
  6  END;
  7  /
PL/SQL procedure successfully completed
SQL> EXPLAIN PLAN FOR (SELECT small_data, SUM(other_data)
  2                      FROM test_table
  3                     GROUP BY small_data);
Explained
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 590429246
--------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |    10 |    60 |     3  (34)| 00:
|   1 |  HASH GROUP BY        |               |    10 |    60 |     3  (34)| 00:
|   2 |   INDEX FAST FULL SCAN| TEST_REAL_IDX |  1000 |  6000 |     2   (0)| 00:
--------------------------------------------------------------------------------

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

SQL> CREATE INDEX test_virtual_idx
  2     ON test_table (other_data, small_data)
  3     NOSEGMENT;
Index created
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session altered
SQL> EXPLAIN PLAN FOR (
  2     SELECT /*+ INDEX (TEST_TABLE TEST_VIRTUAL_IDX)*/small_data,
  3            SUM(other_data)
  4       FROM test_table
  5      GROUP BY small_data);
Explained
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2641322569
--------------------------------------------------------------------------------
| Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                  |    10 |    60 |    27   (4)| 00:00
|   1 |  HASH GROUP BY   |                  |    10 |    60 |    27   (4)| 00:00
|   2 |   INDEX FULL SCAN| TEST_VIRTUAL_IDX |  1000 |  6000 |    26   (0)| 00:00
--------------------------------------------------------------------------------

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

Из вашего вопроса кажется, что реальный индекс больше не используется, что может быть главной проблемой здесь: возможно, индекс не подходит для ответа на запрос (возможно, из-за обнуляемого столбца?).