what if I ran a query:
select /*+ Hello World!!!!! */ * from emp where emp_id = :b1;
and it used the index... does that imply/infer that the "hint" Hello World!!!!! was the reason the hint was used?
if you just run:
select * from emp where emp_id = :b1;
without a hint, I would expect the index to be used as well (emp_id would be a primary key, it'll get 1 row, typically to get 1 row from a table by a primary key - the optimizer would tend to use the primary key index!)
But in this case, you might be surprised. We can use a 10053 optimizer dump to see what the optimizer is actually dealing with. For example:
ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte%ORA11GR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from emp where empno = 1234;
Execution Plan
----------------------------------------------------------
Plan hash value: 4024650034
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=1234)
ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable n number;
ops$tkyte%ORA11GR2> exec :n := 1234;
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> @10053
ops$tkyte%ORA11GR2> alter session set events '10053 trace name context forever, level 1';
Session altered.
ops$tkyte%ORA11GR2> select /*+ index(emp,emp_pk) */ * from emp where empno = :n;
no rows selected
so here we have an EMP table, primary key on EMPNO and "where empno = some_number" will tend to use the index regardless. We run the hinted query using what "appears" to be an invalid hint (has a comma, doesn't need a comma, but is actually allowed to use a comma) but if we review the trace file - we'll see:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ INDEX ("EMP" "EMP_PK") */ "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "OPS$TKYTE"."EMP" "EMP" WHERE "EMP"."EMPNO"=:B1
kkoqbc: optimizing query block SEL$1 (#0)
and further more we can see:
atom_hint=(@=0x4b6fdc err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("EMP" "EMP_PK") )
indicating the hint was used and was not in error...
the comma is allowed but not required:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF51102 <quote>
When tablespec is followed by indexspec in the specification of a hint, a comma separating the table name and index name is permitted but not required. Commas are also permitted, but not required, to separate multiple occurrences of indexspec.
</quote>