Teradata分区主索引相关技术咨询及实操疑问

First, let's recap your table creation statement to set the context:

CREATE TABLE customerservice.ordertbl_new (
    ORDER_ID integer NOT NULL,
    CUST_ID integer NOT NULL,
    ORDER_DATE date,
    ORDER_AMOUNT integer
) PRIMARY INDEX (CUST_ID)
PARTITION BY case_n(
    ORDER_AMOUNT < 10000,
    ORDER_AMOUNT < 20000,
    ORDER_AMOUNT < 30000,
    NO case OR UNKNOWN
);

Key Breakdown of Your PPI Setup #

• Primary Index (PI): CUST_ID is your hash PI, which determines how rows are distributed across Teradata AMPs. Rows with the same CUST_ID will land on the same AMP.
• Partitioning Logic: You're using case_n() (a Teradata-specific conditional partitioning function) to split data into 4 logical partitions within each AMP:
  1. Partition 1: Rows where ORDER_AMOUNT < 10000
  2. Partition 2: Rows where 10000 ≤ ORDER_AMOUNT < 20000 (since conditions are evaluated top-to-bottom, non-matching rows fall to the next case)
  3. Partition 3: Rows where 20000 ≤ ORDER_AMOUNT < 30000
  4. Partition 4: All remaining rows (including ORDER_AMOUNT ≥ 30000, ORDER_AMOUNT IS NULL, or any unhandled edge cases)

Common Q&A for Your PPI Table #

1. Where do my inserted rows land?

Looking at your first insert:

insert into customerservice.ordertbl_new values(10001,2,'2012-01-24',548);

This row has ORDER_AMOUNT=548, so it falls into Partition 1. For any subsequent inserts:

• Rows with ORDER_AMOUNT between 10000 and 19999 go to Partition 2
• Rows with ORDER_AMOUNT between 20000 and 29999 go to Partition 3
• Rows with ORDER_AMOUNT ≥30000 or NULL go to Partition 4

2. How does PPI improve query performance?

PPI enables partition elimination: when your query filters on ORDER_AMOUNT, Teradata only scans the relevant partitions instead of the entire table. For example:

SELECT * FROM customerservice.ordertbl_new WHERE ORDER_AMOUNT < 10000;

This query will only access Partition 1 on each AMP, drastically reducing I/O compared to a non-partitioned table.

Even better, combining the PI and PPI filters (e.g., WHERE CUST_ID=2 AND ORDER_AMOUNT <10000) will:

1. Use the hash PI to target only the AMP holding CUST_ID=2
2. Within that AMP, only scan Partition 1
   This is the most efficient access path for your table.

3. How do I check partition details and data distribution?

Use these commands to inspect your partitions:

• Get a quick overview of partition definitions:

  HELP PARTITION customerservice.ordertbl_new;
  

• Query system tables to see row counts per partition:

  SELECT 
      PartitionNumber,
      PartitionName,
      RowCount
  FROM DBC.PartitionRowCountsV
  WHERE DatabaseName = 'customerservice' 
    AND TableName = 'ordertbl_new';
  

4. What maintenance steps should I take for this PPI table?

• Collect Statistics: Always update stats on both the PI and partition key to help the optimizer make optimal decisions:

  COLLECT STATISTICS ON customerservice.ordertbl_new COLUMN (CUST_ID, ORDER_AMOUNT);
  

• Monitor Partition Skew: If one partition (e.g., Partition 4) grows significantly larger than others, consider adjusting your partitioning logic (e.g., split the upper range into smaller buckets) to avoid performance bottlenecks.
• Avoid Over-Partitioning: While PPI helps, too many partitions can increase AMP overhead. Your current 4-partition setup is reasonable for this range-based logic.

5. Can I modify the partitioning scheme later?

Yes, you can alter the partition definition using ALTER TABLE...ADD PARTITION or ALTER TABLE...DROP PARTITION, but note that this can be resource-intensive for large tables. Plan such changes during maintenance windows.

内容的提问来源于stack exchange，提问作者tonyibm