Why oracle ignore hinted virtual index?

Question

i´m trying to find out the best way to optimize a query, so i have created a virtual index apart from another created index, so now i have one "real" index with all fields included, and the virtual one. When i run the execution plan, it uses the "real" one, so i avoid using it with hints like this:

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

But now it doesn´t use any index, and the execution plan shows a full scan instead of pointing the new virtual index... I don´t really know too much about hints or virtual indexes, so maybe/probably i´m doing something wrong, but i can´t see what, could anyone help me?

Thanks.

Answer 1

First of all, to make the optimizer use an index you have to write the hint like this:

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

Secondly, try to turn the _use_nosegment_indexes parameter on like this:

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

To find out more about virtual indexes use this article

If I were you I wouldn't waste my time on making oracle use index, if oracle doesn't use it, it doesn't need the index. During the parsing and analyzing the optimizer evaluate all possible ways to extract rows and it chooses the way with the lowest cost. Optimizer performs this evaluation based on the statistics. So make sure you have fresh statistics.

Answer 2

Hints work with virtual indexes. Here's a demo, first with a real index:

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

Then we create a virtual one, we'll reverse the columns because no two indexes can have the exact same columns in the same order:

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

As you can see the virtual index has taken precedence over the real index thanks to the hint.

From your question it seems that the real index is not used anymore, which might be the main problem here: maybe the index is inadequate to answer the query (because of a nullable column perhaps?).