Tuning SQL note1

Optimizer_mode : RULE | FRIST_ROWS | ALL_ROWS | CHOOSE

COST : run by FTS Which don't need , Iespeacial under 3 tables jion

RULE : choose error index

FULL TABLE SCAN 临界条件:

1. sorted table -- > 40% rows need be seleced using FTS

2. unsorted table -- >7%rows need be selected using FTS

About driver table under RULE : 返回行最小的表should be put the frist position in  where clause ，but COST auto choose driver table .

COST : need all tables and indexes be analyzed .   dbms_utility.analyze_schema('schema_name','','','FOR TABLE FOR ALL INDEXES')

CBO SQL TIPS
/*+ rule*/ 
/*+ full, table =xxx parallel = nn */
/*+ table =xxx index=xxx */
/*+ frist_rows */
/*+ use_hash */

CUSOR_SHARING =FORCE

OUTLINE : hard sql execute plan

TOOLS : XPLAN , TIMING ON

oracleHu 2005-10-28