DBMS_RANDOM SQL Query Issue

From: Shaun Martinec <smartinec_at_usa.net>
Date: 17 Oct 2001 07:46:45 -0700
Message-ID: <c471783e.0110170646.5162b9ec@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 - 09:46:45 CDT