Oracle10g新增的HINT并不是很多,但是可以发现,很多HINT添加了对应的NO_HINT。
在9i中,表扫描路径相关的执行计划中只有一个NO提示:对应INDEX提示的NO_INDEX。而10g中Oracle增加了NO_INDEX_FFS和NO_INDEX_SS提示,分别对应INDEX_FFS和INDEX_SS。(INDEX_SS是10g新增的提示)
而在表连接相关的提示中,9i没有任何NO提示。而10g增加了NO_USE_MERGE、NO_USE_NL和NO_USE_HASH三个连接提示。
简单介绍一下几个NO提示:
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
表已创建。
SQL> DESC T
名称 是否为空? 类型
----------------------------------------------------------------- -------- ------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> ALTER TABLE T MODIFY (OWNER NOT NULL, OBJECT_NAME NOT NULL);
表已更改。
SQL> CREATE INDEX IND_T_OWNER_OBJECT_ID ON T(OWNER, OBJECT_ID);
索引已创建。
SQL> CREATE INDEX IND_T_OBJECT_NAME ON T(OBJECT_NAME);
索引已创建。
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')
PL/SQL 过程已成功完成。
SQL> COL OWNER FORMAT A10
SQL> COL OBJECT_NAME FORMAT A40
SQL> SELECT OWNER, OBJECT_NAME, STATUS FROM T WHERE OBJECT_ID = 12345;
OWNER OBJECT_NAME STATUS
---------- ---------------------------------------- -------
SYS /c4dfe52_DbmsCompilerInterface VALID
执行计划
----------------------------------------------------------
Plan hash value: 612927175
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 41 | 6 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IND_T_OWNER_OBJECT_ID | 1 | | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=12345)
filter("OBJECT_ID"=12345)
SQL> SELECT /*+ NO_INDEX_SS(T IND_T_OWNER_OBJECT_ID) */
2 OWNER, OBJECT_NAME, STATUS FROM T WHERE OBJECT_ID = 12345;
OWNER OBJECT_NAME STATUS
---------- ---------------------------------------- -------
SYS /c4dfe52_DbmsCompilerInterface VALID
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 42 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 41 | 42 (5)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=12345)
上面是使用NO_INDEX_SS提示的例子,下面看看NO_INDEX_FFS的例子:
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
50524
执行计划
----------------------------------------------------------
Plan hash value: 3813267093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_OWNER_OBJECT_ID | 50524 | 11 (10)| 00:00:01 |
---------------------------------------------------------------------------------------
SQL> SELECT /*+ NO_INDEX_FFS(T IND_T_OWNER_OBJECT_ID) */
2 COUNT(*) FROM T;
COUNT(*)
----------
50524
执行计划
----------------------------------------------------------
Plan hash value: 3565015618
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 (7)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IND_T_OBJECT_NAME | 50524 | 16 (7)| 00:00:01 |
-----------------------------------------------------------------------------------
SQL> SELECT /*+ NO_INDEX_FFS(T) */ COUNT(*) FROM T;
COUNT(*)
----------
50524
执行计划
----------------------------------------------------------
Plan hash value: 3537499178
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| IND_T_OWNER_OBJECT_ID | 50524 | 23 (0)| 00:00:01 |
----------------------------------------------------------------------------------
SQL> SELECT /*+ NO_INDEX(T) */ COUNT(*) FROM T;
COUNT(*)
----------
50524
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50524 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------
使用NO_INDEX_FFS如果指定到一个索引,那么Oracle只是不考虑该索引的FAST FULL SCAN,如果将NO_INDEX_FFS指定到表,那么Oracle将不会考虑该表上所有索引的FAST FULL SCAN,但是仍然可能选择其他索引扫描的执行路径。
如果通过NO_INDEX指定到表,则Oracle将不会考虑该表上的任何索引。
下面看看表连接的情况:
SQL> CREATE TABLE T1 AS SELECT * FROM DBA_TABLES;
表已创建。
SQL> SELECT COUNT(*) FROM T, T1
2 WHERE T.OWNER = T1.OWNER
3 AND T.OBJECT_NAME = T1.TABLE_NAME;
COUNT(*)
----------
1757
执行计划
----------------------------------------------------------
Plan hash value: 949044725
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 47 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | HASH JOIN | | 1602 | 101K| 47 (5)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1602 | 54468 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T | 50524 | 1529K| 41 (3)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."OWNER"="T1"."OWNER" AND
"T"."OBJECT_NAME"="T1"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ NO_USE_HASH(T, T1) */ COUNT(*) FROM T, T1
2 WHERE T.OWNER = T1.OWNER
3 AND T.OBJECT_NAME = T1.TABLE_NAME;
COUNT(*)
----------
1757
执行计划
----------------------------------------------------------
Plan hash value: 712353386
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | | 274 (3)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 65 | | | |
| 2 | MERGE JOIN | | 1602 | 101K| | 274 (3)| 00:00:03 |
| 3 | SORT JOIN | | 1602 | 54468 | | 6 (17)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T1 | 1602 | 54468 | | 5 (0)| 00:00:01 |
|* 5 | SORT JOIN | | 50524 | 1529K| 3984K| 268 (3)| 00:00:03 |
| 6 | TABLE ACCESS FULL| T | 50524 | 1529K| | 41 (3)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T"."OWNER"="T1"."OWNER")
filter("T"."OBJECT_NAME"="T1"."TABLE_NAME" AND
"T"."OWNER"="T1"."OWNER")
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ NO_USE_HASH(T, T1) NO_USE_MERGE(T, T1) */
2 COUNT(*) FROM T, T1
3 WHERE T.OWNER = T1.OWNER
4 AND T.OBJECT_NAME = T1.TABLE_NAME;
COUNT(*)
----------
1757
执行计划
----------------------------------------------------------
Plan hash value: 4034493185
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 486 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 31 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1602 | 101K| 486 (0)| 00:00:06 |
| 4 | TABLE ACCESS FULL | T1 | 1602 | 54468 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T_OBJECT_NAME | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."OWNER"="T1"."OWNER")
5 - access("T"."OBJECT_NAME"="T1"."TABLE_NAME")
Note
-----
- dynamic sampling used for this statement
SQL> CREATE INDEX IND_T1_OWNER_TABLE_NAME ON T1(OWNER, TABLE_NAME);
索引已创建。
SQL> SELECT COUNT(*) FROM T, T1
2 WHERE T.OWNER = T1.OWNER
3 AND T.OBJECT_NAME = T1.TABLE_NAME
4 AND T.OBJECT_NAME = 'DUAL';
COUNT(*)
----------
1
执行计划
----------------------------------------------------------
Plan hash value: 1700038726
----------------------------------------------------------------------------------------------------
| Id | Operation | Name |Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
| 2 | NESTED LOOPS | | 1 | 65 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T | 2 | 62 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IND_T_OBJECT_NAME | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_T1_OWNER_TABLE_NAME| 1 | 34 | 1 (0)| 00:00:01
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T"."OBJECT_NAME"='DUAL')
5 - access("T"."OWNER"="T1"."OWNER" AND "T1"."TABLE_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement
SQL> SELECT /*+ NO_USE_NL(T T1) */ COUNT(*) FROM T, T1
2 WHERE T.OWNER = T1.OWNER
3 AND T.OBJECT_NAME = T1.TABLE_NAME
4 AND T.OBJECT_NAME = 'DUAL';
COUNT(*)
----------
1
执行计划
----------------------------------------------------------
Plan hash value: 2453463917
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 65 | 5 (40)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1| 65 | | |
| 2 | MERGE JOIN | | 1| 65 | 5 (40)| 00:00:01 |
| 3 | SORT JOIN | | 1| 34 | 3 (34)| 00:00:01 |
|* 4 | INDEX FAST FULL SCAN |IND_T1_OWNER_TABLE_NAME| 1| 34 | 2 (0)| 00:0
|* 5 | SORT JOIN | | 2| 62 | 2 (50)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T | 2| 62 | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_T_OBJECT_NAME | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."TABLE_NAME"='DUAL')
5 - access("T"."OWNER"="T1"."OWNER")
filter("T"."OBJECT_NAME"="T1"."TABLE_NAME" AND "T"."OWNER"="T1"."OWNER")
7 - access("T"."OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement
需要注意,Oracle一共只有三种连接方法,如果在HINT中同时指定三种,那么Oracle会认为提示无效,根据COST来进行连接方式的选择。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69425/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-69425/