SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-11
Examples of SAMPLE
Cluster sampling can be done only on a base table, not on intermediate results.
Cluster sampling is generally faster than sampling individual rows because fewer
blocks are read from disk. In random row and periodic sampling, the entire result table
being sampled is read, and each row in the table is processed. In cluster sampling,
only the disk blocks that are part of the result table are read and processed. Therefore,
if the sampling percentage is small, the performance advantage of cluster sampling
over other sampling methods can be dramatic.
Cluster sampling is designed for large tables. It might return zero rows if there are not
enough blocks in a table to fill at least one cluster and you specify a large cluster size.
This can also happen with a partitioned table if each partition does not have enough
blocks to fill at least one cluster. For example, if a table uses 1000 blocks and is
distributed over 256 partitions, there will be an average of 4 blocks per partition. If you
specify a SAMPLE RANDOM clause with a cluster size of 25 blocks and ten percent,
even if there are 10,000 rows in the table, sampling will result in the SELECT
statement returning 0 rows. To avoid this, use a smaller CLUSTER size.
For more information, see the SQL/MX Query Guide.
Examples of SAMPLE
Within SQLCI, suppose that the data-mining tables SALESPER, SALES, and DEPT
have been created as:
CREATE TABLE $db.mining.salesper
( empid NUMERIC (4) UNSIGNED NOT NULL
,dnum NUMERIC (4) UNSIGNED NOT NULL
,salary NUMERIC (8,2) UNSIGNED
,age INTEGER
,sex CHAR (6)
,PRIMARY KEY (empid) );
CREATE TABLE $db.mining.sales
( empid NUMERIC (4) UNSIGNED NOT NULL
,product VARCHAR (20)
,region CHAR (4)
,amount NUMERIC (9,2) UNSIGNED
,PRIMARY KEY (empid) );
CREATE TABLE $db.mining.dept
( dnum NUMERIC (4) UNSIGNED NOT NULL
,name VARCHAR (20)
,PRIMARY KEY (dnum) );
Within MXCI, the ANSI alias name has been mapped as:
CREATE SQLMP ALIAS db.mining.salesperson $db.mining.salesper;
CREATE SQLMP ALIAS db.mining.sales $db.mining.sales;
CREATE SQLMP ALIAS db.mining.department $db.mining.dept;
Suppose, too, that sample data is inserted into this database similar to the data in the
sample database.