Thanks for your answers, also on StackOverflow. --Luigi
On Mon, Jan 14, 2013 at 9:08 PM, Thomas Mueller < [email protected]> wrote: > Hi, > > I would avoid using "order by rand()" if possible. > > The trick with min(id) / max(id) should work, unless there are many gaps. > If there are many gaps, things get complicated. > > I don't know if there is a good way that works well in all databases. > Maybe you want to ask on StackOverflow? > > An efficient algorithm would be to use random access / index lookup, or > skip (to select the nth row of a table). But like most databases, H2 > doesn't support it yet. The next-generation storage engine for H2, the > MVStore, does in fact support this feature. But the MVStore isn't stable > yet, and running SQL against the MVStore isn't supported at this time: > http://h2database.com/html/mvstore.html "Also supported, and very > uncommon for maps, is fast index lookup." > > Regards, > Thomas > > > > > > On Mon, Jan 14, 2013 at 11:27 AM, Noel Grandin <[email protected]>wrote: > >> >> On 2013-01-14 12:08, Luigi R. Viggiano wrote: >> >> >> I've searched around and I found answers like this: SELECT * FROM sample >> ORDER BY RAND() limit X. >> >> It looks to me that this will fetch the full table, isn't it? >> >> Yeah, that wouldn't work very well with H2, we'd scan the whole table. >> >> I think it would be best to generate 10 or 50 random intengers and do a >> "select * from sample where rowid in (<random integer list>)". But afaik, >> rowid concept is missing in H2, so I may opt for using the ID column in my >> table. >> >> >> Fetch MIN(id) and MAX(id) and cache them, then generate some random >> numbers between the two and do a >> >> select * from sample where id in (<random integer list>) >> >> That should do the trick since we tend to be very linear with our >> sequences. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "H2 Database" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/h2-database?hl=en. >> > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
