ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle10G新增NO提示

Oracle10G新增NO提示

原创 Linux操作系统 作者:yangtingkun 时间:2007-09-15 00:00:00 0 删除 编辑

Oracle10g新增的HINT并不是很多,但是可以发现,很多HINT添加了对应的NO_HINT


9i中,表扫描路径相关的执行计划中只有一个NO提示:对应INDEX提示的NO_INDEX。而10g中Oracle增加了NO_INDEX_FFSNO_INDEX_SS提示,分别对应INDEX_FFSINDEX_SS。(INDEX_SS是10g新增的提示)

而在表连接相关的提示中,9i没有任何NO提示。而10g增加了NO_USE_MERGENO_USE_NLNO_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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10365925