Oracle10G新增NO提示
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Metasploit新增技巧提示功能
- Oracle10g新增DBMS_MONITOR包Oracle
- 在Oracle10g 新增ASM磁碟組OracleASM
- Oracle10g新增CONVERT語法Oracle
- Eclipse新增更多提示Eclipse
- Oracle10g新增DBMS_MONITOR包（二）Oracle
- Oracle10g新增DBMS_MONITOR包（一）Oracle
- Oracle10g新增BLOCK CHANGE TRACKING功能OracleBloC
- ibatis 新增DTD 自動提示BAT
- 在Oracle10g RAC下新增ASM磁碟組OracleASM
- Oracle10g新增DBMS_FILE_TRANSFER包（二）Oracle
- Oracle10g新增DBMS_FILE_TRANSFER包（一）Oracle
- Java 在PDF中新增工具提示|ToolTipJava
- Java 在PDF中新增工具提示ToolTipJava
- 新增節點oracle10g rac(rhel4)_clusterwareOracle
- 新增節點oracle10g rac(rhel4)_databaseOracleDatabase
- idea Maven新增依賴沒有提示IdeaMaven
- javascript input文字框新增提示文字效果JavaScript
- Oracle10g新增的檢視dba_tablespace_usage_metricsOracle
- 新增節點oracle10g rac(rhel4)_instanceOracle
- 【RAC】Oracle10g rac新增刪除節點命令參考Oracle
- Oracle10g RAC環境OCR的新增、刪除、備份Oracle
- Oracle10g RAC環境VoteDisk的新增、刪除、備份Oracle
- Oracle11gr2新增APPEND_VALUES提示OracleAPP
- Myeclipse 2014 javascript 新增 jquery 程式碼提示EclipseJavaScriptjQuery
- 新增 CNAME 記錄提示和 A 記錄衝突如何解決？
- sublime3中如何新增javascript程式碼自動提示JavaScript
- Oracle11gr2新增提示CHANGE_DUPKEY_ERROR_INDEXOracleErrorIndex
- jboss連線rhel5 oracle10g 10.2.0.4提示TNS-12514Oracle
- 為你的 JavaScript 專案新增智慧提示和型別檢查JavaScript型別
- Dynamics CRM 向檢視列新增自定義圖示和提示資訊
- Oracle11gr2新增提示IGNORE_ROW_ON_DUPKEY_INDEXOracleIndex
- 程式碼片段新增智慧提示，打造一款人見人愛的ORM框架ORM框架
- testWeb例子中，還沒有建立資料庫呢，怎麼回提示新增成功？Web資料庫
- oracle10g recyclebinOracle
- oracle10g SGAOracle
- oracle10g PGAOracle
- Oracle10g心得Oracle