Oracle中的sql语句分页性能测试_oracle分页 sql 性能-优快云博客

本文链接：https://blog.youkuaiyun.com/ahqianjin/article/details/5459457

建表:

-- Create table
create table T_CUSTOMINFO
(
CUSTOMID            VARCHAR2(50) not null,
CUSTOMNAME          VARCHAR2(200),
CERTNO              VARCHAR2(50),
CERTTYPE            VARCHAR2(50),
TELNO               VARCHAR2(100),
BIRTHDAY            DATE,
REGISTDATE          DATE,
ADDRESS             VARCHAR2(500),
POSTCODE            VARCHAR2(10),
EMAIL               VARCHAR2(200),
FAXNO               VARCHAR2(100),
SEX                 VARCHAR2(10),
CORPID              VARCHAR2(50),
BUYHOUSETYPE1       VARCHAR2(50),
BUYHOUSETYPE2       VARCHAR2(50),
BUYHOUSEAREABEGIN   NUMBER(5),
BUYHOUSEAREAEND     NUMBER(5),
ISATTIC             VARCHAR2(10),
ISGARAGE            VARCHAR2(10),
NATIONALITY         VARCHAR2(50),
FAMILYNUM           NUMBER(2),
HOUSEDECORATIONNEED VARCHAR2(50),
BUYAIM              VARCHAR2(50),
NATIVEPLACE         VARCHAR2(50)
)
tablespace HOUSE
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 5M
    minextents 1
    maxextents unlimited
    pctincrease 0
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_CUSTOMINFO
add constraint PK_T_CUSTOMINFO primary key (CUSTOMID)

批量插入数据:

insert into t_custominfo (CUSTOMID, CUSTOMNAME, CERTNO, CERTTYPE, TELNO, BIRTHDAY, REGISTDATE, ADDRESS, POSTCODE, EMAIL, FAXNO, SEX, CORPID, BUYHOUSETYPE1, BUYHOUSETYPE2, BUYHOUSEAREABEGIN, BUYHOUSEAREAEND, ISATTIC, ISGARAGE, NATIONALITY, FAMILYNUM, HOUSEDECORATIONNEED, BUYAIM, NATIVEPLACE)
SELECT ROWNUM||'_'||substr(t.customid,5,20), '张三', '242323523523523', '身份证', '124124214125', to_date('30-10-2008', 'dd-mm-yyyy'), to_date('07-04-2010', 'dd-mm-yyyy'), 'werwerwe ffwe', '', '1233@sohu.com', '23423523', '男', '7e28e618-6b6e-4ffc-be5a-91c36b766b38', '一室一厅', '一室一厅', null, null, '是', '是', '中国', 3, '无要求', '投资', ''
FROM T_CUSTOMINFO t

分页:

第一种分页:

WITH SQLRESULT AS
(SELECT CUSTOMID,
         CUSTOMNAME,
         CERTNO,
         CERTTYPE,
         TELNO,
         BIRTHDAY,
         REGISTDATE,
         ADDRESS,
         POSTCODE,
         EMAIL,
         FAXNO,
         SEX,
         CORPID,
         BUYHOUSETYPE1,
         BUYHOUSETYPE2,
         BUYHOUSEAREABEGIN,
         BUYHOUSEAREAEND,
         ISATTIC,
         ISGARAGE,
         NATIONALITY,
         FAMILYNUM,
         HOUSEDECORATIONNEED,
         BUYAIM,
         NATIVEPLACE
    FROM T_CUSTOMINFO T
   WHERE T.CORPID LIKE '%%'
     AND ROWNUM <= ( /*INDEX*/
          8000 + 1) * 30 /*INDEX为第几页,应从0开始*/
)
SELECT *
FROM SQLRESULT A
MINUS
SELECT *
FROM SQLRESULT B
WHERE ROWNUM < /*INDEX*/
       8000 * 30 + 1;

第二种分页:

WITH SQLRESULT AS
(SELECT CUSTOMID
    FROM T_CUSTOMINFO T
   WHERE T.CORPID LIKE '%%'
     AND ROWNUM <= ( /*INDEX*/
          8000 + 1) * 30 /*INDEX为第几页,应从0开始*/
)
SELECT * FROM T_CUSTOMINFO WHERE CUSTOMID IN
(
SELECT A.CUSTOMID
FROM SQLRESULT A
MINUS
SELECT B.CUSTOMID
FROM SQLRESULT B
WHERE ROWNUM < /*INDEX*/
       8000 * 30 + 1
)

第三种分页:

WITH SQLRESULT AS
   (SELECT CUSTOMID,
           CUSTOMNAME,
           CERTNO,
           CERTTYPE,
           TELNO,
           BIRTHDAY,
           REGISTDATE,
           ADDRESS,
           POSTCODE,
           EMAIL,
           FAXNO,
           SEX,
           CORPID,
           BUYHOUSETYPE1,
           BUYHOUSETYPE2,
           BUYHOUSEAREABEGIN,
           BUYHOUSEAREAEND,
           ISATTIC,
           ISGARAGE,
           NATIONALITY,
           FAMILYNUM,
           HOUSEDECORATIONNEED,
           BUYAIM,
           NATIVEPLACE,
           ROWNUM AS row_num
      FROM T_CUSTOMINFO T
     WHERE T.Corpid LIKE '%%'
       AND ROWNUM <= (/*INDEX*/600+1) * 30 /*INDEX为第几页,应从0开始*/
     )
SELECT *
    FROM SQLRESULT A
WHERE NOT EXISTS(SELECT * FROM SQLRESULT B WHERE B.row_num </*INDEX*/ (600*30+1) AND B.CUSTOMID=A.CUSTOMID);

按最坏情况(即最后的30行)测试所耗的时间

行数	第一种分页	第二种分页	第三种分页
9000			很长几乎死机
60030	5秒	0.781秒	死机
120030	9.266秒	1.375秒	…
244768	19.969秒	4.19秒	…