Oracle資料庫查詢高效分頁Oracle資料庫查詢高效分頁

Oracle資料庫查詢高效分頁 

        由於網頁渲染速度的影響，在C/S程式中那種一個Grid包含幾千、上萬行的資料基本上在網頁是無法展現的，因此一般採用分頁的形式顯示（也可能採用Visual Srolling方式載入的，公司專屬應用程式系統的不是很常見），ASP.NET 的資料控制項一般帶有分頁功能，3.5以後還提供了單獨的分頁控制項，也有用過AspNetPager這個第三方的組件。

       分頁的控制項實在很方便，以前的處理方式就是資料都拿出來，然後由控制項進行處理，一般資料量不大的時候應該說感覺不出來優劣，但由於每次從資料庫取的時候都是取所有的資料，肯定會增加資料庫的壓力，傳輸的資料庫多了對網路頻寬也會產生壓力的。很有可能查出來1萬條資料，最後顯示只用到啟用50條，翻頁的時候又重新去查1萬條資料，顯示之後的的又50條。

       以下的分頁SQL比較常見的，在SQL Server也有對應的使用TOP關鍵字的版本，記得剛學Oralce的時候就想著怎麼不能rownum between minValue and maxValue的用法。與最初的疑惑的原理一樣，rownum是在查詢過程中產生的，因此以下的SQL其實是查出來5300行，然後扔掉了前面5000行，返回後面的300行。當然這種已經進了一大步的，由資料庫返回的資料變少的，只是當查詢的頁數比較大的時候，查詢還是存在一定的浪費。

?

12345

select *   from (select a.*, rownum as rnum           from (select * from yz_bingrenyz) a          where rownum <=5300)  where rnum >= 5000

       Linq提供了Skip和Take的API可以用於分頁，由於使用的是Entity Framework，在好奇的驅使下用EFProfiler查看產生的SQL，才知道這樣以下分頁更好。 主要就是使用了row_numer()over()這樣的分析函數，可以直接找到那第5000行開始的地方，然後在取出30行就行了。

?

123456

select *   from (select *           from (select t.*, row_number() OVER(ORDER BY null) AS "row_number"                  from yz_bingrenyz t) p          where p."row_number" > 5000) q  where rownum <= 300

比較分析：

        本機測試前者耗時1.3s,後者僅0.25s，從以下的執行計畫也能看出差異來。

實際應用

       如果每次查詢都要寫這種SQL那肯定比較麻煩，可以採用預存程序進行封裝，但由於要動態執行SQL，效率肯定又要打折扣，所以在ASP.NET中用C#封裝函數比較好，對於沒有使用Entity Framework的而用ADO.NET的，傳入表名 、主鍵名、頁數、要取的行數作為參數，用DBCommand進行執行返回結果即可。

        由於網頁渲染速度的影響，在C/S程式中那種一個Grid包含幾千、上萬行的資料基本上在網頁是無法展現的，因此一般採用分頁的形式顯示（也可能採用Visual Srolling方式載入的，公司專屬應用程式系統的不是很常見），ASP.NET 的資料控制項一般帶有分頁功能，3.5以後還提供了單獨的分頁控制項，也有用過AspNetPager這個第三方的組件。

       分頁的控制項實在很方便，以前的處理方式就是資料都拿出來，然後由控制項進行處理，一般資料量不大的時候應該說感覺不出來優劣，但由於每次從資料庫取的時候都是取所有的資料，肯定會增加資料庫的壓力，傳輸的資料庫多了對網路頻寬也會產生壓力的。很有可能查出來1萬條資料，最後顯示只用到啟用50條，翻頁的時候又重新去查1萬條資料，顯示之後的的又50條。

       以下的分頁SQL比較常見的，在SQL Server也有對應的使用TOP關鍵字的版本，記得剛學Oralce的時候就想著怎麼不能rownum between minValue and maxValue的用法。與最初的疑惑的原理一樣，rownum是在查詢過程中產生的，因此以下的SQL其實是查出來5300行，然後扔掉了前面5000行，返回後面的300行。當然這種已經進了一大步的，由資料庫返回的資料變少的，只是當查詢的頁數比較大的時候，查詢還是存在一定的浪費。

?

12345

select *   from (select a.*, rownum as rnum           from (select * from yz_bingrenyz) a          where rownum <=5300)  where rnum >= 5000

       Linq提供了Skip和Take的API可以用於分頁，由於使用的是Entity Framework，在好奇的驅使下用EFProfiler查看產生的SQL，才知道這樣以下分頁更好。 主要就是使用了row_numer()over()這樣的分析函數，可以直接找到那第5000行開始的地方，然後在取出30行就行了。

?

123456

select *   from (select *           from (select t.*, row_number() OVER(ORDER BY null) AS "row_number"                  from yz_bingrenyz t) p          where p."row_number" > 5000) q  where rownum <= 300

比較分析：

        本機測試前者耗時1.3s,後者僅0.25s，從以下的執行計畫也能看出差異來。

實際應用

       如果每次查詢都要寫這種SQL那肯定比較麻煩，可以採用預存程序進行封裝，但由於要動態執行SQL，效率肯定又要打折扣，所以在ASP.NET中用C#封裝函數比較好，對於沒有使用Entity Framework的而用ADO.NET的，傳入表名 、主鍵名、頁數、要取的行數作為參數，用DBCommand進行執行返回結果即可。