Lazy Loading Large Data Sets With APEX Interactive Grid

The Challenge of Large Data Sets

Interactive Grid is one of APEX’s most powerful components, but it can struggle with very large data sets if not configured properly. By default, an Interactive Grid fetches rows in batches as the user scrolls, but the initial query still needs to be efficient, and the total row count query can be expensive on tables with millions of rows. Understanding how to configure lazy loading properly will keep your grids responsive even against large tables.

Setting the Fetch Size

The most important setting is the number of rows fetched per AJAX request. In the Interactive Grid attributes, set the “Rows Per Page” to a reasonable value like 50. Under the component’s JavaScript Initialization Code, you can fine tune the fetch behavior:

function(config) {
    config.defaultGridViewOptions = {
        rowsPerPage: 50
    };
    // Disable the total row count to avoid expensive COUNT(*) queries
    config.features = config.features || {};
    config.features.totalRows = false;
    return config;
}

Disabling totalRows is one of the most impactful changes you can make. On a table with 10 million rows, Oracle has to perform a full count to display “Showing 1-50 of 10,000,000” in the pagination bar. By disabling this, APEX only needs to fetch the current page plus one extra row to determine if there are more results.

Optimizing the Source Query

The source query for an Interactive Grid should be as simple as possible. Avoid joining to unnecessary tables in the base query. Instead, use LOV columns for lookup values, which APEX fetches separately and more efficiently. Make sure the columns users are most likely to sort and filter on have appropriate indexes.

If your grid supports text searching across multiple columns, consider adding an Oracle Text index or a concatenated virtual column with a standard index rather than relying on LIKE predicates with leading wildcards, which force full table scans.

Server Side Pagination vs Client Side

APEX Interactive Grids use server side pagination by default, which is correct for large data sets. Never switch to client side pagination for grids backed by tables with more than a few thousand rows. Client side pagination fetches all rows up front, which defeats the purpose of lazy loading and can freeze the browser with enough data.

Progressive Enhancement

For the best user experience, combine lazy loading with a skeleton loading indicator. You can add a CSS animation to the grid’s loading state so users see a visual indicator that data is being fetched rather than staring at a blank grid. This small UX touch makes the application feel responsive even when the query takes a moment to return.