添加ROWNUM实现分页后Oracle SQL查询超时问题排查

我有一条可正常执行的Oracle SQL查询，添加ROWNUM <=5实现分页后出现超时且无结果返回，参考分页规范实现且有类似分页查询可正常运行。相关代码如下：

可正常运行的基础查询 #

select 
    tablea.* 
from 
    (select 
         vcell.operator_name, vcell.vendor_name, 
         v_territory.territory_label as Region, 
         v_zone.zone_short_name as Zone, v_cluster.cluster_nm, 
         vcell.engineering_number, v_location.loc_site_long_nm as Name,
         vcell.Band infoband, vcell.Freq_Band infofreqband, 
         vcell.Carrier infocarr, vcell.channel_bandwidth infobandwidth, 
         vcell.cell_type infocelltype, vcell.enodeb_id infoenbid, 
         vcell.local_cell_id infocellid, vcell.ecell_name infocellname, 
         vcell.Sector infosector, vcell.Channel infochannel,
         vcell.physical_cell_id infopci, vcell.TAC infotac, 
         tbl.* 
     from
         cim.v_zone v_zone, cim.v_cluster v_cluster, 
         cim.v_territory v_territory, cim.v_location v_location,
         cim.v_cell_lte vcell, netact_b_lnd.SRAN_CELL_LNCEL tbl 
     where
         v_zone.zone_label = '9' 
         and v_location.operator_name = 'Base' 
         and vcell.operator_name = 'Box' 
         and vcell.vendor_name = 'N' 
         and v_zone.zone_id = v_cluster.zone_id 
         and v_territory.territory_id = v_zone.territory_id 
         and v_cluster.cluster_id = v_location.cluster_id 
         and vcell.engineering_number = v_location.engineering_number 
         and SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'LNBTS-',1) + 6, 6) = vcell.enodeb_id
         and REPLACE(SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME, 'LNCEL-', 1) + 6, INSTR(SUBSTR(tbl.DISTNAME || '/', INSTR(tbl.DISTNAME,'LNCEL-',1) + 6, 4),'/',1)),'/','') = vcell.local_cell_id 
       order by tbl.distname) tablea

添加ROWNUM后超时的查询 #

select 
    tablea.*, ROWNUM 
from 
    (select
         vcell.operator_name, vcell.vendor_name, 
         v_territory.territory_label as Region, 
         v_zone.zone_short_name as Zone, v_cluster.cluster_nm, 
         vcell.engineering_number, v_location.loc_site_long_nm as Name,
         vcell.Band infoband, vcell.Freq_Band infofreqband, 
         vcell.Carrier infocarr,vcell.channel_bandwidth infobandwidth, 
         vcell.cell_type infocelltype, vcell.enodeb_id infoenbid, 
         vcell.local_cell_id infocellid,vcell.ecell_name infocellname,
         vcell.Sector infosector, vcell.Channel infochannel,
         vcell.physical_cell_id infopci, vcell.TAC infotac, 
         tbl.* 
     from
         cim.v_zone v_zone,cim.v_cluster v_cluster, 
         cim.v_territory v_territory, cim.v_location v_location,
         cim.v_cell_lte vcell, netact_b_lnd.SRAN_CELL_LNCEL tbl 
     where
         v_zone.zone_label = '9'  
         and v_location.operator_name = 'Base' 
         and vcell.operator_name = 'Box' 
         and vcell.vendor_name = 'N' 
         and v_zone.zone_id = v_cluster.zone_id 
         and v_territory.territory_id = v_zone.territory_id 
         and v_cluster.cluster_id = v_location.cluster_id 
         and vcell.engineering_number = v_location.engineering_number 
         and SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'LNBTS-',1) + 6, 6) = vcell.enodeb_id
         and REPLACE(SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'LNCEL-',1) + 6, INSTR(SUBSTR(tbl.DISTNAME || '/', INSTR(tbl.DISTNAME,'LNCEL-',1) + 6, 4),'/',1)),'/','') = vcell.local_cell_id 
       order by tbl.distname) tablea  
where 
    ROWNUM <= 5

可正常运行的类似分页查询 #

select * from ( 
    select a.*, ROWNUM rnum from ( 
        SELECT vcell.operator_name as Provider, vcell.vendor_name as Vendor, loc.region_id as Region, loc.zone_short_name AS Zone, loc.clust, vcell.engineering_number, loc.loc_site_long_nm AS Name, vcell.Band, vcell.Freq_Band, vcell.Carrier,    
        vcell.NRARFCN_DL, vcell.BANDWIDTH_MHZ_DL, vcell.NRARFCN_UL, vcell.BANDWIDTH_MHZ_UL, vcell.cell_type, vcell.gnodeb_id as GNODEBID, vcell.local_cell_id as LOCALCELLID, vcell.cell_name as LOCALCELLNAME, vcell.Sector, vcell.PHYSICALCELLID, vcell.TAC, tbl.* 
        FROM netact_b_lnd.SRAN_NRC_NRCELL tbl 
        INNER JOIN ( SELECT vcl.gnodeb_id, vcl.cell_id, vcl.cell_name, vcl.engineering_number, vcl.operator_name, vcl.vendor_name, vcl.local_cell_id, vcl.NRDUCELLID, vcl.PHYSICALCELLID, vcl.cell_type, vcl.nci, vcl.sector, vcl.band, vcl.carrier, vcl.freq_band, vcl.TAC,  vcl.NRARFCN_DL, vcl.BANDWIDTH_MHZ_DL,
        vcl.NRARFCN_UL, vcl.BANDWIDTH_MHZ_UL FROM cim.v_cell_nr vcl WHERE vcl.operator_name = 'Base' and vcl.vendor_name = 'N') vcell 
        ON SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'MRBTS-',1) + 6, 7) = vcell.gnodeb_id AND REPLACE(SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'NRCELL-',1) + 7, INSTR(SUBSTR(tbl.DISTNAME || '/', INSTR(tbl.DISTNAME,'NRCELL-',1) + 7, 4),'/',1)),'/','') = vcell.local_cell_id 
        INNER JOIN (SELECT v_location.engineering_number, v_location.loc_site_long_nm, v_location.operator_name, v_cluster.region_id, v_zone.zone_short_name, v_cluster.cluster_nm AS clust, v_location.province, v_location.region FROM cim.v_zone v_zone 
        INNER JOIN cim.v_cluster v_cluster ON v_zone.zone_id = v_cluster.zone_id 
        INNER JOIN cim.v_location v_location ON v_cluster.cluster_id = v_location.cluster_id 
        WHERE v_location.operator_name = 'Box') loc ON vcell.engineering_number = loc.engineering_number 
        order BY tbl.distname 
    ) a 
    where ROWNUM < 200000 
) 
where rnum >= 1

问题原因分析 #

1. ROWNUM位置导致执行计划异常：两层嵌套写法中，Oracle优化器可能尝试优先过滤ROWNUM <=5再执行排序，但ROWNUM的赋值依赖结果集生成顺序，实际仍需完成全量数据排序才能取前5条。当内层结果集极大时，全量排序的IO和CPU成本极高，直接引发超时。
2. 字符串函数阻塞索引使用：连接条件中大量使用SUBSTR、INSTR、REPLACE等函数处理tbl.DISTNAME，导致Oracle无法利用该字段的常规索引，只能进行全表扫描，进一步放大性能开销。

解决方案 #

1. 改用三层嵌套分页写法 #

将ROWNUM过滤逻辑放在中间层，明确让优化器先完成内层排序，再取前N条数据，避免全量排序：

select * 
from (
    select tablea.*, ROWNUM rnum 
    from (
        select 
             vcell.operator_name, vcell.vendor_name, 
             v_territory.territory_label as Region, 
             v_zone.zone_short_name as Zone, v_cluster.cluster_nm, 
             vcell.engineering_number, v_location.loc_site_long_nm as Name,
             vcell.Band infoband, vcell.Freq_Band infofreqband, 
             vcell.Carrier infocarr,vcell.channel_bandwidth infobandwidth, 
             vcell.cell_type infocelltype, vcell.enodeb_id infoenbid, 
             vcell.local_cell_id infocellid,vcell.ecell_name infocellname,
             vcell.Sector infosector, vcell.Channel infochannel,
             vcell.physical_cell_id infopci, vcell.TAC infotac, 
             tbl.* 
        from
             cim.v_zone v_zone,cim.v_cluster v_cluster, 
             cim.v_territory v_territory, cim.v_location v_location,
             cim.v_cell_lte vcell, netact_b_lnd.SRAN_CELL_LNCEL tbl 
        where
             v_zone.zone_label = '9'  
             and v_location.operator_name = 'Base' 
             and vcell.operator_name = 'Box' 
             and vcell.vendor_name = 'N' 
             and v_zone.zone_id = v_cluster.zone_id 
             and v_territory.territory_id = v_zone.territory_id 
             and v_cluster.cluster_id = v_location.cluster_id 
             and vcell.engineering_number = v_location.engineering_number 
             and SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'LNBTS-',1) + 6, 6) = vcell.enodeb_id
             and REPLACE(SUBSTR(tbl.DISTNAME, INSTR(tbl.DISTNAME,'LNCEL-',1) + 6, INSTR(SUBSTR(tbl.DISTNAME || '/', INSTR(tbl.DISTNAME,'LNCEL-',1) + 6, 4),'/',1)),'/','') = vcell.local_cell_id 
        order by tbl.distname
    ) tablea  
    where ROWNUM <=5
)
where rnum >=1;

2. 创建基于函数的索引 #

针对连接条件中处理DISTNAME的函数逻辑创建索引，消除全表扫描：

-- 针对enodeb_id匹配逻辑创建索引
CREATE INDEX idx_sran_cell_lncel_enodeb ON netact_b_lnd.SRAN_CELL_LNCEL(
    SUBSTR(DISTNAME, INSTR(DISTNAME,'LNBTS-',1) + 6, 6)
);

-- 针对local_cell_id匹配逻辑创建索引
CREATE INDEX idx_sran_cell_lncel_localcell ON netact_b_lnd.SRAN_CELL_LNCEL(
    REPLACE(SUBSTR(DISTNAME, INSTR(DISTNAME,'LNCEL-',1) + 6, INSTR(SUBSTR(DISTNAME || '/', INSTR(DISTNAME,'LNCEL-',1) + 6, 4),'/',1)),'/','')
);

3. 对比执行计划定位瓶颈 #

使用EXPLAIN PLAN FOR语句对比超时查询和修复后查询的执行计划，重点查看：

• 是否存在TABLE ACCESS FULL（全表扫描）
• 排序操作SORT ORDER BY的执行方式（内存/磁盘）
• 连接操作的类型（嵌套循环/哈希连接/合并连接）

内容的提问来源于stack exchange，提问作者Sanjaya Kasun