単一パーティション スキャンおよびBEGIN/END範囲関数 - Advanced SQL Engine - Teradata Database

パーティション化されたテーブルに対する単一パーティションBEGINまたはEND範囲関数スキャンの例

次の2つのテーブルを定義するとします。

CREATE SET TABLE t11 (
  a INTEGER,
  b PERIOD(DATE))
PRIMARY INDEX(a)
PARTITION BY CAST ((BEGIN(b)) AS INTEGER);
CREATE SET TABLE t12 (
  a INTEGER,
  b PERIOD(DATE))
PRIMARY INDEX(a)
PARTITION BY CAST((END(b)) AS INTEGER);

関連するEXPLAIN句のテキストは、後に示すレポートの例で太字で強調表示されています。

EXPLAIN
SELECT *
FROM t11
WHERE BEGIN(b)=DATE '2005-02-03';

...
3) We do an all-AMPs RETRIEVE step  from a single partition  of
   df2.t11 with a condition of  ("df2.t11.b = PERIOD 
   (DATE '2005-02-03',  DATE '2005-02-03'")
   with a residual condition of ("(BEGIN(df2.t11.b ))
   = DATE '2005-02-03'") into Spool 1 (group_amps), which is built 
   locally on the AMPs. The size of Spool 1 is estimated with no 
   confidenceto be 1 row (56 bytes). The estimated time for this step 
   is 0.03seconds.
...

次のSELECTリクエストは、全行、全パーティション スキャンを使用して行にアクセスします。述部およびパーティション式が異なる範囲で定義されているためです。

EXPLAIN
SELECT *
FROM t11
WHERE END(b)=DATE '2005-02-03';

...
3) We do an all-AMPs RETRIEVE step from df2.t11  by way of an 
    all-rows scan  with a condition of  ("(END(df2.t11.b ))= DATE 
    '2005-02-03'")  into Spool 1 (group_amps), which is built 
   locally on the AMPs. The size of Spool 1 is estimated with no 
   confidence to be 1 row (56 bytes). The estimated time for this step 
   is 0.03 seconds.
...

次のSELECTリクエストは、単一パーティション スキャンを使用して行にアクセスします。

EXPLAIN
SELECT *
FROM t12
WHERE END(b)=DATE '2006-02-03';

...
3) We do an all-AMPs RETRIEVE step  from a single partition of 
    df2.t12  with a condition of  ("df2.t12.b = PERIOD 
   (DATE '2006-02-03'- INTERVAL '1' DAY, DATE '2006-02-03')")  
   with a residual condition of  ("(END(df2.t12.b ))= DATE  
   '2006-02-03'")  into Spool 1 (group_amps), which is built locally 
   on the AMPs. The size of Spool 1 is estimated with no confidence to
   be 1 row (56 bytes). The estimated time for this step is 0.03 
   seconds.
...

次のSELECTリクエストは、単一パーティション スキャンを使用して行にアクセスします。

EXPLAIN
SELECT *
FROM t12
WHERE a=1
AND   END (b)=DATE '2006-02-03';

1) First, we do a single-AMP RETRIEVE step  from a single partition of 
    df2.t12  by way of the primary index "df2.t12.a = 1,
   df2.t12.b = PERIOD (DATE '2006-02-03'-INTERVAL '1' 
   DAY, DATE '2006-02-03')" with a residual condition of 
   ("((END(df2.t12.b )) = DATE '2006-02-03') AND (df2.t12.a = 1)")  
   into Spool 1 (one-amp), which is built locally on that AMP. 
   The size of Spool 1 is estimated with low confidence to be 1 row 
   (56 bytes). The estimated time for this step is 0.02 seconds.
...