DECLARE GLOBAL TEMPORARY TABLE Examples
1. Use a subselect to create a temporary table containing the names and employee numbers of the highest-rated employees.
DECLARE GLOBAL TEMPORARY TABLE
emps_to_promote
AS SELECT emp_name, emp_no FROM employee
WHERE emp_rating >= 9
ON COMMIT PRESERVE ROWS
WITH NORECOVERY, LOCATION=(location1);
2. Create a global temporary table and then insert the names in blocks where the rating is only a single value.
The final select can be run many times as part of a large reporting SQL.
When inserting a large number of records, the min/max indexing will work more efficiently if all the rating values are in blocks. If the rating ranges 1 to 3 appear in all blocks, the scanner needs to select from all blocks; if all the values are the same in a block, the algebra required to return the required results is simple and quicker.
Only the blocks at boundaries will have more than one rating value. Always consider that the load will be performed once and the data may be read many times. (This applies to real tables as well as temporary tables.) Always attempt to load data in the order of the most used retrieval criteria.
DECLARE GLOBAL TEMPORARY TABLE YYMD00(
customer_id DECIMAL(18, 0),
customer_name VARCHAR(20),
customer_rating INTEGER1,
YYDATAF FLOAT)
ON COMMIT PRESERVE ROWS WITH NORECOVERY;
INSERT INTO YYMD00 SELECT emp_no, emp_name, 10, 2014 FROM employee WHERE emp_rating = 1;
INSERT INTO YYMD00 SELECT emp_no, emp_name, 20, 2014 FROM employee WHERE emp_rating = 2;
INSERT INTO YYMD00 SELECT emp_no, emp_name, 30, 2014 FROM employee WHERE emp_rating = 3;
SELECT customer_id, customer_name FROM YYMD00 WHERE customer_rating = 20;