Oracle 10g Sample Clause

                                
Oracle 8i first introduced SAMPLE clause – a method of retrieving sample data from a table.
There are 2 ways you can retrieve subset of rows from a table – one is using sample
percentage of rows and another sample number of blocks.

I have created a table, MYTABLE with 50000 rows.

Analyzed the table using dbms_stats package.

select table_name, blocks, empty_blocks
from user_tables where table_name = 'MYTABLE' ;

TABLE_NAME       BLOCKS         EMPTY_BLOCKS
----------------------     ----------           ------------
MYTABLE                    2423                0

The table uses 2423 blocks.

     SQL>select count(*) from mytable;
     
     COUNT(*)
      -----------
      50000

To retrieve 5 percent of rows from a table, use the command shown below:

SQL> select count(*) from mytable sample(5);

COUNT(*)
-----------
2432

To use 10 percent of blocks in the sample, use the command given below:

SQL> select count(*) from mytable sample block(10) ;

COUNT(*)
----------
     3696

I believe those two sample clauses should be used only in SQL*PLUS to verify that the table has
some rows or expected number of rows. You should never use those two clauses in a production
code- meaning you should not derive an application logic based on the sample clause, because
some time the select statement may NOT return rows at all.

Let me delete 49,999 rows from the table.

SQL> delete mytable where rownum <= 49999 ;

49999 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from mytable;

COUNT(*)
----------
        1

Let us see what oracle returns from the first query:

SQL> select count(*) from mytable sample (5) ;

COUNT(*)
----------
        0            -- even though the table has one row, sample clause returns 0 rows.

SQL> select count(*) from mytable sample block(10) ;

COUNT(*)
----------
        1              --- I got one row.

SQL> select count(*) from mytable sample block(10) ;

COUNT(*)
----------
        0              ---- Next time, the same query returns 0 rows. !!!!

Enhancement in 10g

Prior to 10g, a table with sample clause is not allowed to join with another table.

In 9iR2

SQL> select a.object_id, b.object_name
           from   mytable a, mytable2 sample (3) b
         where a.object_id = b.object_id
SQL> /
  where a.object_id = b.object_id
  *
ERROR at line 3:
ORA-30561: SAMPLE option not allowed in statement with multiple table references

Now, the restriction is removed in 10g.

In 10gR2

SQL> select a.object_id, b.object_name
2 from   mytable a, mytable2 sample (3) b
3 where a.object_id = b.object_id ;

no rows selected

I can use sample clause on both tables.

SQL> select a.object_id, b.object_name
2 from   mytable sample block(30) a, mytable2 sample (3) b
3   where a.object_id = b.object_id ;

no rows selected

Cheers, Tamil