Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

1. General Overview:

Oracle Hints let you make decisions usually made by the optimizer. Force the optimizer to follow your efficient execution plan. You might know information about your data that the optimizer does not know especially if your database statistics are out-of-time.
Remember, That you can use different Oracle packages to update database statistics :
- Analyze Statement
- DBMS_UTILITY
- DBMS_STATS
…

The definition of hints is not the purpose of this note, I am quite sure that you can find easier and better explanation at oracle documetation or just googling with “oracle hints”. The goal of this note is to show, why Oracle sometimes does not care about your hints and why sometimes you can not “enforce” the optimizer to use your hints.

2. Demonstrate the use of index hints:

The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes:
In the following example, the use of hints doe not work, lets follow the expamle step by step ;

CREATE TABLE TEST_index (ID NUMBER PRIMARY KEY, SUPPORT_ID NUMBER, DESCRIPTION VARCHAR2(200));
CREATE UNIQUE INDEX INDX_SUPPORT_ID ON TEST_index(SUPPORT_ID);

BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO TEST_index (ID, support_id, DESCRIPTION) VALUES (i, i+1, ‘description_’||i);
END LOOP;

FOR i IN 10000000..10000010 LOOP
INSERT INTO TEST_index (ID, support_id, DESCRIPTION) VALUES (i, NULL, ‘description_’||i);
END LOOP;
COMMIT;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘Error : ‘||SQLERRM);
ROLLBACK;
END;

SQL> Explain plan for SELECT /*+ INDEX(t INDX_SUPPORT_ID)*/ t.* FROM TEST_index t;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2713238154
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 118K| 14M| 125 (1)| 00:00:02 |
| 1 | TABLE ACCESS FULL| TEST_INDEX | 118K| 14M| 125 (1)| 00:00:02 |
——————————————————————————–
Note
—–
- dynamic sampling used for this statement

12 rows selected

SQL>

why Oracle does NOT USE THE INDEX HINT, and it uses TABLE ACCESS FULL instead.

Support_id is an UNIQUE INDEXED cloumn, ID is a PRIMARY KEY.
IF we look at support_id DATA we could find many “NULL”,the optimizer cannot use this unique index to return the result set as there may be some rows in the table which do not appear in the index; The problem is that the hint in this example could lead to Oracle producing the wrong results, so that s why it is USING a FULL TABLE scan.
Let s demonstrate that Oracle did not used the hint for NULL reason.

DELETE FROM TEST_index WHERE support_id IS NULL;
ALTER TABLE test_index MODIFY support_id NOT NULL
ALTER INDEX INDX_SUPPORT_ID REBUILD ONLINE

SQL> Explain plan for SELECT /*+ INDEX(t INDX_SUPPORT_ID)*/ t.* FROM TEST_index t;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————–
Plan hash value: 2480420295
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%C
——————————————————————————–
| 0 | SELECT STATEMENT | | 118K| 14M| 662
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_INDEX | 118K| 14M| 662
| 2 | INDEX FULL SCAN | INDX_SUPPORT_ID | 118K| | 210
——————————————————————————–
Note
—–
- dynamic sampling used for this statement

13 rows selected

SQL>

3. Conclusion :
WHen the hint could lead to Oracle producing the wrong results, Oracle does not use it.