常用提示
CHOOSE
作用:强制使用CHOOSE为优化器规则;
例子:
SQL>select /**//*+choose*/ f1_num from t_huang; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: CHOOSE 1 0 TABLE ACCESS (FULL) OF 'T_HUANG'
RULE
作用:强制使用RULE为优化器规则;
例子:
SQL>select /**//*+rule*/ f1_num from t_huang; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 TABLE ACCESS (FULL) OF 'T_HUANG'
FIRST_ROWS[(n)]
作用:强制使用FIRST_ROW为优化器规则,以最快速度检索第一行,以优化查询。(n)没有则默认为1。这个提示在系统用户通过Oracle Form查询单条或少量数据时特别有用。
注意:在使用update、delete、group by、intersect、minus、union时,first_row提示无效。
例子:
SQL>select /**//*+first_rows(10000)*/ * from t_huang where f1_num < 10000; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=1 Card=4 B ytes=252) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4 Bytes=252) 2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card =1)
ALL_ROWS
作用:强制使用ALL_ROW为优化器规则,以最快速度检索所有行,以优化查询。当处理大批量数据时,这个提示特别有用。
例子:
SQL>select /**//*+all_rows*/ * from t_huang where f1_num < 100000; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=1 Card=4 Byt es=252) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4 Bytes=252) 2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card =1)
FULL
作用:对所指定的表进行全表扫描。在查询表的大部分数据时使用该索引,将不会扫描索引,而直接进行全表扫描。
例子:
SQL>select /**//*+full(t_wei)*/ * from t_wei where f1_num2 < 9000; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=252) 1 0 TABLE ACCESS (FULL) OF 'T_WEI' (Cost=2 Card=4 Bytes=252) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 663 consistent gets 0 physical reads 0 redo size 541708 bytes sent via SQL*Net to client 7982 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
对比:
SQL>select * from t_wei where f1_num2 < 9000; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI' 2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1270 consistent gets 0 physical reads 0 redo size 541708 bytes sent via SQL*Net to client 7982 bytes received via SQL*Net from client 668 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10000 rows processed
返回的数据为表的大部分数据,在没有使用强制全表扫描的情况下会先扫描索引,比使用全表扫描的方式多出了近一半的consistent gets
INDEX[(table index1, index2…)]
作用:强制使用一个或多个索引。在某些情况下(特别是在使用基于成本的优化规则下),Oracle优化器不能正确选择所有,可以通过使用这个提示强制指定使用某一个或多个索引。
例子:
SQL>select /**//*+index(t_huang PK_T_HUANG)*/ * from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000; Execution Plan ---------------------------------------------------------- 0 SELECT ST