Re: count(*) - sample block

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Dec 2006 14:50:25 -0800
Message-ID: <1165618212.610191@bubbleator.drizzle.com>

prateek wrote:
> Hi,
> I am curious to know why this query:
>
> "select count(*) from far_employee sample block (1);"
>
> returns zero at times and some other times returns a non zero number.
> Table: far_employee is populated with more than 400K records.
>
> Thanks,
> Prateek

A bug comes to mind rather quickly after running some tests in the lab. I especially like this result (10.2.0.2):

SQL> SELECT COUNT(*)
   2 FROM airplanes
   3 SAMPLE BLOCK (100);
SAMPLE BLOCK (100)

                  *

ERROR at line 3:
ORA-30562: SAMPLE percentage must be in the range [0.000001,100)

Apparently 100 <> 100. <g>

SQL> SELECT COUNT(*)
   2 FROM airplanes
   3 SAMPLE BLOCK (99.999999);    COUNT(*)

---

     250000

But it does get it right if you hedge.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Dec 08 2006 - 16:50:25 CST