记录一次Oracle数据库千万级数据表的分页性能优化

项目背景：

        公司有一个项目用的是oracle数据库，用户数据量比较大。有1200w+(其实也不多)。然后在分页查询的时候到300w左右的时候就比较慢了，大概要3s+，到600w左右的时候要6s+。其实这个表的查询并不复杂。就是一个单表的数据分页查询。

原来的分页SQL-1:

        通过使用逻辑分页行编号值ROWNUM进行分页

SELECT * FROM
    (SELECT
        T.*, ROWNUM AS ROWNO FROM
        (SELECT
            CUST_ID,
            CUST_NAME,
            CUST_NAT,
            CERT_NO,
            ORG_ID,
            TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
            GENDER
        FROM xx_xx
        WHERE 1 = 1) T) R
WHERE ROWNO >= 3000000 AND ROWNO <= 3000050;

 

修改过的分页SQL-2：

        也是通过逻辑分页编号值ROWNUM进行分页

SELECT * FROM
    (SELECT
        T.*,
        ROWNUM AS ROWNO
    FROM
        (SELECT
            CUST_ID,
            CUST_NAME,
            CUST_NAT,
            CERT_NO,
            ORG_ID,
            TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
            GENDER
        FROM xx_xxWHERE
            1 = 1 AND ROWNUM <= 3000050) T) R
WHERE ROWNO>3000000;

  

修改过的分页SQL-3：

        使用物理分页行编号值rowid

SELECT
    CUST_ID,
    CUST_NAME,
    CUST_NAT,
    CERT_NO,
    ORG_ID,
    TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
    GENDER
FROM
    T2A_CUST_I
WHERE rowid IN (
    SELECT t2.rid
    FROM
        (SELECT
            t1.*,
            ROWNUM rn
        FROM
            (SELECT
                CUST_ID,
                CUST_NAME,
                CUST_NAT,
                CERT_NO,
                ORG_ID,
                TO_CHAR(CREATE_DT, 'YYYY-MM-DD') AS CREATE_DT,
                GENDER,
                rowid AS rid
            FROM xx_xx
            WHERE 1 = 1) t1)t2
    WHERE rn>3000000 AND rn < 3000050);

 

 分析：

      1、ROWNUM和rowid的区别？ ROWNUM是逻辑地址，表示查询耨条记录在整个结果集中的位置，同一条记录查询条件不同对应的rownum是不同的二rowid是不会变的。rowid是物理地址，用于定位数据表中某条数据的位置，是唯一的、不会改变的，查询快

      2、SQL-1和SQL-2都是通过ROWNUM来分页的，效果为什么会有很大差距？这个网上很多都是说CBO优化模式，Oracle可以讲外层的查询条件推到内层查询中，以提高内层查询的执行效率。很多帖子都在。大家自己可以了解一下。

        

性能对比

	3000000-3000050	6000000-6000050	9000000-9000050
SQL-1	7.8s	8s	8.2
SQL-2	2.2s	4.4s	6.5s
SQL-3	2.9s	2.8s	2.7

 

 

      

     
  

参考：

https://blog.csdn.net/zp19860529/article/details/114308856

https://www.cnblogs.com/hqbhonker/p/4755694.html