Well, I really need to guarantee that I return some rows and that's
not possible using a random sample. Good thought, though.
"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3bcd9bb8$0$232$ed9e5944_at_reading.news.pipex.net>...
> If you are on 8i you could try
>
> select offer_id,title,line from
> (
> SELECT offer_id,title,line from onsite offers where cost > 0 and bid_amount
> < 1 sample(10)
> )
> where rownum < 2;
>
> the subquery will return a random 10% of your rows. You could obviously use
> a different figure than 10.
>
> I offer no guarantees as to performance.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> "Shaun Martinec" <smartinec_at_usa.net> wrote in message
> news:c471783e.0110170646.5162b9ec_at_posting.google.com...
> > I have the following sql query that gets 2
> > random rows from the table:
> >
> > SELECT offer_id,title,line FROM
> > (select DISTINCT offer_id,title,line,DBMS_RANDOM.RANDOM r FROM
> > onsite_offers WHERE cost > 0 AND bid_amount < .1 ORDER BY r)
> > WHERE rownum <= 2;
> >
> > Is there any side-effects that result from using DBMS_RANDOM.RANDOM in
> > this way? This query seems to spawn an Oracle process that takes up
> > 40% of the CPU. The SQL that the process is executing is:
> >
> > SELECT user FROM SYS.DUAL;
> >
> > The query will stay running on the CPU for a long time. We are running
> > Oracle 8i Standard on Solaris. The query is getting executed through
> > the JDBC thin client drivers running under Allaire JRUN. Any leads
> > would be greatly appreciated. Thanks.
Received on Wed Oct 17 2001 - 13:08:25 CDT
