Re: DBMS_RANDOM SQL Query Issue

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.