愛無所不在

在SQL語句優化過程中，我們經常會用到hint,現總結一下在SQL優化過程中常見Oracle HINT的用法： 1. /*+ALL_ROWS*/ 表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化. 例如: SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. /*+FIRST_ROWS*/ 表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化. 例如: SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 3. /*+CHOOSE*/ 表明如果數據字典中有訪問表的統計信息,將基於開銷的優化方法,並獲得最佳的吞吐量; 表明如果數據字典中沒有訪問表的統計信息,將基於規則開銷的優化方法; 例如: SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 4. /*+RULE*/ 表明對語句塊選擇基於規則的優化方法. 例如: SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 5. /*+FULL(TABLE)*/ 表明對表選擇全局掃瞄的方法. 例如: SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT'; 6. /*+ROWID(TABLE)*/ 提示明確表明對指定表根據ROWID進行訪問. 例如: SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT'; 7. /*+CLUSTER(TABLE)*/ 提示明確表明對指定表選擇簇掃瞄的訪問方法,它只對簇對象有效. 例如: SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 8. /*+INDEX(TABLE INDEX_NAME)*/ 表明對表選擇索引的掃瞄方法. 例如: SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M'; 9. /*+INDEX_ASC(TABLE INDEX_NAME)*/ 表明對表選擇索引升序的掃瞄方法. 例如: SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT'; 10. /*+INDEX_COMBINE*/ 為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式. 例如: SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATEV.AVG_SAL; 20. /*+NO_MERGE(TABLE)*/ 對於有可合併的視圖不再合併. 例如: SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL; 21. /*+ORDERED*/ 根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接. 例如: SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1; 22. /*+USE_NL(TABLE)*/ 將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表. 例如: SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 23. /*+USE_MERGE(TABLE)*/ 將指定的表與其他行源通過合併排序連接方式連接起來. 例如: SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 24. /*+USE_HASH(TABLE)*/ 將指定的表與其他行源通過哈希連接方式連接起來. 例如: SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 25. /*+DRIVING_SITE(TABLE)*/ 強制與ORACLE所選擇的位置不同的表進行查詢執行. 例如: SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO; 26. /*+LEADING(TABLE)*/ 將指定的表作為連接次序中的首表. 27. /*+CACHE(TABLE)*/ 當進行全表掃瞄時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端 例如: SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS; 28. /*+NOCACHE(TABLE)*/ 當進行全表掃瞄時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端 例如: SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS; 29. /*+APPEND*/ 直接插入到表的最後,可以提高速度. insert /*+append*/ into test1 select * from test4 ; 30. /*+NOAPPEND*/ 通過在插入語句生存期內停止並行模式來啟動常規插入. insert /*+noappend*/ into test1 select * from test4 ; 下午 04:13 | 新增意見 | 永久連結 | 發佈至您的部落格 | Oracle PL/SQL 在Oracle中導出、導入dmp數據庫文件 Oracle 數據導入導出imp/exp就相當於oracle數據還原與備份。exp 命令可以把數據從遠程數據庫服務器導出到本地的dmp文件，imp命令可以把dmp文件從本地導入到遠處的數據庫服務器中。 利用這個功能可以構建兩個相同的數據庫，一個用來測試，一個用來正式使用。 執行環境：可以在SQLPLUS.EXE或者DOS（命令行）中執行，DOS中可以執行時由於 在oracle 8i 中 安裝目錄\ora81\BIN被設置為全局路徑，該目錄下有EXP.EXE與IMP.EXE文件被用來執行導入導出。oracle用java編寫， SQLPLUS.EXE、EXP.EXE、IMP.EXE這兩個文件有可能是被包裝後的類文件。SQLPLUS.EXE調用EXP.EXE、 IMP.EXE所包裹的類，完成導入導出功能。 下面介紹的是導入導出的實例。 數據導出： 1 將數據庫TEST完全導出,用戶名system 密碼manager 導出到D:\daochu.dmp中 exp system/manager@TEST file=d:\daochu.dmp full=y 2 將數據庫中system用戶與sys用戶的表導出 exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys) 3 將數據庫中的表inner_notify、notify_staff_relat導出 exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat) 4 將數據庫中的表table1中的字段filed1以"00"打頭的數據導出 exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\" 上面是常用的導出，對於壓縮，既用winzip把dmp文件可以很好的壓縮。也可以在上面命令後面 加上 compress=y 來實現。 數據的導入 1 將D:\daochu.dmp 中的數據導入 TEST數據庫中。 imp system/manager@TEST file=d:\daochu.dmp imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y 上面可能有點問題，因為有的表已經存在，然後它就報錯，對該表就不進行導入。 在後面加上 ignore=y 就可以了。 2 將d:\daochu.dmp中的表table1 導入 imp system/manager@TEST file=d:\daochu.dmp tables=(table1) 基本上上面的導入導出夠用了。不少情況要先是將表徹底刪除，然後導入。 注意： 操作者要有足夠的權限，權限不夠它會提示。 數據庫時可以連上的。可以用tnsping TEST 來獲得數據庫TEST能否連上。 附錄一： 給用戶增加導入數據權限的操作 第一,啟動sql*puls 第二，以system/manager登陸 第三，create user 用戶名 IDENTIFIED BY 密碼 （如果已經創建過用戶， 這步可以省略） 第四，GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA,CONNECT,RESOURCE,CREATE SESSION TO 用戶名字 第五, 運行-cmd-進入dmp文件所在的目錄, imp userid=system/manager full=y file=*.dmp 或者 imp userid=system/manager full=y file=filename.dmp 執行示例: F:\Work\Oracle_Data\backup>imp userid=test/test full=y file=inner_notify.dmp 屏幕顯示 Import: Release 8.1.7.0.0 - Production on 星期四 2月 16 16:50:05 2006 (c) Copyright 2000 Oracle Corporation. All rights reserved. 連接到: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production With the Partitioning option JServer Release 8.1.7.0.0 - Production 經由常規路徑導出由EXPORT:V08.01.07創建的文件 已經完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的導入 導出服務器使用UTF8 NCHAR 字符集 (可能的ncharset轉換) . 正在將AICHANNEL的對象導入到 AICHANNEL . . 正在導入表 "INNER_NOTIFY" 4行被導入 準備啟用約束條件... 成功終止導入，但出現警告。 附錄二： Oracle 不允許直接改變表的擁有者, 利用Export/Import可以達到這一目的. 先建立import9.par, 然後，使用時命令如下：imp parfile=/filepath/import9.par 例 import9.par 內容如下： FROMUSER=TGPMS TOUSER=TGPMS2 （註：把表的擁有者由FROMUSER改為TOUSER，FROMUSER和TOUSER的用戶可以不同） ROWS=Y INDEXES=Y GRANTS=Y CONSTRAINTS=Y BUFFER=409600 file==/backup/ctgpc_20030623.dmp log==/backup/import_20030623.log