Re: Randomize data from SQL statement

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: 1998/09/21
Message-ID: <6u62q8$r8f$1_at_pascal.a2000.nl>#1/1


Tricia Kha wrote
>I want to generate a 30% random sample output data from my
>Order_number field of my Order_item table.

A quick trick someone posted here before:

    select *
    from all_tables
    where dbms_utility.get_hash_value( dump( rowid ), 0, 100 ) <= 30;

If I am correct, then get_hash_value is available as of version 7.3 (it is not in PO7.2). This will get you the same 30% every time you run the query. You might add sysdate to get different (though not truly random at all) results:

    select *
    from all_tables
    where dbms_utility.get_hash_value( dump( rowid ) + sysdate, 0, 100 ) <= 30;

Better, get the Rand function at
http://www.oracle.com/st/products/features/free_plsql_utilities.html

    select *
    from all_tables
    where Rand < 0.30;

Note that in neither case you can be sure that indeed exactly 30% is retrieved.

Arjan.
I have sent a CC: to your mail address, as my posts are not always visible in all newsgroups :-( Received on Mon Sep 21 1998 - 00:00:00 CEST

Original text of this message