In 18C release optimizer parameter OPTIMIZER_IGNORE_HINTS(was underscore parameter) is now documented.
If set to TRUE the optimizer ignores embedded hints. Can be changed on system or session level.
Let’s create test table to see how it works. Important note the table has got primary key constraint so it means index.
CREATE TABLE test_tbl ( id1 PRIMARY KEY, id2 ) AS SELECT rownum,rownum FROM dual CONNECT BY LEVEL < 1000; SELECT count(*) FROM test_tbl; COUNT(*) ---------- 999
Embedded hint /*+ index(test_tbl) */ forces to use index
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=FALSE; EXPLAIN PLAN FOR SELECT /*+ index(test_tbl) */ sum(id2) FROM test_tbl; SELECT * FROM dbms_xplan.display(format=>'BASIC'); Plan hash value: 1092693181 ---------------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TBL | | 3 | INDEX FULL SCAN | SYS_C0012 | ----------------------------------------------------------
and here embedded hints are ignored
ALTER SESSION SET OPTIMIZER_IGNORE_HINTS=TRUE; EXPLAIN PLAN FOR SELECT /*+ index(test_tbl) */ sum(id2) FROM test_tbl; SELECT * FROM dbms_xplan.display(format=>'BASIC'); Plan hash value: 765159746 --------------------------------------- | Id | Operation | Name | --------------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| TEST_TBL | ---------------------------------------
It’s very useful hint to see if embedded hints can be removed in legacy code :). I really love it.
Have a fun 🙂
Tomasz