Optimizing top-N and pagination queries
Queries to find the top-N rows or page through results are common. For example, to find the three winners of a tournament, top five selling products or page 5 of the most recent comments.
But how do you write the SQL to answer these questions and which indexes ensure the statements are fast?
Watch the recording to learn the basics of order by, how to use fetch first to get the top-N rows and paged search results, and indexing strategies to make these queries fast.
Highlights include:
00:10 - Slow pagination query
01:50 - Order by basics
14:00 - Demo of order by
17:30 - Demo controlling location of nulls
26:00 - Top-N queries using fetch first
30:00 - Real world demo of fetch first N bricks
32:40 - Creating indexes for Top-N queries
35:20 - Demo of Top-N queries
40:50 - Demo of using indexes to avoid sorting
46:30 - How descending sorts affects index usage
56:00 - Pagination queries
56:30 - Inconsistent results for pagination queries
59:40 - Performance of pagination queries
1:02:55 - Demo of pagination queries
1:09:50 - Summary of pagination & top-N SQL
Watch video Optimizing top-N and pagination queries online without registration, duration hours minute second in high quality. This video was added by user Oracle Developers 16 April 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 888 once and liked it 23 people.