Full Hint

95 阅读 0 评论 63 点赞

我是靠谱客的博主幸福天空，最近开发中收集的这篇文章主要介绍Full Hint，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Full Hint可以提示优化器对指定的表进行全表扫描，需要注意的是Full一次只能对一张表起作用。

下面是实验过程

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Personal Oracle Database 10g Release 10.2.0.1.0 - Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

这是我的oracle环境

SQL> desc test1;
名称是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc test2
名称是否为空? 类型
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> create index pk_test1 on test1(empno);

索引已创建。

SQL> create index pk_test2 on test2(empno);

索引已创建。

SQL> select test1.empno,test2.empno from test1,test2
2 where test1.empno=test2.empno;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 1756776100

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 1 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 1 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_TEST1 | 14 | 182 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| PK_TEST2 | 1 | 13 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TEST1"."EMPNO"="TEST2"."EMPNO")

Note
-----
- dynamic sampling used for this statement
初始情况下，优化器选择对两张表进行索引扫描

SQL> select /*+full(test1)*/ test1.empno,test2.empno from test1,test2
2 where test1.empno=test2.empno;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 3442032678

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 14 | 182 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PK_TEST2 | 1 | 13 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TEST1"."EMPNO"="TEST2"."EMPNO")

Note
-----
- dynamic sampling used for this statement
使用full干预后，对test1进行全表扫描

SQL> select /*+full(test1 test2)*/ test1.empno,test2.empno from test1,test2
2 where test1.empno=test2.empno;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 1756776100

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 1 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 364 | 1 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | PK_TEST1 | 14 | 182 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| PK_TEST2 | 1 | 13 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("TEST1"."EMPNO"="TEST2"."EMPNO")

Note
-----
- dynamic sampling used for this statement
使用full(test1 test2)进行干预后，发现没有全表扫描，说明full一次只能对一张表起作用

SQL> select /*+full(test1) full(test2)*/ test1.empno,test2.empno from test1,test2
2 where test1.empno=test2.empno;

已选择14行。

执行计划
----------------------------------------------------------
Plan hash value: 497311279

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 364 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST1 | 14 | 182 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST2 | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("TEST1"."EMPNO"="TEST2"."EMPNO")

Note
-----
- dynamic sampling used for this statement