Oracle SQL query times out after ROWNUM is added for pagination

Data and Databases
1

I am facing a strange problem with my Oracle SQL query. The initial query runs smoothly and quickly retrieves results. However, when I introduce ROWNUM to implement pagination, the query just times out without producing any output. I’m struggling to identify what might be causing this issue.

-- This query executes without any problems
select 
    primary_table.* 
from 
    (select 
         operator.provider_code, operator.equipment_brand, 
         area_view.area_name as Region, 
         district_view.district_code as District, cluster_view.cluster_name, 
         operator.site_number, site_view.site_full_name as SiteName,
         operator.frequency_band band_info, operator.channel_freq freq_info, 
         operator.carrier_id carrier_info, operator.bw_config bandwidth_info, 
         operator.node_type type_info, operator.base_station_id bs_info, 
         operator.cell_number cell_info, operator.cell_identifier name_info, 
         operator.sector_num sector_info, operator.rf_channel channel_info,
         operator.pci_value pci_info, operator.tracking_area_code tac_info, 
         equipment_table.* 
     from
         network.area_view area_view, network.cluster_view cluster_view, 
         network.district_view district_view, network.site_view site_view,
         network.lte_stations operator, equipment_db.LTE_NODE_DATA equipment_table 
     where
         district_view.district_code = '7' 
         and site_view.provider_code = 'Primary' 
         and operator.provider_code = 'Main' 
         and operator.equipment_brand = 'E' 
         and district_view.district_id = cluster_view.district_id 
         and area_view.area_id = district_view.area_id 
         and cluster_view.cluster_id = site_view.cluster_id 
         and operator.site_number = site_view.site_number 
         and SUBSTR(equipment_table.NODE_PATH, INSTR(equipment_table.NODE_PATH,'BASE-',1) + 5, 6) = operator.base_station_id
         and REPLACE(SUBSTR(equipment_table.NODE_PATH, INSTR(equipment_table.NODE_PATH, 'CELL-', 1) + 5, INSTR(SUBSTR(equipment_table.NODE_PATH || '/', INSTR(equipment_table.NODE_PATH,'CELL-',1) + 5, 4),'/',1)),'/','') = operator.cell_number 
       order by equipment_table.node_path) primary_table

-- This version hangs indefinitely
select 
    primary_table.*, ROWNUM 
from 
    (select
         operator.provider_code, operator.equipment_brand, 
         area_view.area_name as Region, 
         district_view.district_code as District, cluster_view.cluster_name, 
         operator.site_number, site_view.site_full_name as SiteName,
         operator.frequency_band band_info, operator.channel_freq freq_info, 
         operator.carrier_id carrier_info, operator.bw_config bandwidth_info, 
         operator.node_type type_info, operator.base_station_id bs_info, 
         operator.cell_number cell_info, operator.cell_identifier name_info,
         operator.sector_num sector_info, operator.rf_channel channel_info,
         operator.pci_value pci_info, operator.tracking_area_code tac_info, 
         equipment_table.* 
     from
         network.area_view area_view, network.cluster_view cluster_view, 
         network.district_view district_view, network.site_view site_view,
         network.lte_stations operator, equipment_db.LTE_NODE_DATA equipment_table 
     where
         district_view.district_code = '7'  
         and site_view.provider_code = 'Primary' 
         and operator.provider_code = 'Main' 
         and operator.equipment_brand = 'E' 
         and district_view.district_id = cluster_view.district_id 
         and area_view.area_id = district_view.area_id 
         and cluster_view.cluster_id = site_view.cluster_id 
         and operator.site_number = site_view.site_number 
         and SUBSTR(equipment_table.NODE_PATH, INSTR(equipment_table.NODE_PATH,'BASE-',1) + 5, 6) = operator.base_station_id
         and REPLACE(SUBSTR(equipment_table.NODE_PATH, INSTR(equipment_table.NODE_PATH,'CELL-',1) + 5, INSTR(SUBSTR(equipment_table.NODE_PATH || '/', INSTR(equipment_table.NODE_PATH,'CELL-',1) + 5, 4),'/',1)),'/','') = operator.cell_number 
       order by equipment_table.node_path) primary_table  
where 
    ROWNUM <= 10

I have another similar query that successfully implements pagination using a three-level nesting approach:

select * from ( 
    select output.*, ROWNUM row_position from ( 
        SELECT node.provider_code as Company, node.equipment_brand as Brand, location.territory_id as Area, location.district_code AS Region, location.cluster_short, 
        node.site_number, location.site_full_name AS LocationName, node.frequency_band, node.channel_band, node.carrier_num,    
        node.DOWNLINK_FREQ, node.DL_BANDWIDTH, node.UPLINK_FREQ, node.UL_BANDWIDTH, node.node_type, node.base_id as BASEID, 
        node.cell_num as CELLNUM, node.cell_identifier as CELLNAME, node.sector_id, node.PHYS_CELL_ID, node.TRACK_AREA, equipment.* 
        FROM equipment_db.NR_CELL_CONFIG equipment 
        INNER JOIN ( SELECT n.base_id, n.cell_num, n.cell_identifier, n.site_number, n.provider_code, n.equipment_brand, n.cell_num, n.CELL_ID_NR, n.PHYS_CELL_ID, n.node_type, n.network_cell_id, n.sector_id, n.frequency_band, n.carrier_num, n.channel_band, n.TRACK_AREA,  
        n.DOWNLINK_FREQ, n.DL_BANDWIDTH, n.UPLINK_FREQ, n.UL_BANDWIDTH FROM network.nr_nodes n WHERE n.provider_code = 'Primary' and n.equipment_brand = 'E') node 
        ON SUBSTR(equipment.CONFIG_PATH, INSTR(equipment.CONFIG_PATH,'NODE-',1) + 5, 7) = node.base_id AND REPLACE(SUBSTR(equipment.CONFIG_PATH, INSTR(equipment.CONFIG_PATH,'NRCELL-',1) + 7, INSTR(SUBSTR(equipment.CONFIG_PATH || '/', INSTR(equipment.CONFIG_PATH,'NRCELL-',1) + 7, 4),'/',1)),'/','') = node.cell_num 
        INNER JOIN (SELECT site.site_number, site.site_full_name, site.provider_code, cluster.territory_id, district.district_code, cluster.cluster_short AS cluster_short, site.state_code, site.territory FROM network.district_view district 
        INNER JOIN network.cluster_view cluster ON district.district_id = cluster.district_id 
        INNER JOIN network.site_view site ON cluster.cluster_id = site.cluster_id 
        WHERE site.provider_code = 'Main') location ON node.site_number = location.site_number order BY equipment.config_path ) output 
          where ROWNUM < 150000 ) 
where row_position >= 1

Could anyone explain why the ROWNUM addition disrupts the first query while it functions correctly in the second query?

2

I’ve hit this Oracle issue before - it’s your ORDER BY in the subquery. Oracle can’t efficiently push down the sort when you use ROWNUM, so it materializes the whole result set before limiting rows. Two fixes: move ORDER BY to the outer query, or swap ROWNUM for ROW_NUMBER() OVER (ORDER BY equipment_table.node_path). Both will perform way better.

3

that’s really strange! have you looked at the execution plans for both queries? i wonder if oracle is choosing a diff optimizer path after adding rownum. does it time out immediately or after some time? maybe try adding /*+ first_rows */ as a hint to see if that helps!

4

I hit this exact problem building a telecom reporting system. Oracle struggles with ROWNUM when you mix complex joins and string functions. Your second query works because it uses proper INNER JOINs. This lets Oracle’s optimizer estimate row counts correctly and pick better execution paths. The first query uses old comma joins plus expensive SUBSTR and INSTR operations on equipment_table.NODE_PATH. Oracle can’t figure out how many rows to process before running those string functions, so it evaluates every possible combination. Switch your WHERE clause joins to explicit INNER JOINs like you did in the working query. Oracle’s optimizer gets the stats it needs to process only necessary rows before hitting ROWNUM. The three-level nesting also helps Oracle understand the execution order better.