![]() ![]() Notice that the songs are being listed in random order, thanks to the DBMS_RANDOM.VALUE function call used by the ORDER BY clause. When executing the SQL query above on Oracle, the following result set is obtained: songĮd Sheeran & Passenger – No Diggity (Kygo Remix) On Oracle, you need to use the DBMS_RANDOM.VALUE function, as illustrated by the following example: Luckily, in our case, the song table represents the current playlist of a desktop application, hence the size of the result set is reasonable. ![]() ![]() However, to randomize the returned rows, we need the ORDER BY clause to use a function or database object that returns a random value for each row contained in the SQL result set.Īs already mentioned, using a database-specific function that returns random values is suitable for small result sets only, as otherwise, the SQL query performance is going to be affected. Whenever we need to sort a given SQL query result set, we have to use the ORDER BY clause. We want to shuffle the song list so that each new playlist is different than a previously generated one. Using a RANDOM function in the SQL ORDER BY clause The song table is populated as follows: id Let’s assume we are developing a music player desktop application, and we have the following song table in our database. If you have to shuffle a large result set and limit it afterward, then it’s better to use something like the Oracle SAMPLE(N) or the TABLESAMPLE in SQL Server or PostgreSQL instead of a random function in the ORDER BY clause. Note that sorting a large result set using a RANDOM function might turn out to be very slow, so make sure you do that on small result sets. This is a very handy trick, especially when you want to shuffle a given result set. In this article, we are going to see how we can sort an SQL query result set using an ORDER BY clause that takes a RANDOM function provided by a database-specific function. So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night! Well, Hypersistence Optimizer is that tool!Īnd it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework. Follow having a tool that can automatically detect JPA and Hibernate performance issues. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |