Column parititioning - why does explain show an extra partition?

Database
Enthusiast

Column parititioning - why does explain show an extra partition?

I've just started playing around with column partitioning in R14. I created a table using a recent example that Paul Sinclair put into a recent Teradata Magazine article:

CREATE TABLE Sales_CP (

    TxnNo INTEGER,

    TxnDate DATE,

    ItemNo INTEGER,

    Quantity INTEGER )

  PARTITION BY COLUMN,

  UNIQUE INDEX (TxnNo);

When I do an insert/select into this table, the explain shows a merge into 4 column partitions.

  7) We do an all-AMPs MERGE into 4 column partitions of BARRY.sales_cp

     from Spool 1 (Last Use) with buffered output.  The size is

     estimated with low confidence to be 114,384 rows.  The estimated

     time for this step is 0.25 seconds.

This makes perfect sense since there are 4 columns in the table and each one is in it's own partition. However, when I do a "select *" from this table, a fifth partition shows up in the explain.

EXPLAIN SEL * FROM sales_cp;

  3) We do an all-AMPs RETRIEVE step from 5 column partitions of

     BARRY.sales_cp into Spool 1 (group_amps), which is built locally

     on the AMPs.  The size of Spool 1 is estimated with high

     confidence to be 19,978 rows (859,054 bytes).  The estimated time

     for this step is 0.13 seconds.

When I select one column from the table, the explain shows that 2 column partitions are involved:

EXPLAIN SEL txnno FROM sales_cp;

  3) We do an all-AMPs RETRIEVE step from 2 column partitions of

     BARRY.sales_cp into Spool 1 (group_amps), which is built locally

     on the AMPs.  The size of Spool 1 is estimated with high

     confidence to be 19,978 rows (499,450 bytes).  The estimated time

     for this step is 0.08 seconds.

Can anyone explain the existence of this extra partition?

Thanks,

Barry

Tags (3)
1 REPLY
Enthusiast

Re: Column parititioning - why does explain show an extra partition?

I guess I should have read the Orange Book first. This is from the Orange Book:

Note that the count of 27 for the number of column partitions includes the 26 user-specified column partitions of the CP table and the delete column partition.

Apparently, the select is also accessing the "delete" column partition.