Archives of the TeradataForum

Message Posted: Mon, 29 Mar 2005 @ 01:36:53 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Once upon a time there lived a table. This table, while large, did not normally SQueeL. It was a beautiful table, for it's size and looked like this:

     CREATE SET TABLE DTNG_C2T.T_C2T_ACCT  (
           C2t_Acct_Ky DECIMAL(15,0) NOT NULL,
           C2t_Acct_Ky_Eff_Dt DATE COMPRESS (DATE '2003-09-22',DATE '2003-09-29'...DATE '2003-09-08'), - dates removed to protect the innocent
           ... - other glorious columns
           C2t_Acct_Opn_Dt DATE FORMAT 'yyyy-mm-dd' COMPRESS (DATE '2003-04-01',...DATE '2002-09-27'),
           ...) - even more glorious columns
     PRIMARY INDEX ( C2t_Acct_Ky )
     PARTITION BY CASE_N(
     C2t_Acct_Ky_Expir_Dt =  '9999-12-31',

And it had it's little statistics all in a row, with good, sampled statistics on the ky, and complete statistics on the dates. It new how many records it had in it's sincere main partition:

     select * from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'

as it would EXPLAIN to anyone who asked:

     2) Next, we do an all-AMPs 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 '9999-12-31'")
        with a residual condition of (
        "DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31'")
        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 result spool file will not be
        cached in memory.  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 and 56 seconds.

And it knew that when you asked it about some of it's other columns, while it didn't know for certain, it certainly did know a little:

     select * from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       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 all-AMPs 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 '9999-12-31'")
          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 '9999-12-31'))))")
          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 47 seconds.

One day, a dazed and confused DBA came upon this table while searching for a dangerous and ugly Product Join. He spied this table dancing with the nasty product join, and he pounced upon the cute table in an effort to hunt down and kill the dangerous product join. He asked the table two simple questions. The first was very simple.

     select * from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       and a.C2t_Acct_Opn_Dt = '2005-02-01'

       2) Next, we do an all-AMPs 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 '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt = DATE '2005-02-01') AND
          (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31')")
          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 2,404 rows.  The estimated time for this step
          is 1 minute and 46 seconds.

The second was not as simple, but still, pretty easy:

     select * from T_C2T_ACCT a
      where a.C2t_Acct_Ky_Expir_Dt = '9999-12-31'
       and C2t_Acct_Opn_Dt BETWEEN '2005-02-01' and '2005-02-28'

       2) Next, we do an all-AMPs 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 '9999-12-31'")
          with a residual condition of (
          "(DTNG_C2T.T_C2T_ACCT.C2t_Acct_Ky_Expir_Dt = DATE '9999-12-31')
          AND ((DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt <= DATE '2005-02-28')
          AND (DTNG_C2T.T_C2T_ACCT.C2t_Acct_Opn_Dt >= DATE '2005-02-01'))")
          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 43 rows.  The estimated time for this step is
          1 minute and 46 seconds.

He sprang up in surprise and cried "Why, table, why do you think you would have more rows for February 1st than you would for the entire month of February? Are you just being mean to me? Does it have anything to do with the reconfig we did last Friday? But your beautiful statistics were collected after said reconfig? Why do you torment me so by participating with the ugly Product Join? I know it's because you think there are only 43 rows in February, with low confidence, but that makes no sense?" His cries of anguish could be heard throughout the kingdom. And the table answered, "I don't know!"

Argh.

iv

	<Prev	Next>		<<First	<Prev	Next>	Last>>