From oracle-l-bounce@freelists.org Fri Oct 7 17:50:29 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j97Mo9Hp030968 for ; Fri, 7 Oct 2005 17:50:19 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j97Mo3vX030941 for ; Fri, 7 Oct 2005 17:50:03 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 04C061F7D4B; Fri, 7 Oct 2005 17:49:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 25409-10; Fri, 7 Oct 2005 17:49:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 68FD81F57CD; Fri, 7 Oct 2005 17:49:09 -0500 (EST) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5CB7B.B2C4B0C6" Subject: RE: Random Select of X % of total records X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Date: Fri, 7 Oct 2005 14:12:00 -0600 Message-ID: <87E9F113CEF1D211A4C3009027301874A10985@ddbcinc.ddbc.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Random Select of X % of total records Thread-Index: AcXLet1j4oC4/u4UQRqX+ZfahD2LfwAAEvbQ From: "Justin Cave (DDBC)" To: , "oracle-l" X-archive-position: 26589 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jcave@ddbcinc.com Precedence: normal Reply-To: jcave@ddbcinc.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-2.7 required=5.0 tests=AWL,BAYES_00,HTML_70_80, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5CB7B.B2C4B0C6 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Assuming the desire is to see roughly 10% of the CLAIMS table and you don't have a particularly strict definition of "random", you should be able to just use the SAMPLE clause =20 SCOTT @ hp92 Local> create table t as select * from all_objects; =20 Table created. =20 Elapsed: 00:00:04.23 SCOTT @ hp92 Local> select count(*) from t; =20 COUNT(*) ---------- 29907 =20 Elapsed: 00:00:00.54 SCOTT @ hp92 Local> select count(*) from t sample(10); =20 COUNT(*) ---------- 2989 =20 Elapsed: 00:00:00.08 =20 Justin Cave Distributed Database Consulting, Inc. http://www.ddbcinc.com =20 ________________________________ From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Kevin Lange Sent: Friday, October 07, 2005 4:07 PM To: oracle-l Subject: Random Select of X % of total records =20 Evening all; One of our developers is asking if there is a simple SQL way to generate a random select of the total records in a table. i.e. They want to see 10% of the records in the CLAIMS table. =20 Database in question is 9i (9.2.0.6) =20 SQL has to be simple because its going to be used by Cognos which does not allow anonymous sql blocks. =20 I was hoping that some of the new analytical functions would work for this but I have not used them yet. =20 Any help out there ?? ------_=_NextPart_001_01C5CB7B.B2C4B0C6 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Assuming the desire is to see = roughly 10% of the CLAIMS table and you don’t have a particularly strict = definition of “random”, you should be able to just use the SAMPLE = clause

 

SCOTT @ hp92 Local> create table = t as select * from all_objects;

 

Table = created.

 

Elapsed: = 00:00:04.23

SCOTT @ hp92 Local> select = count(*) from t;

 

  = COUNT(*)

----------<= /p>

     = 29907

 

Elapsed: = 00:00:00.54

SCOTT @ hp92 Local> select = count(*) from t sample(10);

 

  = COUNT(*)

----------<= /p>

      = 2989

 

Elapsed: = 00:00:00.08

 

Justin Cave  = <jcave@ddbcinc.com>

Distributed Database Consulting, = Inc.

http://www.ddbcinc.com

 

From: = oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Kevin Lange
Sent: Friday, October 07, = 2005 4:07 PM
To: oracle-l
Subject: Random Select of = X % of total records

 

Evening = all;

  One of our developers is = asking if there is a simple SQL way to generate a random select of the total = records in a table.   i.e. They want to see 10% of the records in the = CLAIMS table.

 

Database in question is 9i = (9.2.0.6)

 

SQL has to be simple because its = going to be used by Cognos which does not allow anonymous sql = blocks.

 

I was hoping that some of the new analytical functions would work for this but I have not used them = yet.

 

Any help out there = ??

------_=_NextPart_001_01C5CB7B.B2C4B0C6-- -- http://www.freelists.org/webpage/oracle-l