Oracle10G新增NO提示

yangtingkun發表於2007-09-15

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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章