oraclehints的那点事

引言：hints翻译成中文就是提示，暗示的意思，它在数据库中作用就是更改SQL语句的执行方式，你可以使用hints强制sql按照你所设置的方式执行sql，一般用来做性能

引言：hints翻译成中文就是提示，暗示的意思，它在数据库中作用就是更改SQL语句的执行方式，你可以使用hints强制sql按照你所设置的方式执行sql，一般用来做性能诊断和调优，不建议在开发中使用。

1.写一条SQL，使它通过全表扫描方式的效率优于索引访问，分别给出各自的执行计划。

LEO1@LEO1> create table leo1 as select * from dba_objects; 创建leo1表

Table created.

LEO1@LEO1> create index idx_leo1 on leo1(object_id); 在这个object_id列上创建索引

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true); 分析表和索引

PL/SQL procedure successfully completed.

LEO1@LEO1> select count(*) from leo1; 表上有71958行记录

COUNT(*)

---------------

71958

LEO1@LEO1> select /*+ full(leo1) */ * from leo1 where object_id>100;

71859 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 71862 | 6807K| 287 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| LEO1 | 71862 | 6807K| 287 (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID">100)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

5762 consistent gets 5762次一致性读

0 physical reads

0 redo size

3715777 bytes sent via SQL*Net to client

53214 bytes received via SQL*Net from client

4792 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

71859 rows processed

LEO1@LEO1> select /*+ index(leo1 idx_leo1) */ * from leo1 where object_id>100;

71859 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1434365503

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 71862 | 6807K| 1232 (1)| 00:00:15 |

| 1 | TABLE ACCESS BY INDEX ROWID| LEO1 | 71862 | 6807K| 1232 (1)| 00:00:15 |

|* 2 | INDEX RANGE SCAN | IDX_LEO1 | 71862 | | 160 (0)| 00:00:02 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">100)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

10735 consistent gets 10735次一致性读

0 physical reads

0 redo size

8241805 bytes sent via SQL*Net to client

53214 bytes received via SQL*Net from client

4792 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

71859 rows processed