1、上周我總結了SQL的使用及一些使用技巧.這周補充總結下項目上遇到(或根據遇到的情況發散學習)的SQL技巧.一、 萬能查詢SQL（統計數據時的萬能，也許其他方面并不萬能）所謂萬能SQL是在不考慮效率的情況下，幾乎都能通用的查詢SQL。目前我發現的萬能SQL包括兩種形式：形式一、相同的記錄需要得到多種該記錄對應的唯一結果，而這些結果不能在同一個SQL查詢。先看下我的公式：Select * from (從主表查詢唯一記錄數) t1,(查詢t1記錄唯一對應的結果1) t2,(查詢t1記錄唯一對應的結果2)t3,Where t1=t2.(+)And t1=t2.(+)再解釋下上面的查詢第一步，查詢得到t

2、1，t1做為結果的基列，是正確結果的一部分。T1的記錄數與最終結果記錄數相同。第二步，t2,t3兩個查詢只是對t1結果列的一個補充。在我的應用中，t2與t3大多都是以與t1相關聯的字段進行group by的一個統計結果。需要注意對應t1的一條記錄，t2 t3 不能有多條結果。第三步，t1與t2，t3進行外連接，查詢出結果。形式二、UNION與UNION ALL兩個或多個來源不同的信息需要匯總到一起，就需要UNION或UNION ALL形式一的思想是：固定行數不變。形式二的思想是：固定列數不變。那么形式一加形式二是否就實現了萬能查詢了呢？至少對我現在這個應用去統計非?？量痰男枨髞碚f萬能查詢幾乎都

3、是萬能的。需要注意的是：萬能查詢效率問題：在一些情況下，大多查詢都走索引，效率還是很高的。在另外一些情況下，查詢也是走了索引，但效率就很低，為什么？因為查詢數據量大，SQL結構不是最優。還有一種情況，查詢不走索引，當然不走索引不一定慢。遇到這種問題時，一般把查詢拆開借用臨時表會有所改善，另外種方法是對SQL的查詢語句本身的優化（結構上與SQL效率上進行優化）二、 AVG與SUM當一張表包括兩種信息（一種是已經統計好的信息，一種是還沒統計的信息），如果需求是統計已經統計好的信息或統計還沒統計的信息，很簡單的一句SQL就搞定了。但如果要同時統計兩種信息時，第一種想到的辦法是自連接：已經統計好的信息

4、 與 還沒統計的信息分開統計后再做連接。（用到萬能連接思想）第二種方法，例子一時想不起來了，先看下SQL結構Select ,avg(t1.r1),sum(t1.r2),from table1 t1group by t1.r1字段已經是統計過的數據了，使用avg(t1.r1) + group by結構計算的avg(t1.r1)的值還是t1.r1，應該這個group里面每個值都一樣，在平均下還是它自己。Sum(t1.r2)根據這個group對r2求和。AVG與SUM這種形式，在一般情況下不會出現，只是在一些不是很規范的表結構的表里會出現，當然不是很規范的表有時確實是為了解決查詢效率的問題。三、 重

5、復記錄由于IT給我的很多數據都是經過統計后的結果給我，我拿到的結果難免會有重復記錄。要把重復記錄視為一條記錄處理起來可不簡單，很容易搞錯。記住下面的方法就不再會為重復記錄頭疼。1 先來個group，得到每條重復記錄的max(rowid)2 Max(rowid)再和源表關聯，重復記錄沒了。看下面例子表zjl_temp_01中，同一記錄中r2,r3,r6重復的為重復記錄。不管這條記錄其他列是否相同，在后面的查詢中我只使用r2,r3,r6重復的記錄中的一條。WITH tt AS (SELECT t02.*FROM (SELECT t.r2, t.r3, t.r6, MAX(t.rowid) row_

6、idFROM zjl_temp_01 tGROUP BY t.r2, t.r3, t.r6) t01,(SELECT DISTINCT * FROM zjl_temp_01) t02WHERE t01.row_id = t02.rowid)刪除重復記錄 delete from t where t.rowid50 or t.a1 55)AND amv.materialscode 9990333449替換成SELECT *FROM all_material_clear_a_year_v amv,CATEGORY ctgWHERE 1 = 1AND amv.category=ctg.category

7、_id(+)AND ctg.category_id IS NULLAND ctg.parent_id(+)55AND amv.materialscode 9990333449NOT IN 也可以替換成 NOT EXISTS對于not in 和 not exists，外連接 的區別：什么時候只能用NOT IN：如果選擇的列可以為空，則不能被替換。只能使用 NOT IN，其他情況下可使用外連接與NOT EXISTS三種方法的性能區別：not in 只有當子查詢中，select 關鍵字后的字段有not null約束或者有這種暗示時用not in,另外如果主查詢中表大，子查詢中的表小但是記錄多，則應當

8、使用not in,并使用anti hash join.如果主查詢表中記錄少，子查詢表中記錄多，并有索引，可以使用not exists，另外not in最好也可以用/*+ HASH_AJ */ 或者外連接+is null，NOT IN 在基于成本的應用中較好使用外連接+is null，一直搞不明白為什么外連接可以替代NOT IN，只是測試過外連接確實可以替換?？聪旅娴睦樱篠ELECT *FROM all_material_clear_a_year_v amv,CATEGORY ctgWHERE 1 = 1AND amv.category=ctg.category_id(+)AND ctg.ca

9、tegory_id IS NULLAND ctg.parent_id(+)55AND amv.materialscode 9990333449如果沒搞過外連接替換NOT IN的話，根本不知道上面SQL到底是干什么，AND ctg.category_id IS NULL為什么不是ctg.category_id(+) IS NULL，這就是和普通的外連接不一樣的地方了。如果用ctg.category_id(+) IS NULL，結果都是amv中的記錄并且一條也沒少。但如果用ctg.category_id IS NULL，結果就少了很多，和以前外連接觀念不一樣了。雖然我也不知道為什么，但是知道可以這

10、樣用，不會在下次碰到這個問題時被別人忽悠了。上面的語句其實結果和下面一樣SELECT *FROM all_material_clear_a_year_v amvWHERE 1 = 1AND amv.category NOT IN(SELECT category_id FROM category ctg WHERE ctg.parent_id 55)AND amv.materialscode 9990333449在NOT IN中使用hints，用/*+ HASH_AJ */SELECT c2FROM t1WHERE c1 NOT IN(SELECT /*+ HASH_AJ */ c1 FROM

11、t2);上面的用法大大提高了NOT IN的效率。為什么使用HINTS，以后再研究吧?；诔杀镜膽弥凶詣邮褂昧?*+ HASH_AJ */五、去掉空格和回車符select replace(translate(b,chr(10)|chr(13), ), ,) from a;六、一個多層次的SELECT語句，盡量把里層的表連接放外層，除非放里層能使里層查詢得到的記錄數減少。七、導數據（SQL SERVER TO ORACLE） 補充上周總結中的導數據1、DTS上次總結提到通過DTS導數據到ORACLE包括ODBC和ORACLE HOME的驅動。而DTS中還提供Oracle Provider for

12、 OLD DB，前兩種方法都是基于OLD DB技術之上的方法，效率比OLD DB低4到5倍。這里提一下用OLD DB導數據的技巧：OLD DB不支持自動轉換數據類型和關鍵字，而ODBC卻能自動轉換數據類型和關鍵字?？梢韵扔肙DBC向導得到創建對應ORACLE表的源碼，再用OLDDB去導數據。2、ORACLE通用網關的效率：CREATE TABLE AS SELECT * FROM TABLEREMOTE 一共25秒而DTS最快的OLD DB方式導同樣數據卻要15分鐘。3、ORACLE TO ORACLE當大量數據需要從一個ORACLE數據庫導到另外個ORACLE數據庫時，可使用EXP和IMP命

13、令進行批量導數據。在有ORACLE DB SERVER或ORACLE DB CLIENT環境的機器的cmd命令行下，直接輸入imp或exp可得到幫助。exp username/passwordservicename前段時間看到一個IMP可能會出現的問題與解決方法：在利用import進行數據庫重建過程中,有些視圖可能會帶來問題,因為結構輸入的順序可能造成視圖的輸入先于它低層次表的輸入,這樣建立視圖就會失敗.要解決這一問題,可采取分兩步走的方法:首先輸入結構,然后輸入數據.命令舉例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,數據文件:expdat

14、a.dmp):imp jfcl/hfjfora1 file=empdata.dmp rows=Nimp jfcl/hfjfora1 file=empdata.dmp full=Y buffer=64000 commit=Y ignore=Y- 第一條命令輸入所有數據庫結構,但無記錄.第二次輸入結構和數據,64000字節提交一次.ignore=Y選項保證第二次輸入既使對象存在的情況下也能成功.八、更新時 容易出錯的地方Update table1 t set t.row1=(select . Where )And exists .后面的exists一定不能不寫，否則會把沒匹配到的記錄更新為NULL

15、九、如果一個語句查找的行數超過總行數的20%,它將不能通過使用索引獲得性能上的提高.這是網上看到的，摘錄了下來，不知道是否正確也不知道為什么。十、基于CBO的優化 學習過程中一些筆記SELECT e1.ename, dallasdept.dnameFROM emp e1,(SELECT deptno, dnameFROM deptWHERE loc = DALLAS) dallasdeptWHERE e1.deptno = dallasdept.deptno;上面的查詢ORACLE的執行順序問題查詢中有VIEW 參數設置 _complex_view_merging8i 先查VIEW，在和其他連

16、接 false9i 把VIEW打開，和其他連接 或把幾個連接連接到一起 true10g 計算前兩種方法哪種效率更高可以用merge() 或no_merge()來指定查詢方式SELECT /*+NO_MERGE(dallasdept)*/ e1.ename, dallasdept.dnameFROM emp e1,(SELECT deptno, dnameFROM deptWHERE loc = DALLAS) dallasdeptWHERE e1.deptno = dallasdept.deptno;This causes view dallasdept not to be merged.When the NO_MERGE hint is used without an argument,