----
1 - access("OBJECT_NAME"='EMPLOYEES')
filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
1 physical reads
0 redo size
538 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
当全表扫描扫描了942个块，联合索引只扫描了17个数据块。可以看到联合索引的第一个字段的值重复率很高时，即使谓词中没有联合索引的第一个字段，依然会使用index_ss方式，效率远远高于全表扫描效率。但当 第一个字段的值重复率很低时，使用 index_ss的效率要低于 全表扫描，读者可以自行实验
和表的关联相关的hint
/*+ leading(table_1,table_2) */
在多表关联查询中，指定哪个表作为驱动表，即告诉优化器首先要访问哪个表上的数据。
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */
让Oracle根据from后面表的顺序来选择驱动表，oracle建议使用leading，他更为灵活
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */
在多表关联查询中，指定使用nest loops方式进行多表关联。
select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_hash(table_1,table_2) */
在多表关联查询中，指定使用hash join方式进行多表关联。
select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
?
在多表关联查询中，指定使用hash join方式进行多表关联，并指定表t为驱动表。
select /*+ use_hash(t,t1) leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_merge(table_1,table_2) */
在多表关联查询中，指定使用merge join方式进行多表关联。
select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_nl(table_1,table_2) */
在多表关联查询中，指定不使用nest loops方式进行多表关联。
select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_hash(table_1,table_2) */
在多表关联查询中，指定不使用hash join方式进行多表关联。
select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_merge(table_1,table_2) */
在多表关联查询中，指定不使用merge join方式进行多表关联。
select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
其他常用的hint
/*+ parallel(table_name n) */
在sql中指定执行的并行度，这个值将会覆盖自身的并行度
select /*+ parallel(t 4) */ count(*) from t;
/*+ no_parallel(table_name) */
在sql中指定执行的不使用并行
select /*+ no_parallel(t) */ count(*) from t;
/*+ append */以直接加载的方式将数据加载入库
insert into t /*+ append */ select * from t;
/*+ dynamic_sampling(table_name n) */
设置sql执行时动态采用的级别，这个级别为0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234
/*+ cache(table_name) */
进行全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性
select /*+ full(employees) cache(employees) */ last_name from employees
附录hint表格
| Hints for Optimization Approaches and Goals |
| ALL_ROWS |
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). |
| FIRST_ROWS |
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows. |
| CHOOSE |
The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement |
| RULE |
The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g. |
| Hints for Access Paths |
| FULL |
The FULL hint explicitly chooses a full t |