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.

Reply via email to