SQL Developer Data Grid Pagination: Edit 100,000 Rows Without Crashing

Why Pagination Matters in SQL Developer

Running a SELECT that returns more than 10,000 rows in SQL Developer’s Data Grid can quickly consume memory and lock the entire result set. The UI may become sluggish or even crash, especially on machines with limited RAM. The Data Grid’s built‑in pagination feature mitigates this by loading only the visible slice of data, keeping the client responsive while still allowing full‑row edits.

Enabling and Configuring Pagination

Pagination is automatic for result sets that exceed a threshold defined in Preferences. You can also fine‑tune it:

1. Open Tools → Preferences.
   • Navigate to Database → Result Sets.
   • Adjust Rows per page (default 1,000). Set to 500 for tighter memory usage.
2. Close Preferences; the next query will show pagination controls at the bottom of the Data Grid.

Hands‑On Example: Editing a Row on Page 42

Assume you have a table employees with 100,000 rows. Follow these steps:

1. Execute a query that returns a large set:

SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE employee_id <= 100000;

2. After the grid loads, locate the pagination bar (bottom of the grid). It displays something like Page 1 of 100.
   • Click the Next button 41 times or enter 42 in the page number box to jump to page 42.
3. In the grid, find the row you want to edit. Double‑click the salary cell and change it to 75000.
   • Press F9 (Commit) or click the green check icon. SQL Developer sends an UPDATE statement for that single row.
4. Verify the change by re‑running the query with a WHERE employee_id = … clause. The updated salary should persist.

During this process, only 500 rows (your page size) are held in memory. Even though the full result set still exists on the server, the client never loads it all at once.

Trade‑Offs and Limitations

• Row‑level Locks: Editing a row locks it until you commit. Other sessions may be blocked if they need the same row.
• Client‑Side Only: Pagination applies only to the Data Grid. Worksheet queries and reports still fetch the entire set, so they can still cause memory pressure.
• Server Load: The database still executes the full SELECT and returns all rows to the client before pagination is applied. Ensure the query is optimized and indexed to reduce server overhead.
• Constraint Violations: If your edit violates a constraint or triggers, the commit will fail. Always test in a non‑production environment first.

Actionable Checklist

1. Set Rows per page to a value that balances memory usage and navigation speed (e.g., 500).
2. Run large queries in the Data Grid to confirm pagination appears.
3. When editing, use F9 to commit immediately and release locks.
4. Monitor the SQL Developer process memory (Task Manager/Activity Monitor) before and after enabling pagination.
5. For critical data, always create a backup or use a sandbox schema before making bulk edits.

By following these steps, you can comfortably edit large result sets in SQL Developer without sacrificing performance or stability.