Comments in hint
January 31, 2012 Leave a comment
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.