select * from vw_user where user_id in (select user_id from vw_user where 1 = 1 and rownum <= 15 and user_id not in (select user_id from vw_user where 1 = 1 and rownum <= 6975))
这句SQL执行速度超级慢? 为什么?
这句SQL执行速度超级慢? 为什么?
套了3层,貌似有一层还没有必要~SELECT * FROM
(
SELECT ROWNUM AS RN,vw_user.* FROM vw_user)
WHERE RN>=6975 AND RN<=6990
RN>=6960 AND RN<=6975
--包
CREATE OR REPLACE PACKAGE dotnet
IS
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集 PROCEDURE dotnetpagination (
pindex IN NUMBER, --分页索引
psql IN VARCHAR2, --产生dataset的sql语句
psize IN NUMBER, --页面大小
pcount OUT NUMBER, --返回分页总数
v_cur OUT types.cursortype --返回当前页数据记录
); PROCEDURE dotnetpagerecordscount (
psqlcount IN VARCHAR2, --产生dataset的sql语句
prcount OUT NUMBER --返回记录总数
);
END dotnet;
/--包体
CREATE OR REPLACE PACKAGE BODY dotnet
IS
--***************************************************************************************
PROCEDURE dotnetpagination (
pindex IN NUMBER,
psql IN VARCHAR2,
psize IN NUMBER,
pcount OUT NUMBER,
v_cur OUT types.cursortype
)
AS
v_sql VARCHAR2 (1000);
v_count NUMBER;
v_plow NUMBER;
v_phei NUMBER;
BEGIN
------------------------------------------------------------取分页总数
v_sql := 'select count(*) from (' || psql || ')'; EXECUTE IMMEDIATE v_sql
INTO v_count; pcount := CEIL (v_count / psize);
------------------------------------------------------------显示任意页内容
v_phei := (pindex- 1) * psize + psize;
v_plow := v_phei - psize + 1;
--Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
v_sql :=
'select * from ('
|| psql
|| ') where rn between '
|| v_plow
|| ' and '
|| v_phei; OPEN v_cur FOR v_sql;
END dotnetpagination;--**************************************************************************************
PROCEDURE dotnetpagerecordscount (psqlcount IN VARCHAR2, prcount OUT NUMBER)
AS
v_sql VARCHAR2 (1000);
v_prcount NUMBER;
BEGIN
v_sql := 'select count(*) from (' || psqlcount || ')'; EXECUTE IMMEDIATE v_sql
INTO v_prcount; prcount := v_prcount; --返回记录总数
END dotnetpagerecordscount;
--**************************************************************************************
END dotnet;
/
不要出现*,手动全写出来,就算所有列都要显示,