Oracle分頁(yè)查詢
一.引用文章
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A --不排序
WHERE ROWNUM <= 40
)
WHERE RN >= 21;
SELECT /*+ FIRST_ROWS */ *
FROM (SELECT a.*, row_number() over(ORDER BY 1) rn--假排序，速度同上
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
SELECT *
FROM (SELECT a.*, row_number() over(ORDER BY c1) rn --真實(shí)排序，無(wú)法比較速度
FROM t1 a)
WHERE rn BETWEEN 21 AND 40;
Oracle的分頁(yè)查詢語(yǔ)句基本上可以按照本文給出的格式來(lái)進(jìn)行套用。
分頁(yè)查詢格式：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
其中最內(nèi)層的查詢SELECT * FROM TABLE_NAME表示不進(jìn)行翻頁(yè)的原始查詢語(yǔ)句。ROWNUM <= 40和RN >= 21控制分頁(yè)查詢的每頁(yè)的范圍。
上面給出的這個(gè)分頁(yè)查詢語(yǔ)句，在大多數(shù)情況擁有較高的效率。分頁(yè)的目的就是控制輸出結(jié)果集大小，將結(jié)果盡快的返回。在上面的分頁(yè)查詢語(yǔ)句中，這種考慮主要體現(xiàn)在WHERE ROWNUM <= 40這句上。
選擇第21到40條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過(guò)ROWNUM <= 40來(lái)控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 40語(yǔ)句，在查詢的最外層控制分頁(yè)的最小值和最大值。這是，查詢語(yǔ)句如下：
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
對(duì)比這兩種寫法，絕大多數(shù)的情況下，第一個(gè)查詢的效率比第二個(gè)高得多。
這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。對(duì)于第一個(gè)查詢語(yǔ)句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過(guò)了ROWNUM限制條件，就終止查詢將結(jié)果返回了。
而第二個(gè)查詢語(yǔ)句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無(wú)法將第三層的查詢條件推到最內(nèi)層（即使推到最內(nèi)層也沒(méi)有意義，因?yàn)樽顑?nèi)層查詢不知道RN代表什么）。因此，對(duì)于第二個(gè)查詢語(yǔ)句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過(guò)濾在最外層完成，顯然這個(gè)效率要比第一個(gè)查詢低得多。
上面分析的查詢不僅僅是針對(duì)單表的簡(jiǎn)單查詢，對(duì)于最內(nèi)層查詢是復(fù)雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。
這里就不對(duì)包含排序的查詢進(jìn)行說(shuō)明了，下一篇文章會(huì)通過(guò)例子來(lái)詳細(xì)說(shuō)明。下面簡(jiǎn)單討論一下多表聯(lián)合的情況。對(duì)于最常見(jiàn)的等值表連接查詢，CBO一般可能會(huì)采用兩種連接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會(huì)考慮）。在這里，由于使用了分頁(yè)，因此指定了一個(gè)返回的最大記錄數(shù)，NESTED LOOP在返回記錄數(shù)超過(guò)最大值時(shí)可以馬上停止并將結(jié)果返回給中間層，而HASH JOIN必須處理完所有結(jié)果集（MERGE JOIN也是）。那么在大部分的情況下，對(duì)于分頁(yè)查詢選擇NESTED LOOP作為查詢的連接方法具有較高的效率（分頁(yè)查詢的時(shí)候絕大部分的情況是查詢前幾頁(yè)的數(shù)據(jù)，越靠后面的頁(yè)數(shù)訪問(wèn)幾率越小）。
因此，如果不介意在系統(tǒng)中使用HINT的話，可以將分頁(yè)的查詢語(yǔ)句改寫為：
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
二.我的存儲(chǔ)過(guò)程
create or replace package CJ_PageData is
TYPE type_cur IS REF CURSOR; --建立游標(biāo)
PROCEDURE CJ_GetDataByPage
(
tblName in varchar2,
strGetFields in varchar2,
fldName in varchar2,
PageSize in number,
PageIndex in number,
doCount in number,
OrderType in number,
strWhere in varchar2,
DataCount out number,
DataSet out type_cur
);
end CJ_PageData;
CREATE OR REPLACE PACKAGE BODY CJ_PageData
AS
PROCEDURE CJ_GetDataByPage
(
tblName in varchar2, --表名
strGetFields in varchar2, --返回字段
fldName in varchar2, --排序字段名
PageSize in number, --分頁(yè)大小
PageIndex in number, --頁(yè)碼
doCount in number, --是否統(tǒng)計(jì)(0否,1是)
OrderType in number, --排序類型(0降,1升)
strWhere in varchar2, --篩選條件
DataCount out number, --返回記錄總數(shù)
DataSet out type_cur --返回游標(biāo)(指針)
)
as
strSQL varchar2(5000) ; -- 臨時(shí)記錄Sql語(yǔ)句
strOrder varchar2(400) ; -- 臨時(shí)記錄排序語(yǔ)句
begin
if doCount <> 0 then
begin
if strWhere <> ' ' then
strSQL := 'select count(*) as total from ' || tblName || ' where '||strWhere;
else
strSQL := 'select count(*) as total from ' || tblName;
end if;
execute immediate strSQL into DataCount;--取出記錄總數(shù)放入返回變量DataCount
end;
else
begin
strSQL := 'select ' ||strGetFields||' from (select T2.*,rownum rn from (select T1.* from ' || tblName||' T1 ';
--得到排序類型語(yǔ)句
if OrderType <> 0 then
begin
strOrder := ' order by ' || fldName ||' asc';
end;
else
begin
strOrder := ' order by ' || fldName ||' desc';
end;
end if;
begin
--組裝SQl語(yǔ)句
if strWhere <> ' ' then
strSQL := strSQL || ' where ' || strWhere || ') T2 where rownum <=' || TO_CHAR(PageIndex*PageSize) || ')'
|| ' where rn >' || TO_CHAR((PageIndex-1)*PageSize) || strOrder;
else
strSQL := strSQL || ' ) T2 where rownum <=' || TO_CHAR(PageIndex*PageSize) || ')'
|| ' where rn >' || TO_CHAR((PageIndex-1)*PageSize) || strOrder;
end if;
end;
end;
end if;
open DataSet for strSQL;
end;
end CJ_PageData;
三,.Net中調(diào)用存儲(chǔ)過(guò)程
1.方法
/// <summary>
/// 獲得記錄總數(shù)和所有記錄{連接字符串,存儲(chǔ)過(guò)程,表,返回字段,排序字段,分頁(yè)大小,頁(yè)碼,統(tǒng)計(jì)(1是,0不是),排序(1升序,0降序),條件}
/// </summary>
/// <param name="strConn"></param>
/// <param name="strSPName">存儲(chǔ)過(guò)程名字</param>
/// <param name="strTableName">表名</param>
/// <param name="strGetFields">返回字段字符串</param>
/// <param name="strFieldName">排序字段</param>
/// <param name="iPageIndex">頁(yè)碼</param>
/// <param name="iPageSize">分頁(yè)大小</param>
/// <param name="iDoCount">是否統(tǒng)計(jì)記錄總數(shù)(1統(tǒng)計(jì),0不統(tǒng)計(jì))</param>
/// <param name="iOrderType">排序類型(0升序,1降序)</param>
/// <param name="strWhere">篩選條件(不帶where)</param>
protected void BuildData(string strConn,string strSPName,string strTableName,string strGetFields,string strFieldName,int iPageSize,int iPageIndex,int iDoCount,int iOrderType,string strWhere)
{
System.Data.OracleClient.OracleConnection conn = new OracleConnection();
System.Data.OracleClient.OracleCommand cmd = new OracleCommand();
System.Data.OracleClient.OracleDataReader dr;
conn.ConnectionString = strConn;
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
cmd.CommandText = strSPName;
cmd.Parameters.Add("tblName",OracleType.VarChar);//表名
cmd.Parameters.Add("strGetFields",OracleType.VarChar);//返回字段名
cmd.Parameters.Add("fldName",OracleType.VarChar);//排序字段名
cmd.Parameters.Add("PageSize",OracleType.Number);//分頁(yè)大小
cmd.Parameters.Add("PageIndex",OracleType.Number);//頁(yè)碼
cmd.Parameters.Add("doCount",OracleType.Number);//是否統(tǒng)計(jì)總記錄數(shù)
cmd.Parameters.Add("OrderType",OracleType.Number);//排序類型
cmd.Parameters.Add("strWhere",OracleType.VarChar);//篩選條件
cmd.Parameters.Add("DataCount",OracleType.Number);//返回得記錄總數(shù)
cmd.Parameters.Add("DataSet",OracleType.Cursor);//返回得記錄集
cmd.Parameters["tblName"].Value = strTableName;
cmd.Parameters["strGetFields"].Value = strGetFields;
cmd.Parameters["fldName"].Value = strFieldName;
cmd.Parameters["PageSize"].Value = iPageSize;
cmd.Parameters["PageIndex"].Value = ( iOrderType == 0 ) ? (pager.PageCount+1-iPageIndex) : iPageIndex;//因?yàn)镺racle中不支持子查詢排序,所以在這里如果需要倒序,則將頁(yè)碼按反序取數(shù)據(jù),"以假亂真".嘿嘿!
cmd.Parameters["doCount"].Value = iDoCount;
cmd.Parameters["OrderType"].Value = iOrderType;
cmd.Parameters["strWhere"].Value = strWhere;
cmd.Parameters["DataCount"].Direction = ParameterDirection.Output;
cmd.Parameters["DataSet"].Direction = ParameterDirection.Output;
cmd.Parameters["tblName"].Size = 100;
cmd.Parameters["strGetFields"].Size = 1000;
cmd.Parameters["fldName"].Size = 100;
cmd.Parameters["strWhere"].Size = 1000;
//try
{
switch (iDoCount)
{
case 0:
{
dr = cmd.ExecuteReader();
dgData.DataSource = dr;
dgData.DataBind();//綁定數(shù)據(jù)源
dr.Close();
break;
}
case 1:
{
cmd.ExecuteOracleScalar();
pager.RecordCount = int.Parse(cmd.Parameters["DataCount"].Value.ToString());
break;
}
}
}
//catch
{
}
conn.Close();
}
2.調(diào)用
A.頁(yè)面加載時(shí)候取得總記錄數(shù),以綁定分頁(yè)控件
private void Page_Load(object src,EventArgs e)
{
if(!Page.IsPostBack)
{
string conn = "password=cqcj;user id=cqcj;data source=cqc1";
this.BuildData(conn,"CJ_PageData.CJ_GetDataByPage","CJ_Log","","",0,0,1,0,"UserID = 'xxr'");
BindData();//這里給分頁(yè)控件.如DataGrid綁定數(shù)據(jù)源
}
}
B.BindData()
protected void BindData()
{
string conn = "password=cqcj;user id=cqcj;data source=cqc1";
this.BuildData(conn,"CJ_PageData.CJ_GetDataByPage","CJ_Log","*","LoginTime",pager.PageSize,pager.CurrentPageIndex,0,1,"UserID = 'xxr'");
pager.CustomInfoText="記錄總數(shù)：<font color=\"blue\"><b>"+pager.RecordCount.ToString()+"</b></font>";
pager.CustomInfoText+=" 總頁(yè)數(shù)：<font color=\"blue\"><b>"+pager.PageCount.ToString()+"</b></font>";
pager.CustomInfoText+=" 當(dāng)前頁(yè)：<font color=\"red\"><b>"+pager.CurrentPageIndex.ToString()+"</b></font>";
}
四.總結(jié)
Oracle中無(wú)法直接返回記錄集,需利用游標(biāo)返回.Oracle中不支持子查詢排序.臨時(shí)表耗費(fèi)資源不推薦使用.
posted on 2009-08-18 18:06
fly 閱讀(2997)
評(píng)論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫(kù)學(xué)習(xí)