Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Unanswered: fetching one random record

    I want to fetch one random record from a table having 5000-6000 records.

    Now what i do is.

    select * from (select * from TABLE_NAME where COLUMN_NAME=SOMEVALUE ORDER BY DMBS_RANDOM ) where ROWNUM <= 1

    But this way, it first randomly sorts all the resulting Rows then gives me back the first row.

    is there any better way to do this, because this way execution time increases with the no. of returned row...
    so is it possible to fetch one random record from a table without sorting the rows ( using ORDER BY ).

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    You could use the sample clause to initially reduce the number of rows. Unfortunately it's based on a percentage of the number of rows so you would need to have a fair idea of the rowcount to start with.

    select *
    from TABLE_NAME
    where COLUMN_NAME=SOMEVALUE
    sample(1)

    would return 1% of the rows. You would then pass this through your outer select to only retrieve the first row. The percent range can go quite low (down to 0.000001 or so - check the docs).

    Hth
    Bill

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    yes there is.

    I did it once where the customer requested a random 500 rows of data from each table having over 200k rows each.

    We created views, which when selected, generated a random output from the table.

    I'll try to look up and see if I still have the code anywhere. It was pretty interesting stuff.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296

    Thumbs up found it!

    Hah!
    I found it.

    basically change the 'tablename' to the table you want, change the 'column_name' to any column in the table (it doesn't matter what column, but perhaps something that is in the PK like a customer_id or something).

    Then run the select on the view where rownum = 1 and PRESTO!

    PHP Code:
    CREATE OR REPLACE VIEW random_tablename AS 
    SELECT 
      DBMS_UTILITY
    .GET_HASH_VALUE 
      
    (TO_CHAR(DBMS_UTILITY.GET_TIME) || column_name,  21038576"RANDOM_ORDER",
      
    tablename.*
    FROM 
      table_name ORDER BY RANDOM_ORDER
    ;
      
      
    SELECT FROM random_tablename
        WHERE ROWNUM 
    =1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    That's an interesting way of doing it, it certainly gets a random row but I just tried it on a large table using a PK column and it's performing full table scans each time. I suppose it's due to the RANDOM_ORDER column having no index available. I don't think it will deal with the original problem of performance.

    Also Vineetgoel, I think I gave you a bum steer on the sample syntax....

    select * from (
    select *
    from TABLE_NAME sample(1)
    where COLUMN_NAME=SOMEVALUE
    ) where rownum <= 1

    It's showing good consistent performance. Running it here on a table of 100k+ rows, the sample size can be safely decreased to 0.01 or less for better performance.

    Hth
    Bill

  6. #6
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    Do you have a sequential primary key?

    If so, you may consider using the DBMS_RANDOM package to generate a
    randiom number, and you can pull that record out of the database. Do
    that inside of a loop that executes 300 times.

  7. #7
    Join Date
    Jul 2002
    Posts
    29
    You can try the sample clause

    select * from <table_name> sample(1)
    where rownum = 1

    The sample clause pulls out a random sample of n% the table.

    rajXesh
    All you need in this life is ignorance and confidence, and then success is sure.
    -- Mark Twain (1835 - 1910)

  8. #8
    Join Date
    Aug 2003
    Posts
    4

    Re: fetching one random record

    I am really thankfull to you guys for sharing your tips to me...
    these things are doing better than what i was doing,
    but i think the sample() is much better..

    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •