Question regarding SAMPLE clause.

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Tue, 4 Mar 2003 14:44:15 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703610021@lnewton.leeds.lfs.co.uk>

Hi T,

I've had a few 'problems' with the sample clause myself on 8174. It seems that you get a random number of rows back. I've tried it on tables with 100 rows and got anything between 5 and 25 rows back for a 10% sample. Seemed odd to me at the time - still does. I've tried it on huge tables and get different numbers of rows each time.

I eneded up wrapping it up like this from a web page generating PL/SQL script :

     FOR c IN (SELECT * FROM (SELECT configitem_asset_id AS asset_id,
                                     configitem_emp_id AS employee_id,
                                     configitem_cid_id AS cid_id
                              FROM   CONFIGITEM SAMPLE (10) -- That's
actually a percentage not a number of rows :o) 
                              WHERE  configitem_loc_id = locid
                             ) 
               WHERE ROWNUM < 11
               ORDER BY asset_id)
     LOOP

So basically I ask for 10% and extract the first 10 rows from the list that comes back - just in case I get more than 10.

I'm generating a web page with a list of 10 randomly chosen assets from our asset database so that the auditors can go find them and confirm tat no-one has pinched them in the meantime !

HTH Regards,
Norman.

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: Telemachus [mailto:telemachus_at_ulysseswillreturn.net] Posted At: Tuesday, March 04, 2003 11:31 AM Posted To: server
Conversation: Question regarding SAMPLE clause. Subject: Question regarding SAMPLE clause.

The Concepts and the SQL Ref aren't too clear on this.

I' like to know if SAMPLE is or is not guaranteed to return exact numbers.

I have a test table with 1,000,000 rows and issue a 50% sample clause Are the over/under results I'm getting acceptable i.e. SAMPLE not guaranteed
to be exact or to within a specific percentage?

<SNIP> Received on Tue Mar 04 2003 - 08:44:15 CST