thanks for that.

in a department we can have at an average 1000 to 5000 employees.

but i run the query in my db, it takes around 8 seconds
this is the autotrace output

1)

SQL> SELECT * FROM ( SELECT * FROM people ORDER BY dbms_random.VALUE ) WHERE ROWNUM <= 4

Execution Plan
----------------------------------------------------------
   0|    |SELECT STATEMENT Optimizer=CHOOSE (Cost=1780 Card=4 Bytes=28|
   1|   0|  COUNT (STOPKEY)                                           |
   2|   1|    VIEW (Cost=1780 Card=88663 Bytes=28904138)              |
   3|   2|      SORT (ORDER BY STOPKEY) (Cost=1780 Card=88663 Bytes=10|
   4|   3|        TABLE ACCESS (FULL) OF 'SID_EM_REF' (Cost=157 Card=8|




Statistics
----------------------------------------------------------
         26  recursive calls
          0  db block gets
       1626  consistent gets
       1351  physical reads
          0  redo size
       1978  bytes sent via SQL*Net to client
        311  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed


but when i do,

2)

SQL> SELECT * FROM (SELECT a.*, ROWNUM rn FROM SID_EM_REF a WHERE ROWNUM <= 562) WHERE rn = 562

Execution Plan
----------------------------------------------------------
   0|    |SELECT STATEMENT Optimizer=CHOOSE (Cost=157 Card=562 Bytes=1
   1|   0|  VIEW (Cost=157 Card=562 Bytes=190518)                     
   2|   1|    COUNT (STOPKEY)                                         
   3|   2|      TABLE ACCESS (FULL) OF 'SID_EM_REF' (Cost=157 Card=886




Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1550  bytes sent via SQL*Net to client
        311  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

so, i think i write a proc and store dbms_random.value in 4 variables and union all the queries simular to 2 replacing 562 with the 4 variables.

any feedback.

because we are going to put this in home page of site.i think 8 seconds is slow.
 

Hi Tom,

I have created your random package with 7.3. (we are in the process of upgrading to 902, but it 

will take another 6 months as we have a huge number of users).

We have a requirement :

    We call companies to offer tenders.
    The companies information is stored in a table called "fatwa" which has over a million 

rows.

    In order to give fair chance to companies and to involve no user intervention in 

selecting companies we decided to randomly select a given set of companies and call them to offer 

tender.

    After selecting the companies I am entering the company id of the selected companies in a 

table called "old_fatwa" so that later I can exclude them.
    And when ever I select new companies I query the "fatwa" table like this :

First a count(*) from the table.

SQL> select count(*) from fatwa;

  COUNT(*)
----------
   1179651

Elapsed: 00:00:02.05

And now selecting companies randomly.

SQL> ed
Wrote file afiedt.buf

  1  select fatwaid from
  2     (select * from fatwa where fatwaid not in
  3             (select fatwaid from old_fatwa)
  4     order by random.rand)
  5* where rownum <=5
SQL> /

   FATWAID
----------
     10407
     22342
     50031
     71660
     54781

Elapsed: 00:02:51.03
SQL> /

   FATWAID
----------
     42989
     66700
    215773
    249553
    228372

Elapsed: 00:02:55.00

I am able to get the companies randomly but it is taking too long.....
Can you please help me reduce that.

Thanks Brijesh.
 

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Release 8.1.5.0.0 - Production
PL/SQL Release 8.1.5.0.0 - Production
CORE Version 8.1.5.0.0 - Production
TNS for 32-bit Windows: Version 8.1.5.0.0 - Production
NLSRTL Version 3.4.0.0.0 - Production

The given query is not working

SQL> select *
  2        from ( select empno, ename
  3                 from emp
  4                        where ename like '%'
  5                        order by dbms_random.value )
  6       where rownum <= 4
  7  /
                      order by dbms_random.value )
                                           *
ERROR at line 5:
ORA-00904: invalid column name

Please help me.
Thirumal
 

Hi Tom,

can you explain if this is documented behaviour or not (we're at 9.2.0.4) :

We have 19502 distinct rows in sites :

SQL> SELECT COUNT(*) FROM sites;

  COUNT(*)
----------
     19502

1 row selected.

These two queries just prove that the query returns a random row each time :

SQL> SELECT sit_id
  2  FROM ( SELECT sit_id, ROW_NUMBER() OVER (ORDER BY dbms_random.value) rn FROM sites )
  3  WHERE rn = 1
  4  /

    SIT_ID
----------
  73458946

1 row selected.

SQL> /

    SIT_ID
----------
  76248570

1 row selected.

However, put the query as an inline select and we always get the same row for every row in the outer query :
SQL> SELECT
  2    x_y,
  3    ( SELECT sit_id
  4        FROM ( SELECT sit_id, ROW_NUMBER() OVER (ORDER BY dbms_random.value) rn FROM sites )
  5       WHERE rn = 1 ) site_id
  6  FROM t2
  7  /

X    SITE_ID
- ----------
x   76362038
x   76362038

2 rows selected.

SQL> /

X    SITE_ID
- ----------
x   76392370
x   76392370

2 rows selected.

Thanks in advance

 

hi tom,


the sample percentage clause doesn't match the given percentage exactly but only in an "up to" pecentage manner. why?

is there a way to return a given percentage of rows EXACTLY using the sample clause syntax?


SQL> create table test as select rownum r from all_tables where rownum <= 100;

Tabelle wurde angelegt.

SQL> select count (*) from test;

  COUNT(*)
----------
       100

SQL> select * from test sample (5);

         R
----------
         4
        96
        97

SQL> select * from test sample (5);

         R
----------
        17
        51
        58
        74

SQL> select * from test sample (5);

         R
----------
        14
        32
        94
        97
 

Here is an implementation on Oracle 8.1.7 to get one row randomly and quickly from large tables.

SQL> create or replace function get_random_rowid
( table_name varchar2
) return urowid
as
sql_v varchar2(100);
urowid_t dbms_sql.urowid_table;
cursor_v integer;
status_v integer;
rows_v integer;
begin
  for exp_v in -6..2 loop
    exit when (urowid_t.count > 0);
    if (exp_v < 2) then
      sql_v := 'select rowid from ' || table_name
      || ' sample block (' || power(10, exp_v) || ')';
    else
      sql_v := 'select rowid from ' || table_name;
    end if;
    cursor_v := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_v, sql_v, dbms_sql.native);
    dbms_sql.define_array(cursor_v, 1, urowid_t, 100, 0);
    status_v := dbms_sql.execute(cursor_v);
    loop
      rows_v := dbms_sql.fetch_rows(cursor_v);
      dbms_sql.column_value(cursor_v, 1, urowid_t);
      exit when rows_v != 100;
    end loop;
    dbms_sql.close_cursor(cursor_v);
  end loop;

  if (urowid_t.count > 0) then
    return urowid_t(trunc(dbms_random.value(0, urowid_t.count)));
  end if;

  return null;

exception when others then
  if (dbms_sql.is_open(cursor_v)) then
    dbms_sql.close_cursor(cursor_v);
  end if;
  raise;
end;
/
show errors

Function created.

Elapsed: 00:00:00.94
No errors.
SQL> select count(*) from my_table;

  COUNT(*)
----------
  74152137

1 row selected.

Elapsed: 00:29:23.13
SQL> set autotrace on
SQL> select get_random_rowid('my_table') from dual;

GET_RANDOM_ROWID('
------------------
AABLs0AAcAAALHkABm

1 row selected.

Elapsed: 00:00:06.36

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'

Statistics
----------------------------------------------------------
        177  recursive calls
       9344  db block gets
        162  consistent gets
        584  physical reads
          0  redo size
        227  bytes sent via SQL*Net to client
        207  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace traceonly
SQL> select * from my_table
where rowid = get_random_rowid('my_table');

1 row selected.

Elapsed: 00:00:06.15

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=66)
   1    0   TABLE ACCESS (BY USER ROWID) OF 'MY_TABLE' (Cost=1 Card=1 Bytes=66)

Statistics
----------------------------------------------------------
         10  recursive calls
       9332  db block gets
        109  consistent gets
        614  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        232  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed