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.

Reply via email to