Re: does select with sample clause return variable record count

Comments embedded.
On Nov 5, 5:06 am, tamsun <tam..._at_gmail.com> wrote:
> hi,
>
> Database version: Oracle Database 10.2.0.2.0
> OS: RedHat Enterprise 4
>
> I find select statement with sample clause return random count records,

As it should.

> e.g.
>
> select count(*) from ADS_DENDOC;
> select count(*) from (select * from ADS_DENDOC sample(1)) a;
>
> 1st run:
> -----------------
> 1302; -- the record count of table ADS_DENDOC
> 9; -- the record count of sample recordset
>
> 2nd run:
> -----------------
> 1302;
> 12;
>
> 3rd run:
> -----------------
> 1302;
> 14;
>
> 4th run:
> -----------------
> 1302;
> 10;
>
> As Oracle sql reference written,sample_percent is a number specifying
> the percentage of the total row or block count to be included in the
> sample. The value must be in the range .000001 to (but not including)
> 100. Why the record count of sample recordset is variable

You didn't read far enough into that paragraph, the rest of which reads:

"This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table."

This is an ESTIMATE, not an absolute. Your 'understanding' is actually misunderstanding, apparently based up on reading only what you want to read, rather than what you should be reading. If you think you understand a function, but it's behaviour isn't what you expect, then it's highly likely you don't fully understand all that you should about that function. Selective reading of the documentation is a prime source for such confusion.

>
> Best regards
> tamsun

David Fitzjarrell Received on Mon Nov 05 2007 - 08:22:04 CST