Re: How hints affect the CBO?

Comments embedded
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:afeqck$q57$1_at_babylon.agtel.net...
> That is, no matter how inneffective a query with FULL hint is, CBO will
> always do full scan even if index access yelds better plan, because it is
> explicitly instructed to do so and this is valid access path.

In this particular case then this is correct (at least on 9.2 but I believe it applies to 817 etc as well. )

The documentation reference is

http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920 /a96533/hintsref.htm#5078
and an example

SQL> create table nl_objects as select * from all_objects;

Table created.

SQL> insert into nl_objects select * from nl_objects;

29077 rows created.

SQL> / 58154 rows created.

SQL> / 116308 rows created.

SQL> / 232616 rows created.

SQL> commit;

SQL> create index i_obj on nl_objects(object_id);

Index created

SQL> select object_name from nl_objects
  2 where object_id=1;

no rows selected

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NL_OBJECTS'    2 1 INDEX (RANGE SCAN) OF 'I_OBJ' (NON-UNIQUE) Statistics

          0  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> select /*+ FULL(nl_objects) */ object_name from nl_objects   2 where object_id=10;

no rows selected

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=614 Card=16 Bytes=43
          2)

   1    0   TABLE ACCESS (FULL) OF 'NL_OBJECTS' (Cost=614 Card=16 Byte
          s=432)

Statistics

          0  recursive calls
          0  db block gets
       6363  consistent gets
       4141  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        368  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> NB 6363 gets instead of 3.

> So, how does CBO work with respect to hints? Does it treat them as
> direct orders if they specify valid access path and does not consider
> alternatives, or does it just tweak costs so that the hinted path looks
> better than the others, but still can choose a different path? Anyone
> seen/can provide an example of CBO choosing path different from
> one suggested by a valid hint that does not exhibit a bug in CBO?
> I tried to come up with one tweaking optimizer_index_cost_adj to
> make indexes very expensive to CBO, but to no avail. Or valid hints
> are mandatory? Anyone can direct me to the correct place in
> documentation where this is explicitly stated (or explicitly stated
> otherwise)? Or maybe some Oracle insider can take a peek at the CBO
> code and tell for sure how hints affect it (don't think this will happen
> though)? ;)

I couldn't and wouldn't necessarily extrapolate from the FULL hint to all hints.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************