Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: First
> Have to confess that I am really disappointed with the responses from
> Michel, Hans, Rene, and Turkbear because not one gave the answer I
> was going to give. And I was sure as I read through theirs that one or
> all of them would have done it first ... so here's mine:
>
> CREATE TABLE t AS
> SELECT object_name
> FROM all_objects
> WHERE rownum < 81;
>
> SELECT COUNT(*)
> FROM t;
>
> SELECT *
> FROM t
> SAMPLE (25);
>
> Returns 25% of the rows.
Daniel, I was under the impression that the OP wanted to retrieve something like 'exactly n rows', not '25% of the rows'. So, if a table has 80 rows, and one wants to select n=20, 25% would be fine. This has at least two problems: first: sample(25) does not select exactly 25%, but the probability for a row to be selected is 25%. Secondly, one has to know how many rows there are in the table prior to selecting it.
RENE_at_ora10> CREATE TABLE t AS
2 SELECT object_name
3 FROM all_objects
4 WHERE rownum < 81;
Table created.
RENE_at_ora10> SELECT COUNT(*) FROM t;
COUNT(*)
80
1 row selected.
RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
2 FROM t
3 SAMPLE (25);
COUNT(*)
20
1 row selected.
RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
2 FROM t
3 SAMPLE (25);
COUNT(*)
25
1 row selected.
RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
2 FROM t
3 SAMPLE (25);
COUNT(*)
25
1 row selected.
RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
2 FROM t
3 SAMPLE (25);
COUNT(*)
19
1 row selected.
Rene
-- Rene Nyffenegger http://www.adp-gmbh.ch/Received on Thu Aug 05 2004 - 04:15:08 CDT