Sub GenerateSales()

Open "c:\time\sales.csv" For Output As # 1 

For i =  1  To  10000000 

DDay = Int(Rnd() *  365  +  1 )
Cust = Int(Rnd() *  5000000  +  1 )
Prod = Int(Rnd() *  30000  +  1 )
OrderValue = Int(Rnd() *  50  +  1 )

Write # 1 , i, DDay, Cust, Prod, OrderValue

Next i

Close # 1 

End Sub

CREATE SET TABLE
  tpch.dmtb_sales
  (
  tran_id INTEGER NOT NULL,
  day_id INTEGER,
  cust_id INTEGER,
  prod_id INTEGER,
  sales_amt INTEGER)
  
  PRIMARY INDEX (tran_id)
    PARTITION BY RANGE_N (day_id BETWEEN  1  AND  370  EACH  10 )
;

LOGON demotdat/dbc, dbc;
DATABASE tpch;
SET RECORD VARTEXT ",";
BEGIN LOADING dmtb_sales ERRORFILES dmtb_sales_error_1 ,dmtb_sales_error_2;
DEFINE
	tran_id	(VARCHAR( 20 )),
	day_id	(VARCHAR( 20 )),
	cust_id	(VARCHAR( 20 )),
	prod_id	(VARCHAR( 20 )),
	sales_amt (VARCHAR( 30 ))
FILE = c:\time\sales.csv;
INSERT INTO dmtb_sales
	(
	tran_id,
	day_id,
	cust_id,
	prod_id,
	sales_amt
	)
VALUES
	(
	:tran_id,	
	:day_id,
	:cust_id,
	:prod_id,
	:sales_amt
	)
;
END LOADING;
LOGOFF;

SELECT COUNT(*) FROM dmtb_sales;

SELECT COUNT(DISTINCT cust_id) FROM dmtb_sales;

Report: Количество клиентов, сделавших от  1  до  5  покупок за  10  дней
Data Rows:  1 
Data Columns:  1 
Cache Used: No

Execution Start Time:		 05 / 13 / 2004   02 : 02 : 17 
Execution Finish Time:		 05 / 13 / 2004   02 : 02 : 53 

Total Duration:		 0 : 00 : 41 . 89 
SQL Duration:		 0 : 00 : 36 . 24 
Analytical Duration:		 0 : 00 : 00 . 00 
Other Processing Duration:		 0 : 00 : 05 . 64 

Number of Rows Returned:		 1 
Number of Columns Returned:		 1 
Number of Temp Tables:		 0 

Total Number of Passes:		 1 
Number of SQL Passes:		 1 
Number of Analytical Passes:		 0 

DB User:		dbc
DB Instance:		tpch

Tables Accessed:
dmtb_sales	


SQL Statements:

Pass0 - Duration:  0 : 00 : 36 . 24 
select count(distinct a11.cust_id)  WJXBFS1
from dmtb_sales a11
	join (select a11.cust_id  cust_id
	from dmtb_sales a11
	where a11.day_id between  1  and  10 
	group by a11.cust_id
	having count(a11.tran_id) between  1 . 0  and  5 . 0 
	) pa1
	  on  (a11.cust_id = pa1.cust_id)
where a11.day_id between  1  and  10 

Explanation
   1 ) First, we lock a distinct TPCH."pseudo table" for read on a
     RowHash to prevent global deadlock for TPCH.a11. 
   2 ) Next, we lock TPCH.a11 for read. 
   3 ) We do an all-AMPs SUM step to aggregate from a single partition of
     TPCH.a11 with a condition of ("(TPCH.a11.day_id >= 1) AND
     (TPCH.a11.day_id <= 10)"), and the grouping identifier in field
      1027 .  Aggregate Intermediate Results are computed globally, then
     placed in Spool  3 .  The input table will not be cached in memory,
     but it is eligible for synchronized scanning.  The size of Spool  3 
     is estimated with no confidence to be  3 , 163  rows.  The estimated
     time for this step is  0 . 91  seconds. 
   4 ) We do an all-AMPs RETRIEVE step from Spool  3  (Last Use) by way of
     an all-rows scan with a condition of ("((Field_3 (INTEGER))<= 5)
     AND ((Field_3 (INTEGER))>= 1)") into Spool  1  (all_amps), which is
     built locally on the AMPs.  The size of Spool  1  is estimated with
     no confidence to be  3 , 163  rows.  The estimated time for this step
     is  0 . 04  seconds. 
   5 ) We execute the following steps in parallel. 
        1 ) We do an all-AMPs RETRIEVE step from Spool  1  (Last Use) by
          way of an all-rows scan with a condition of ("NOT (CUST_ID IS
          NULL)") into Spool  8  (all_amps), which is duplicated on all
          AMPs.  Then we do a SORT to order Spool  8  by row hash.  The
          size of Spool  8  is estimated with no confidence to be  6 , 326 
          rows.  The estimated time for this step is  0 . 07  seconds. 
        2 ) We do an all-AMPs RETRIEVE step from a single partition of
          TPCH.a11 with a condition of ("(TPCH.a11.day_id >= 1) AND
          ((TPCH.a11.day_id <= 10) AND (NOT (TPCH.a11.cust_id IS NULL
          )))") into Spool  9  (all_amps), which is built locally on the
          AMPs.  Then we do a SORT to order Spool  9  by row hash.  The
          result spool file will not be cached in memory.  The size of
          Spool  9  is estimated with no confidence to be  1 , 799 , 919  rows. 
          The estimated time for this step is  26 . 90  seconds. 
   6 ) We do an all-AMPs JOIN step from Spool  8  (Last Use) by way of a
     RowHash match scan, which is joined to Spool  9  (Last Use).  Spool
      8  and Spool  9  are joined using a merge join, with a join condition
     of ("cust_id = CUST_ID").  The result goes into Spool  7  (all_amps),
     which is built locally on the AMPs.  The result spool file will
     not be cached in memory.  The size of Spool  7  is estimated with no
     confidence to be  4 , 243 , 514  rows.  The estimated time for this step
     is  17 . 31  seconds. 
   7 ) We do an all-AMPs SUM step to aggregate from Spool  7  (Last Use) by
     way of an all-rows scan, and the grouping identifier in field  2 . 
     Aggregate Intermediate Results are computed globally, then placed
     in Spool  11 .  The size of Spool  11  is estimated with high
     confidence to be  1  row.  The estimated time for this step is  5 . 64 
     seconds. 
   8 ) We do an all-AMPs SUM step to aggregate from Spool  11  (Last Use)
     by way of an all-rows scan.  Aggregate Intermediate Results are
     computed globally, then placed in Spool  13 .  The size of Spool  13 
     is estimated with high confidence to be  1  row.  The estimated time
     for this step is  0 . 05  seconds. 
   9 ) We do an all-AMPs RETRIEVE step from Spool  13  (Last Use) by way of
     an all-rows scan into Spool  5  (group_amps), which is built locally
     on the AMPs.  The size of Spool  5  is estimated with high
     confidence to be  1  row.  The estimated time for this step is  0 . 03 
     seconds. 
  10 ) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool  5  are sent back to the user as the result of
     statement  1 .

        2 ) We do an all-AMPs RETRIEVE step from a single partition of
          TPCH.a11 with a condition of ("(TPCH.a11.day_id >= 1) AND
          ((TPCH.a11.day_id <= 10) AND (NOT (TPCH.a11.cust_id IS NULL
          )))") into Spool  9  (all_amps), which is built locally on the
          AMPs.  Then we do a SORT to order Spool  9  by row hash.  The
          result spool file will not be cached in memory.  The size of
          Spool  9  is estimated with no confidence to be  1 , 799 , 919  rows. 
          The estimated time for this step is  26 . 90  seconds.

Pass0 - Duration:  0 : 00 : 05 . 31 
select count(distinct a11.cust_id)  WJXBFS1
from dmtb_sales a11
	join (select a11.cust_id  cust_id
	from dmtb_sales a11
	where (a11.day_id between  1  and  10 
	 and a11.prod_id between  1  and  100 )
	group by a11.cust_id
	having count(a11.tran_id) between  1 . 0  and  5 . 0 
	) pa1
	  on  (a11.cust_id = pa1.cust_id)
where (a11.day_id between  1  and  10 
 and a11.prod_id between  1  and  100 )