The LIKE operator is a very useful one. It is used to match strings with partial match while using the underscore (‘_’) as a single character wildcard and the percentage sign (‘%’) as multiple character wildcard.
When writing a query with order by, we can use the column position instead of its name. This order by”trick” is easy to use, but should be handled carefully as it affects the order of rows if the column list changes.
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.
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.