Oracle's OPT_ESTIMATE hint: Usage Guide

Purpose

Usage

• query block

  optional and in the format "@QB_NAME". For example, in the lack of any QB_NAME hints, you could say: OPT_ESTIMATE(@SEL$1 TABLE....)

• operation_type

  QUERY_BLOCK unknown TABLE refers to table Example: [code]select /*+OPT_ESTIMATE(TABLE DD ROWS=10)*/ count(*) from dual DD [/code] INDEX_FILTER adjusts index filtering - number of rows produced by the index lookup, does not affect the cost to scan the index Example INDEX_FILTER - changes output rows ONLY: [code]select /*+ OPT_ESTIMATE(INDEX_FILTER C CKK$ID ROWS=1234) */* from ckk c where id > 9000000 [/code] INDEX_SCAN / INDEX_SKIP_SCAN For index range or skip scans - how many rows will be scanned in the index - affects the COST of the index access and the number of rows outputted Example INDEX_SCAN - changes COST and ROWS: [code]select /*+ OPT_ESTIMATE(INDEX_SCAN C CKK$ID ROWS=1234) */* from ckk c where id > 9000000 [/code] JOIN the join result of 2 tables - oracle joins 2 tables at a time. Note that you can adjust join cardinalities between 2 tables in order to make that join not to be used. Example: [code]select /*+ OPT_ESTIMATE(JOIN (C S) ROWS=123456) */* from ckk c join small s on (s.id = c.id) [/code]

• identifier

  qry_block is always optional TABLES: tab_alias@qry_block INDEX: tab_alias@qry_block index_name JOINS: JOIN(tab1_alias@qry_block tab2_alias@qry_block)

• adjustment

  ROWS=number SCALE_ROWS=number - multiplier to be used. For example 0.1 is 10 times less rows than originally estimated MIN=minimum number of rows from step - very useful to prevent 'insane' plans MAX=maximum number of rows from step