Comments in hint

 

It seems that CBO can work with the line comment inside sql hints.

Let’s see. I create a table with 3 indexes:

--drop table ttt purge;
create table ttt
(
  a number not null,
  b number not null,
  c number not null
);

insert into ttt
  select 1,1,1
    from dual connect by level <= 10000;
commit;

create index iiia on ttt(a);
create index iiib on ttt(b);
create index iiic on ttt(c);

And then I’m able to switch access paths by commenting:

explain plan for
select /*+
         --full(ttt)
         --index(ttt(c))
         index(ttt(a))
         --index(ttt(b))
       */
       count(*)
from ttt;
select * from table(dbms_xplan.display);

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIA | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

explain plan for
select /*+
         --full(ttt)
         index(ttt(c))
         --index(ttt(a))
         --index(ttt(b))
       */
       count(*)
from ttt;
select * from table(dbms_xplan.display);
-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIC | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

It also works fine with the line hint itself:

explain plan for
select --+ index(ttt(b)) -- full(ttt)
count(*)
from ttt;
select * from table(dbms_xplan.display);

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    26   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   INDEX FULL SCAN| IIIB | 10000 |    26   (0)| 00:00:01 |
-----------------------------------------------------------------

Here is one little detail, in PL/SQL you can accidentally comment on the end of the hint as well as the part of the hint and get syntax error:

SQL> declare
2    cnt number;
3  begin
4    select /*+ --full(ttt)
5               index(ttt(c))
6               --index(ttt(a)) index(ttt(b))
7               --index(ttt(b)) */
8         count(*)
9    into cnt
10    from ttt;
11  end;
12  /
declare
*
ERROR at line 1:
ORA-00921: unexpected end of SQL command
ORA-06512: at line 4

It seems to be a bug, as the hint is a comment itself, so it can contain anything that is allowed for the comments:

SQL> begin
2    /*
3    --*/
4    null;
5  end;
6  /

PL/SQL procedure successfully completed.

Summary. It is still undocumented behaviour, but you may use it while testing hinting strategy.