- Each Table in Teradata has a Primary Index unless it is a NoPI table.<\/li>
- The Primary Index is the mechanism that allows Teradata to physically distribute the rows of a table across the AMPs.<\/li>
- AMPs Sort their rows by the Row-ID, so the system can perform a lightning fast Binary Search since the rows are in Row-ID Order.<\/li>
- Partitioning merely tells the AMP to sort its tables\u2019 rows by the Partition first, but then sort the rows by Row-ID within the partition.<\/li>
- Partitioning queries will involve all AMPs, but partitioned tables are designed to prevent FULL Table Scans.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
Advantages and Disadvantages of Partitioning<\/h2>\n\n\n\n
Advantage<\/strong>s<\/p>\n\n\n\n
- Increase query efficiency by avoiding full table scans<\/li>
- PPI\u2019s are defined on a table in order to increase query efficiency by avoiding full table scans without the overhead and maintenance costs of secondary indexes.<\/li>
- Deleting large volumes of rows in entire partitions can be extremely fast.<\/li>
- For range based queries we can effectively remove SI and use PPI, thus saving overhead of SI subtable.<\/li>
- It can access a subset of a large table quickly.<\/li>
- Maximum partitions allowed by Teradata \u2013 65,535<\/strong><\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
Disadvantages<\/strong><\/p>\n\n\n\n
- Partitioned rows are2 or 8 bytes longer. Table uses more PERM space.<\/li>
- A PI access may be degraded if the partitioning column is not part of the PI<\/li>
- Joins to non-partitioned tables with the same PI may be degraded.<\/li>
- The PI can\u2019t be defined as unique when the partitioning column is not part of the PI.<\/li><\/ul>\n\n\n\n
<\/p>\n\n\n\n
Creating a PPI Table with Simple Partitioning<\/h2>\n\n\n\n
- A PPI table has the AMPs sort (order) the rows on the table by the Partition. <\/li>
- This allows for people to avoid Full Table Scans.<\/li><\/ul>\n\n\n\n
A Visual Display of Simple Partitioning<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-33.png\")
<\/figure>\n\n\n\nAn SQL Example that explains Simple Partitioning<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-34.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Creating a PPI Table with RANGE_N <\/h2>\n\n\n\n
- The expression is evaluated and is mapped into one of a list of specified ranges.<\/li>
- Ranges are listed in increasing order and must not overlap with each other.<\/li>
- Result is the data row being placed into a partition associated with that range.<\/li><\/ul>\n\n\n\n
Examples:<\/h3>\n\n\n\n
Example 1 – Creating a PPI Table with RANGE_N Partitioning per Month<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-35.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-36.png\")
Above Example sort the rows on each AMP by order_date and create a different partition every month<\/figcaption><\/figure>\n\n\n\n A Visual of One Year of Data with Range_N per Month<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-38.png\")
<\/figure>\n\n\n\nEach AMP sorts their rows by Month (of Order_Date).<\/p>\n\n\n\n
An SQL Example explaining Range_N Partitioning per Month<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-39.png\")
The above query wants to see orders in the first quarter, so each AMP reads 3 partitions.<\/figcaption><\/figure>\n\n\n\n Example 2<\/strong> – Creating a PPI Table with RANGE_N Partitioning per Day<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-40.png\")
Above Example sort the rows on each AMP by order_date and create a different partition every Day<\/figcaption><\/figure>\n\n\n\n A Visual of Range_N Partitioning Per Day<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-41.png\")
There would be 365 partitions in total for an entire year of Partitioning per Day. Each AMP holds the orders assigned to them in daily partitions on the day of the order date.<\/figcaption><\/figure>\n\n\n\n An SQL Example that explains Range_N Partitioning per Day<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-42.png\")
<\/figure>\n\n\n\n<\/p>\n\n\n\n
Creating a PPI Table with CASE_N<\/h2>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-44.png\")
If an Order_Total is < 1000, it will go into Partition 1. If an Order_Total is between 1000 and 4999.99, it will go in Partition 2. The NO Case partition is for anything falling through and the UNKNOWN partition is for NULL values in the Total.<\/figcaption><\/figure>\n\n\n\n A Visual of Case_N Partitioning<\/strong><\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-45.png\")
There are six partitions for this table. Orders go into partitions based on Order_Total.<\/figcaption><\/figure>\n\n\n\n An SQL Example that explains CASE_N Partitioning<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.devopsschool.com\/blog\/wp-content\/uploads\/2022\/03\/image-46.png\")
All AMPs retrieve, but each only reads partition 5 <\/figcaption><\/figure>\n\n\n\n Note: <\/strong>NO CASE, NO RANGE, and UNKNOWN options are also available which we can use in RANGE_N and CASE_N Partitioning<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
What is the Concept behind Partitioning a Table? Each Table in Teradata has a Primary Index unless it is a NoPI table. The Primary Index is the mechanism that allows Teradata to physically distribute the rows of a table across the AMPs. AMPs Sort their rows by the Row-ID, so the system can perform a lightning fast Binary Search since the rows…<\/p>\n","protected":false},"author":48,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-28542","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28542","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/48"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=28542"}],"version-history":[{"count":2,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28542\/revisions"}],"predecessor-version":[{"id":30967,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/28542\/revisions\/30967"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=28542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=28542"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=28542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}