Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-25-2012
06:39 AM

07-25-2012
06:39 AM

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

1 REPLY

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-25-2012
07:50 AM

07-25-2012
07:50 AM

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.