File Library

SQL "Pagination" Pattern: Designing Efficient Top-N and Pagination Queries

Topic: Developer's Toolkit

Owner: Maxym Kharchenko

Date: 2014-06-22

Subtopic: Best Practices

BASICS:
-- Top-N and pagination queries: Real life examples

LOGICAL DESIGN:
-- Coding top-n and pagination queries in ORACLE sql: the legacy way
-- Coding top-n and pagination queries in ORACLE sql: the 12c way

EFFICIENCY 101:
-- How to make top-n queries inefficient
-- The ugly: Reading, filtering and sorting
-- The bad: Reading and filtering
-- The (still) bad: Trips to the table

-- Perfect access path and how to make it happen

EFFICIENCY 102:
-- Top-N with joins
-- Smart pagination with re-start tokens

PROBLEMS AND GOTCHAS:
-- Multiple pagination scenarios and perfect indexes
-- Handling deleted or logically deleted data

Download File mkharchenko.sqlpagination.presentation.pdf

Become a member of ODTUG to gain access to more than 12,500 files in our technical database.

Not a member? Click through to the topic of interest to browse a list of available presentations.
Already a member? Log-in here to access the full database

Click here to see the Full Techincal Resource Database