Scanning an Index

The internet is full of information about indexes, and for a reason. Indexes in a database is probably the most important performance related topic. There are so many cases, properties, and different ways to use indexes that there is simply a lot to write about. In this post I’d like to talk about a specific use case that I’ve seen a few times, and is related to index scans and performance.

Continue reading

Paging a Query

In the past few months I had several occasions to use the “rownum” pseudo-column to page a query result set. As we know, a query returns a result set, which the application gets and processes. In many cases we wish to show the result set to the user and when the result set is large we would want to show the user only a portion of it (ordered by some key or just a sample). This is often called “paging”. The idea is to show only the beginning of the result set (the first “page”) and allow the user to navigate to other pages to see the rest of the result set. In this case, if we use the application to take this portion of the result set, it would require the entire result set to flow from the database to the application, and then the application would show the relevant part and just ignore the rest; this is both difficult and inefficient.
Continue reading