Oracle 暫存資料表之暫存資料表的應用問題

網上有人給出了最佳的最佳化思路是：

1.先將大表中滿足條件的記錄抽出來產生一張暫存資料表.

2.再將這較小的暫存資料表與另一張較小的表進行關聯查詢.

先不論思路是否值得商榷，這把暫存資料表當成中轉站的做法還是很值得肯定

暫存資料表本質上就是一種cache的表現形式，Oracle的暫存資料表都是事先建好的

一旦用了暫存資料表，存放的就是和本會話相關的資料

沒有人會傻乎乎地用暫存資料表來儲存本應該共用的資料

with子查詢實際上也是用了暫存資料表，Oracle會替你建立一張暫存資料表

因此暫存資料表的開銷WITH子查詢也會有。只要把AUTOTRACE開啟你就會看到REDO的開銷

關於暫存資料表的使用至少會帶來兩個問題：

1）主查詢的執行計畫問題

2）額外的寫redo的問題

如果，

暫存資料表作為複雜查詢條件的中間結果用於主查詢，因為暫存資料表裡往往只是個別欄位的少量資料，1）的問題比較突出；

如果，

暫存資料表作為最終展現前的結果歸集，可能暫存資料表會有比較多欄位的較多資料，2）的問題比較突出

㈠ 主查詢的執行計畫問題

9i暫存資料表由於動態採樣level 1,還得用hint,10g比較好用

比較複雜的預存程序（比如資料幫浦）可能用到暫存資料表，比實體表優勢就是redo少，自動清除

對於暫存資料表的缺陷--採樣問題，執行計畫的問題其實主要是暫存資料表的cardinality的問題

對於暫存資料表方案，建議動態採樣。9IR2以後的版本使用DYNAMIC_SAMPLING 參數或hint能基本避免

如寫上 HINT強制它採樣 /*+dynamic_sampling(t 0) */

cardinality hint分段提示是個比較好的最佳實務

例如：

暫存資料表裡的資料量有大起大落的情形，Oracle只會在硬解析的時候做一次取樣

當暫存資料表資料量變化之後，原來的執行計畫可能已經不是最優的

碰到這種問題建議使用動態SQL

暫存資料表的資料量在插入結束之後可以通過SQL%ROWCOUNT得知

然後在動態SQL裡面拼入cardinality提示，這個提示沒有必要精確，要不然你就會有無數的硬解析了

建議給它設定的坎是5000, 即1-5000當作5000處理，5001-10000當作10000,

如此類推，CARDINALITY = CEIL(SQL%ROWCOUNT/5000)*5000，

你也可以通過測試調整出一個合理的值

1 2 下一頁