 |
 |
Archives of the TeradataForum
Message Posted: Tue, 17 Jun 2003 @ 13:57:11 GMT
| Subj: | | Performance Analyze |
| |
| From: | | Villeneuve Philippe |
Hello,
Sorry, I am new to Teradata and I would like to know i the following request is good or not on big tables (~ 10 000 000 rows) (I can't
test for the moment, I still don't have the production datas) :
select
DATE_ARRT_INFO,CODE_GUIC,CODE_ORGN_FINN,REFR_PRDT,NUMR_ENTT_TITL,....
from DW_SUIV_PRDT_COLT where
DATE_ARRT_INFO >=(select ('01/06/2003' (date, format 'DD/MM/YYYY')) -
INTERVAL '6' MONTH )
Primary index of this table :
DATE_ARRT_INFO, CODE_ORGN_FINN, CODE_GUIC, CODE_PRDT_SERV, REFR_PRDT
explain :
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct DBRVCE1."pseudo table" for read on a RowHash to prevent global deadlock for DBRVCE1.DW_SUIV_PRDT_COLT.
| |
| | 2) | Next, we lock DBRVCE1.DW_SUIV_PRDT_COLT for read.
| |
| | 3) | We do an INSERT into Spool 3.
| |
| | 4) | We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 2, which is built locally on the AMPs.
The size of Spool 2 is estimated with high confidence to be 1 row. The estimated time for this step is 0.17 seconds.
| |
| | 5) | We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 5, which is duplicated on all AMPs.
The size of Spool 5 is estimated with high confidence to be 28 rows. The estimated time for this step is 0.17 seconds.
| |
| | 6) | We do an all-AMPs JOIN step from DBRVCE1.DW_SUIV_PRDT_COLT by way of an all-rows scan with no residual conditions, which is joined to
Spool 5 (Last Use). DBRVCE1.DW_SUIV_PRDT_COLT and Spool 5 are joined using a product join, with a join condition of
("DBRVCE1.DW_SUIV_PRDT_COLT.DATE_ARRT_INFO >= Field_2"). The input table DBRVCE1.DW_SUIV_PRDT_COLT will not be cached in memory, but it is
eligible for synchronized scanning. The result goes into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated
with no confidence to be 1,696,838 to 5,090,512 rows. The estimated time for this step is 1 hour and 37 minutes.
| |
| | 7) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | The contents of Spool 1 are sent back to the user as the result of statement 1.
| |
Have I to add a secondy index on the DATE_ARRT_INFO field?
Thanks in advance for your help
Bests regards,
Philippe VILLENEUVE
| |
