
Archives of the TeradataForumMessage Posted: Thu, 31 Mar 2005 @ 21:49:59 GMT
Hello all! Ok, I'll explain it another (more mundane) way. I was tracking down a product join which has been occurring for several users. There are two tables with a nontrivial number of rows (The account table has 457,602,531, the other has 434,404,251). We have these partitioned by the expiration date of the record, so at first I was worried that the partioning wasn't being used. That was not the case. When I reduce the query to just dealing with the account table, removing grouping, and just making it plain simple, it looks like this, with the corresponding explain (you didn't really want me to put the whole thing in there, right? Isn't this enough?) select a.C2t_Acct_Ky, a.C2t_Acct_Opn_Dt, a.C2t_Acct_Prod_Ky from T_C2T_ACCT a where a.C2t_Acct_Ky_Expir_Dt = '99991231' and a.C2t_Acct_Opn_Dt between '20050201' and '20050228' and a.c2t_acct_con_acct_flg = 1 and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR' and a.C2t_Acct_Stat in ('NEW', 'REENROLLED') and a.C2t_Acct_Bm_Bra_Id = 725 2) Next, we do an allAMPs RETRIEVE step from a single partition of DTNG_C2T.T_C2T_ACCT with a condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") with a residual condition of ( "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt <= DATE '20050228') AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt >= DATE '20050201'))))))") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with low confidence to be 14 rows. The estimated time for this step is 1 minute and 46 seconds. If I change the query to just look at the partition, the confidence changes to high and it has a wonderful (if large) estimate. select a.C2t_Acct_Ky, a.C2t_Acct_Opn_Dt, a.C2t_Acct_Prod_Ky from T_C2T_ACCT a where a.C2t_Acct_Ky_Expir_Dt = '99991231' 2) Next, we do an allAMPs RETRIEVE step from a single partition of DTNG_C2T.T_C2T_ACCT with a condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") with a residual condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with high confidence to be 90,061,104 rows. The estimated time for this step is 2 minutes. If I comment out the date check, while the confidence drops to low, it still estimates well enough that if I used these conditions in my original query, it uses a different plan. You'll notice that the explain here estimates around 100,000 rows coming back. select a.C2t_Acct_Ky, a.C2t_Acct_Opn_Dt, a.C2t_Acct_Prod_Ky from T_C2T_ACCT a where a.C2t_Acct_Ky_Expir_Dt = '99991231' /* and a.C2t_Acct_Opn_Dt between '20050201' and '20050228' */ and a.c2t_acct_con_acct_flg = 1 and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR' and a.C2t_Acct_Stat in ('NEW', 'REENROLLED') and a.C2t_Acct_Bm_Bra_Id = 725 2) Next, we do an allAMPs RETRIEVE step from a single partition of DTNG_C2T.T_C2T_ACCT with a condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") with a residual condition of ( "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE = '99991231'))))") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with low confidence to be 905,264 rows. The estimated time for this step is 1 minute and 46 seconds. The actual number of rows (replacing the columns selected with a count(*) is 1,728,378. While it's an order of magnitude off, it's still in the ballpark enough that on the more complex query, the optimizer tries to cut down the number of rows by first doing other stuff (joining to other tables) before doing the product join. If I change the date condition to select one day out of the month, the following plan is produced: select a.C2t_Acct_Ky, a.C2t_Acct_Opn_Dt, a.C2t_Acct_Prod_Ky from T_C2T_ACCT a where a.C2t_Acct_Ky_Expir_Dt = '99991231' and a.C2t_Acct_Opn_Dt = /* between */ '20050201' /* and '20050228' */ and a.c2t_acct_con_acct_flg = 1 and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR' and a.C2t_Acct_Stat in ('NEW', 'REENROLLED') and a.C2t_Acct_Bm_Bra_Id = 725 2) Next, we do an allAMPs RETRIEVE step from a single partition of DTNG_C2T.T_C2T_ACCT with a condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") with a residual condition of ( "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt = DATE '20050201') AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE = '99991231')))))") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with low confidence to be 761 rows. The estimated time for this step is 1 minute and 46 seconds. Well, this is also low confidence, but close to the actual figure of 3,039 rows. However, with the between, which by any shred of common sense should have more rows, the explain gets a little weird. Instead of having MORE rows in the estimate (a whole month probably having more records than a single day), it has less! select a.C2t_Acct_Ky, a.C2t_Acct_Opn_Dt, a.C2t_Acct_Prod_Ky from T_C2T_ACCT a where a.C2t_Acct_Ky_Expir_Dt = '99991231' and a.C2t_Acct_Opn_Dt between '20050201' and '20050228' and a.c2t_acct_con_acct_flg = 1 and a.C2t_Crted_Src_Acct_Sys = 'BILLMGR' and a.C2t_Acct_Stat in ('NEW', 'REENROLLED') and a.C2t_Acct_Bm_Bra_Id = 725 2) Next, we do an allAMPs RETRIEVE step from a single partition of DTNG_C2T.T_C2T_ACCT with a condition of ( "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231'") with a residual condition of ( "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Bm_Bra_Id = 725) AND (((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'NEW') OR (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Stat = 'REENROLLED')) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Crted_Src_Acct_Sys = 'BILLMGR') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Con_Acct_Flg = 1) AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '99991231') AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt <= DATE '20050228') AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt >= DATE '20050201'))))))") into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with low confidence to be 14 rows. The estimated time for this step is 1 minute and 46 seconds. With the one day, the estimate is ~700 rows, instead of the actual ~3,000. But the actual number of rows that result are 62,934, as opposed to the estimate of fourteen (14!). Hey, if I thought I was going to end up with 14 rows, I might do a product join immediately, but hey, even I know that if you have 700 rows for one day of a month, you might estimate 28*700 (or 19,600) rows. I know, I know, the optimizer doesn't know about "months" and "days", but it does have complete (important for a date, since sample stats do funny things) and recent statistics: help statistics T_C2T_ACCT column C2t_Acct_Opn_Dt Date Time Number of Rows Number of Nulls Number of Intervals Number of Uniques Numeric Sampled Sampled Percent Version Min Value Mode Value Mode Frequency Max Value Mode Value Mode Frequency ... 05/03/28 13:01:34 457602531 5910338 100 22859 Y 0 0 2 101 960810 1886902 = 760316 101 421324 ... I didn't put the entire results out, but then again, I don't know how to read all those anyway. But you can see that the stats aren't sampled. There is a goodly population of null values (5,910,338) but not compared to the number of rows in the table (457,602,531 or 1.3%). Now, obviously, I should use some other method to join these two tables, especially if it occurs often. However, my question still stands. Why is the estimate for a whole set of values (the between) WORSE than the equality condition? What's going on. And that's the start of my story. iv
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 15 Jun 2023  