 |
 |
Archives of the TeradataForum
Message Posted: Thu, 15 Apr 2004 @ 12:17:37 GMT
| Subj: | | Re: Spool space error |
| |
| From: | | Claybourne Barrineau |
Rohit,
Make sure you are using 'Union All' instead of 'Union'. Also, if you are on V2R5, you might want to consider 'Horizontal Partitioning' via the
use of check constraints in the Date column definitions. This will require you to create a view which performs a 'Union All' of your multiple
year based fact tables. Like PPI, however, I doubt if the optimizer will make use of the horizontal partitioning if you select your date range
from another table (such as a join to a Calendar table.)
Hope this helps,
Clay
Create Table Test_Space.HP_CLB_2001
(DateField date check (DateField between 1010101 and 1011231));
Insert into Test_Space.HP_CLB_2001 Values (1010101);
Insert into Test_Space.HP_CLB_2001 Values (1010102);
Insert into Test_Space.HP_CLB_2001 Values (1010103);
Insert into Test_Space.HP_CLB_2001 Values (1010104);
Insert into Test_Space.HP_CLB_2001 Values (1010105);
Collect Stats on Test_Space.HP_CLB_2001 Index (DateField);
Create Table Test_Space.HP_CLB_2002
(DateField date check (DateField between 1020101 and 1021231));
Insert into Test_Space.HP_CLB_2002 Values (1020101);
Insert into Test_Space.HP_CLB_2002 Values (1020102);
Insert into Test_Space.HP_CLB_2002 Values (1020103);
Insert into Test_Space.HP_CLB_2002 Values (1020104);
Insert into Test_Space.HP_CLB_2002 Values (1020105);
Collect Stats on Test_Space.HP_CLB_2002 Index (DateField);
Create View Test_Space.HP_CLB
(
DateField
)
as
(
Select DateField
From Test_Space.HP_CLB_2001
Union All
Select DateField
From Test_Space.HP_CLB_2002
);
explain
Select *
From Test_Space.HP_CLB
Where DateField between 1010101 and 1011231;
-- Note, no reference to Test_Space.HP_CLB_2002 in the explain
| | | | |
| | 1) | First, we lock a distinct Test_Space."pseudo table" for read on a RowHash to prevent global deadlock for Test_Space.HP_CLB_2001.
| |
| | 2) | Next, we lock Test_Space.HP_CLB_2001 for read.
| |
| | 3) | We do an all-AMPs RETRIEVE step from Test_Space.HP_CLB_2001 by way of an all-rows scan with a condition of
("(Test_Space.HP_CLB_2001.DateField <= DATE '2001-12-31') AND (Test_Space.HP_CLB_2001.DateField >= DATE '2001-01-01')") into Spool 1 (all_amps),
which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 5 rows. The estimated time for this step is 0.03
seconds.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan with a condition of ("(DATEFIELD <= DATE '2001-12-31')
AND (DATEFIELD >= DATE '2001-01-01')") into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated with high
confidence to be 5 rows. The estimated time for this step is 0.03 seconds.
| |
| | 5) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 2 are sent back to the user as the result of statement 1. The total estimated time is 0.06 seconds.
| |
| |
