How to use partitioned primary index (PPI) Part 1

How to use partitioned primary index (PPI)

This post is about row partitioning and will not discuss columnar.

What is partitioning?

To explain it correctly, let's get back to the basics...
Each Teradata tables (except NoPI type) have "Primary Index", aka. PI, which is not physical index, but rather a logical construction: one or more columns of the table which give the input for hashing method. The hash value determines two things:

• Which AMP will store the record
• Storing order of the records within the AMPs

If the PI is non-partitioned then the records are stored in order of hash value of PI.

If you use (row) partitioning, you define it at the Primary Index.
In this case Teradata will associate a 2bytes or 2/8 bytes (at V14.10) "partition code" to the record*, and the storing order is <partition code>,<hash_value> (aka. RowKey).
That way partitions are not sub-tables or other physical objects, but only influence the record storing order.

* This implies that no more than 64k(2bytes)/9Q(8bytes) partitions can exist. For details read the appropriate Teradata version's documentation.

What is the difference between PPI and NUSI?

NUSI (Non Unique Secondary Index) can serve as similar purposes, but is absolutely different.
NUSI is a separate subtable, with analogue PI to base table, but different (value) ordering.
For details please read Teradata documentation.

How to define?

Non partitioned table:
create table tablePI
(
  Trx_id Integer
, Trx_dt Date
)
PRIMARY INDEX (Trx_id)

Partitioned table:
create table tablePPI (   Trx_id Integer
, Trx_dt Date
)
PRIMARY INDEX (Trx_id, Trx_dt**)
PARTITION BY RANGE_N(Trx_dt BETWEEN DATE '2010-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN) 

Highlights

• **Partitioning key (Trx_dt here) can be part of the PI or not. This is very important, see below.
• Partitioning can be single or multiple (MLPPI) levels***
• RANGE_N or CASE_N functions can be used for determining partition code
• RANGE_N function has constant interval endpoints and partition length.
• NO RANGE and UNKNOWN partitions will store the out-of-intervals and null value records respectively

***MLPPI is a technique when multiple or nested partitioning is defined on the table. Logically it looks like sub-partitions, but in practice it only influences the calculation of partition code values, which is still a linear 2/8 bytes value overall the table.

Pros - Cons of using PPI

PPI is a very useful feature, but not a silver bullet to use it everywhere. Look the trade offs:

• (+) Partition elimination
  Only the relevant partitions are scanned while accessing data
• (+) Interval filtering is supported
• (+) Accelerates INSERTs
  If we load increment data into a populated table. Very likely less data blocks are affected, since few partitions are involved (if date is the partitioning basis) 
• (-) 2 or 8 bytes extra space allocation per record
• (-) Compression is not allowed on PartKey column
• (-) PartKey inclusion problem (see below)
• (-) Partition elimination works only with literals
  Subselects cause full table scans

Design aspects

RANGE_N or CASE_N

These functions are used to define partitioning. RANGE_N is for concentrate date (integer) intervals into partitions, while CASE_N is like a CASE-WHEN-THEN expression, where the outcome is the partition.

Typically RANGE_N is used when we partition a transaction table by its date or timestamp, while CASE_N is popular in special cases like categorizing. You can use more columns in the logical expression, but take care, all of them must be used in filter condition to enable partition elimination.

RANGE_N: what interval size?

It depends on the granularity of the data, granularity of filtering and how long interval should be stored in the table. Usually daily partitioning is ideal.

RANGE_N: interval extension or intervals in advance?

If we load transactional data into our partitioned table, the date column we use as partition key is populated later and later dates, while we have a finite partition range definition.

Partition ranges can be added to RANGE_N definition periodically (depends on version), or we can define partitions in far advance. (365 partitions required for a year, 65k partitions cover ~ 180years, which is more than enough) Note that empty partitions do not allocate space.

One of the methods above should be applied, otherwise the NO RANGE partition will grow extensively, which will cause performance degradation due to less effective partition elimination.

Partitioning Key: include in PI or not?

This is the funny point.
Partitioning key is the column(s) that determines the partition, say used in the RANGE_N/CASE_N definition. We can include it in the Primary Index or not, we decide.

Let's take an example. We have a master-detail pair of tables, nicely "equi-PI"-ed for effective join:

CREATE TABLE ORDER_HEAD
(
  ORDER_NO INTEGER
, ORDER_DT DATE
) UNIQUE PRIMARY INDEX (ORDER_NO);

CREATE TABLE ORDER_ITEM
(
  ORDER_NO INTEGER
, ORDER_ITEM_NO
, PROD_NO INTEGER
) PRIMARY INDEX (ORDER_NO);

We modify ORDER_HEAD's PI:
UNIQUE PRIMARY INDEX (ORDER_NO, ORDER_DT)

PARTITION BY RANGE_N(ORDER_DT BETWEEN DATE '2010-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY , NO RANGE, UNKNOWN)




Should we include ORDER_DT or not? Which is better, what is the difference?

• Not include
  ORDER_HEAD and ORDER_ITEM tables will have similar AMP distribution, but different physical order within the AMPs.
  Each join operation requires sort of the selected ORDER_HEAD records in spool, or ORDER_ITEMS table will be merge joined against each selected non empty partitions of ORDER_HEAD sequentially (called sliding-window merge join)
• Include
  ORDER_HEAD and ORDER_ITEM tables will have different AMP distribution, each join operation requires redistribution.Why do we not use the same PI at ORDER_ITEM? Because we do not have that column there.

Neither of the above is acceptable in many cases. What should we do? In this case I would copy the ORDER_DT to the ORDER_ITEM table also, and use the same "Included" version of PI. Requires some more space, logic in load time, but great gain while accessing data.

Use cases

Filtering

This select will eliminate all partitions except those three:

select * from ORDER_HEAD where order_dt between '2013-12-12' (date) and '2013-12-14' (date);

This select will generate all rows scan:

select * from ORDER_HEAD where cast( order_dt as char(7)) = '2013-12';

This select will generate all rows scan* either (sub-query):

select * from ORDER_HEAD  where order_dt in (select max(calendar_date) from sys_calendar.calendar  where year_of_calendar=2013 and month_of_year=5);

Why? Optimizer has to determine which partitions to be accessed in time of generating execution plan. That time it cannot know what is the result of the subquery. That is it.

* I got a proper comment on this option to double check. Yes, right, this information is a out-of-date. With actual versions of Teradata (V13.10..V14.10) I experienced 3 different results:

• Full scan
  Eg. sub-query contains a "group by"
• Dynamic partition elimination
  Sub-query is simple, indicates "enhanced by dynamic partition elimination" section in the plan
• Plan-time partititon elimination
  Literal condition or very simple sub query. Parsing time evaluation enables PO to determine which partitions to be scanned.  Plan: "...We do an all-AMPs ... step from 3 partitions of...". Do not really know exactly what decides between full scan, dynamic- or plan-time elimination... Explanations welcome.

Join

We join two tables: T1 and T2. The table shows what happens if they are partitioned, not partitioned and the partitioning key is included or not in the PI:

T2 T1	PI:(a)	PI:(a) PART(b)	PI:(a,b) PART(b)
PI:(a)	Join: T1.a=T2.a RowHash match
PI:(a) PART(b)	Join: T1.a=T2.a T1 sorted by hash(a) or Sliding-window MJ	Join: T1.a=T2.a T1&T2 sorted by hash(a) or Sliding-window MJ (NxM combinations) Join: T1.a=T2.a and T1.b=T2.b T1&T2 sorted by RowKey RowKey based MJ
PI:(a,b) PART(b)	Join: T1.a=T2.a T1 Redistributed & sorted by hash(a)	Join: T1.a=T2.a T1 Redistributed by hash(a) T2 sorted by hash(a) and MJ Join: T1.a=T2.a and T1.b=T2.b T2 Redistributed and sorted by RowKey RowKey based MJ	Join: T1.a=T2.a and T1.b=T2.b RowKey based MJ

Insert

Let's take a transaction table like ORDERS. In practice we load it periodically (eg. daily) with the new increment which is typically focused to a short interval of transaction date/time. If the ORDERS table is not partitioned, then the outstanding hashing algorithm will spread them all over the data blocks of the table evenly, therefore Teradata has to modify far more data blocks than the increment was reside in.

But if the ORDERS table is partitioned, then the physical order of the records is primarily determined by the partition key. This means that the increment will reside in very few partitions, close together, and the insert operation requires approx the same number of blocks to be written than the increment was in.

For more details on PPIs please refer the documentation of the appropriate Teradata version.

To be continued...

How to choose partitioned primary index (PPI)

What is the difference between NPPI and PPI?

• NPPI: Non partitioned primary index
  The good old regular PI. The rows are distributed by HASHAMP(HASHBUCKET(HASHROW(PI))), and ordered by HASHROW(PI), nothing special
• PPI: Partitioned primary index
  Distribution is the same, but ordering different: <PartitionID><HASHROW(PI)>. The <PartitionID> is a stored value in each rows, allocating 2 or 8 bytes (see below).

The only difference is the storing order of the records (and the 2/8 bytes overhead).

What is PPI good for?

The partitioning feature - like in many other databases - usually solves some performance issues, say enables to eliminate some needless work in specific situations.

• SELECT
  Eligible "where conditions" result serious partition-elimination, which means that usually only a small fraction of the table should be scanned instead of the whole one.
• INSERT
  Check the storing order of the NPPI tables: the records are in "hash" order, that is if I want to insert a series of records into a Teradata table, they will reside in spreadly distributed data blocks. If the table is big enough, my new eg. 1000 records will get into ~1000 different data blocks, what means 1000 pieces of expensive "random writes". However if my 1000 records got to a PPI table and they will have the same PartitionID, they will get into far less than 1000 data blocks with high probability. In real life situations we often will write to continuous data blocks with much cheaper "sequential write" 
• DELETE
  Same as INSERT
• BACKUP
  Teradata allows archiving only one or more partitions, saving lot of time and tape. Older data in transaction tables usually does not change therefore it is unnecessary to backup them every time

"Good-to-know"s

Costs of partitioning

Like all good things in the world, partitioning has trade-offs also:

• Extra 2/8 bytes per record allocated storage space
  Depending on maximal number of partitions. See "Number of partitions" chapter
• Slower "SAMPLE" scans
  Proper random sampling is more complex, since the physical storing order is in correlation with partitioning value
• Extra sort operations / Sliding window joins
  If joined to a table which has NPPI or PPI with not exactly same definition will result a preparation "sort" step, or leads to a "sliding window merge join", which is technically N x M merge joins between the partitions of TableA and TableB.

Number of partitions

How many partitions should I have?
How many partitions do I have?
How is an empty partition looks like?
They are all interesting questions, let's analyze the implementation of Teradata implementation.

Partition is not an object, it is just a calculated (and stored) value in the record, which will determine the physical storing order of the record. A partition will not allocate space, an "empty partition" technically means that no record exists with that partition's partitionID, nothing else.
How many partitions I have in the table? As many different PartitionID in the existing records occure, which depends on the occurring values of the partitioning column.
How many partitions can I have in the table? It depends on the table definition. One must use the RANGE_N or the CASE_N function to define the PartitionID calculation. Its definition unambiguously determines how many different PartitionID values may occur. In versions up to V13.10 65535 is allowed, from V14.00 we can have as many as 9.2 Quintillion (8 bytes PartitionID). The table definition cannot be altered to switch between 2 and 8 bytes layout.

What is the drawback of having many partition? The sliding-window merge join. Mind including partitioning column into the PI if possible (otherwise PI based filtering will cause as many accesses as many partitions exist).

What happens with the out-of-range records?

We have the clauses NO RANGE and NO CASE in the PPI definition. They mean an ID value for that partition that is out of the defined range or case, those records got into this partition. It can be a hidden trap, if you forget to maintain your date partition definition on a transaction table, and all records got to get into this partition from a moment. And the partition keeps fattening, queries keep go slowing somehow...

Multi level partitioning

This is a good trick. One can define partitioning "hierarchically", which is simply a "Cartesian product" of the partitions at each levels, the result is a single PartitionID. In case of 2 bytes partitioning, the "Cartesian product" should fall below 65535.

What is sensational in the Teradata implementation of multi level PPI? You can filter only lower level partitioning key(s) also, partition elimination will happen. How? It calculates all possible combinations, and produces the PartitionID list to be scanned, excellent.

Partitioning granularity

The next good question is: how fine should I define partitioning?

It depends. Basically I'd branch to two main cases:

• "Temporal" (date) partitioning
  The best partition size is the day. Most of the filtering is on day level, and we have ~365 days a year, not too much partitions for your lifetime. If we partition on monthly units, then the partition elimination ranges are more rough, and we have 12 partitions a year, which is also too much in case of a PI-NPPI join.
• All others
  It really depends. Depends on the goal, and the value demographics. It's good to correlate with the filtering pattern (what is the frequent relevant 'where' condition parcel).

Hope it helped, please ask, if something is missing or confusing.

How to use query banding in Teradata?

What is queryband?

Teradata is a diligent RDBMS that runs sometimes millions of SQLs a day. You will see them in the DBQL (DataBase Query Logging area) - if it is switched on - but it's a hard job to know around in that mess of queries. How can I find a specific query? What did that query run by? If I want to analyze or modify something I need to find the source of the execution as exactly as can be.
Queryband is a labelling possibility to flag the queries to let their source job/report/etc. be easily found.

Who defines the queryband?

Setting the queryband is usually the responsibility of the query runner:

• ETL software or solution that executes it
• OLAP tool that issues it
• Person, who runs it ad-hoc

How to set the queryband?

Technically it is a quite simple stuff: Teradata provides a command to set it:

SET QUERY_BAND = {'<variable1>=<value1>;<variable2>=<value2>;...' / NONE} [UPDATE] for SESSION/TRANSACTION;

, where:

<variable1>=<value1>;

Queryband can consist of arbitrary number of "variable"-"value" pairs. Both are string values. Do not forget to put the semicolon after each variable-value pair!

NONE: clears the queryband 

UPDATE: is specified, then those variables that has been previously defined are updated by the new value, others are added with the given value. Empty value string is a valid content and will not remove the variable. Please note that deleting a value is only possible by redefining the queryband without that specific variable.

SESSION/TRANSACTION: what it says...

Where can I check queryband?

The values are reflected in the dbc.SessionfoX.QueryBand and the dbc.DBQLogtbl.QueryBand. The following example shows its content:

SET QUERY_BAND='PROJECT=TeraTuningBlog;TASK=QB_example;' for session;

(For the logged in session)
SELECT queryband FROM dbc.sessioninfoX WHERE sessionNo=session;----------------------------------------------------
PROJECT=TeraTuningBlog;TASK=QB_example;

(For the formerly ran queries)

SELECT queryband FROM dbc.dbqlogtbl WHERE Queryid=...;----------------------------------------------------
=S> PROJECT=TeraTuningBlog;TASK=QB_example;

(For a specific variable, eg. "PROJECT")
SELECT QB_PROJECT FROM
(
   SELECT CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.sessioninfoX WHERE sessionNo=session
) x ;

----------------------------------------------------

TeraTuningBlog

(Which queries has been run by the "LoadCustomers" project?)

   SELECT a.*, CAST((case when index(queryband,'PROJECT=') >0 then substr(queryband,index(queryband,'PROJECT=') ) else '' end) AS VARCHAR(2050)) tmp_PROJECT
     ,CAST( (substr(tmp_PROJECT,characters('PROJECT=')+1, nullifzero(index(tmp_PROJECT,';'))-characters('PROJECT=')-1)) AS VARCHAR(2050)) QB_PROJECT
   FROM dbc.dbqlogtbl a WHERE QB_PROJECT="LoadCustomers"

;

Designing querybanding

We know how to set the queryband, it's quite easy to build in / configure in the ETL tool, OLAP software and other query running applications. But what variables should we define, and how should we populate them? I give a best practice, but it is just a recommendation, can be modified due according to your taste.

First of all, some things to mind:

• Use short variable names and values, since they will be logged in each DBQL records
• Define consistent structure in each source systems to easily analyze data
• Record as detailed information as you need, not more, not less. Define unique values for those items you later want to differentiate. Using a lookup/hierarchy table you can easily merge what you need, but never can drill down what is aggregated.

I recommend these variables to be defined:

• SYS: Maximum of 3 characters ID of the system that ran the Query, like INF (Informatica), MST (Microstrategy), SLJ (SLJM), BO (Business Objects), AH (ad-hoc query tool)
• ENV: P (Production) / Tx (Test x) / Dx (Development x), the identifier of environment. x may be neglected, if it does not matter
• JOB: Which job or report contains that specific query (the name of it)
• STP: (Step) Which SQL script, or other sub-structure does the query belong to (name of it)
• VER: Version of the JOB. This will determine the version of the script (if available)

What’s a DBA to do?

http://www.teradatamagazine.com/v09n02/tech2tech/applied-solutions-3-whats-a-dba-to-do/

A DATA WAREHOUSE FROM TERADATA ELIMINATES MANY MANUAL TASKS TYPICAL OF OTHER SYSTEMS.

BY ROGER MANN

What do experienced DBAs with an Oracle, IBM or Microsoft background need to know about managing a Teradata system? Basically, much less than they need to know about the others.

The Teradata Database is a shared-nothing massively parallel processing (MPP) relational database management system (RDBMS), making it the only commercially available RDBMS designed from the ground up for data warehousing.

Parallel processing and the automation of many typical DBA functions were created in the DNA of the Teradata Database. Because of that architecture, many functions that are manual or enhanced by wizards in other vendors’ systems are managed automatically. Consequently, the roles and responsibilities of the DBA are significantly different. Fewer tasks are required, making the system much easier to manage. Understanding those differences and how to exploit them with the RDBMS is key to driving the success of the organization.

The next sections break down how a data warehouse from Teradata differs from other systems, enabling the DBA to focus on more productive work and less on manually maintaining the data warehouse.

SYSTEM INSTALLATION

Data warehouse performance is achieved in a parallel database architecture by the “divide and conquer” method. First, the data is divided into small, equal units. Then independent software modules process those units simultaneously (i.e., in parallel) to conquer the problems (i.e., answer the queries).

The units of work and the hardware resources allocated to each parallel software module must be as equal as possible. Like a chain that is only as strong as its weakest link, the overall job cannot conclude until every unit has completed its processing. This “balanced processing” of workloads in the Teradata Database enables superior query performance.

The DBA’s objective, therefore, is to install a balanced processing platform, operating system (OS), database management software and disk subsystems. In a typical environment, these steps require careful planning with time-consuming analysis of user data and targeted queries. Anxious for a quick return on investment (ROI), however, management too often applies pressure to take shortcuts—which can have disastrous consequences.

With a Teradata purpose-built platform, the OS, database and disk subsystems are installed before system delivery. All that is needed is the size of the raw user data, number of concurrent users and some targeted queries. This information is inputted into a system-sizing calculator and a platform configuration is recommended. The result is a balanced parallel-processing platform that is tailored to the organization’s application requirements.

A pre-configured, purpose-built Teradata platform relieves DBAs of the responsibility of understanding and setting the myriad OS and database parameters and installation options. No longer do DBAs have to spend hours writing programs to analyze the data and determine partitioning and data placement. They are free from the burden of having to understand and be responsible for setting the various options and initialization parameters with both the database and OS.

Those runtime database control parameter settings are critical for performance tuning of transaction processing applications where queries are predetermined and must be tuned. However, a data warehouse is built on the concept that users are able to ask any question of any data at any time. There is no opportunity to know or tune the queries beforehand. This is best left to the Teradata Database to optimize and tune dynamically.

In short, once the Teradata platform is installed, the DBA can immediately define the databases, users and tables and load data. Users can then leverage the data warehouse as it is intended—to run queries that answer their business intelligence (BI) questions.

STORAGE MANAGEMENT

Click to enlarge

Because the Teradata storage subsystem is installed and balanced before delivery, management of the disk subsystem is greatly simplified. The DBA familiar with managing items such as disk groups, logical volumes, node groups, file system, files and tablespaces will find that those entities and concepts are nonexistent. (See table.)

All disk organization is entirely logical, as opposed to physical. (See figure) Initially, all space in the system is allocated to a predefined system database called DBC. Using the “CREATE” DML command, the DBA will define DATABASE and USER entities. The space parameter on the CREATE DML statement is not a physical “allocation” but is simply a size quota. If a database is allocated 5TB of space that is the maximum amount of space the database is allowed to use. Anytime that database attempts to use more than 5TB, an “out of space” message will result. However, the system is not out of space, it just exceeded its space quota.

Click to enlarge

DISK FILE SYSTEM

A database management system (DBMS) is designed for storing and retrieving data. Typical file-system architectures fragment and performance degrades over time as insert, updates and deletes are applied to the data.

Teradata broke all the rules with its file system design. Data is not stored in B-Tree indexes based on data values; rather, the file system is built on raw disk slices. There are no pages, BufferPools, tablespaces, extents, etc. to manage. Row lengths are variable and stored in blocks that can grow or shrink on demand. Maximum block size is configurable with row or block placement managed by the DBMS. Rows can dynamically be moved, space reclaimed or the file system defragmented on the fly. This is a transparent background process that runs continuously using available system resources. Because of this process, the DBA never has to do a re-organization, and performance is optimized on a continual basis and does not degrade with file updates.

USER MANAGEMENT

Defining users is easy with a Teradata system. There are two types: query users with no workspace capability; and power users who have the capability to create and manipulate tables within their own workspace based on whatever limitations the DBA placed on their space usage.

The DBA first adds the users with a CREATE USER DML command, then grants them security rights to database entities. Role-based security is supported for ease of maintenance.

INDEX MANAGEMENT

DBAs need to resist the temptation to over-index the tables. Because of the powerful parallel architecture of the Teradata platform, it is unnecessary to avoid full-table scans. Therefore, far fewer indexes are needed than in other RDBMSs. In fact, as experienced in several organizations, tables having more than 80 billion rows each can be scanned in less than five minutes.

These recommended steps will help determine the number of indexes needed in the Teradata Database:

• The DBA defines a primary index (PI) and a secondary index on any column that will participate as a foreign key in join operations. The PI is for data distribution and keyed access.
• Once the indexes are defined, the query workload is run and the query capture facility logs the query activity.
• The Teradata Index Wizard uses this information to recommend the addition or removal of indexes based on actual query usage.

This process saves the DBA from having to manually analyze the number of indexes.

Special indexes are available for specific performance needs. The partitioned primary index (PPI), for one, can deliver dramatic results. A large transaction file that is accessed with date parameter queries is a good candidate for creating a PPI on transaction date. The Optimizer then can eliminate partitions on any date-sensitive queries with dramatic response-time reductions.

The multi-level PPI, join, aggregate and aggregate join indexes are tools that can turbo-charge certain applications.

WORKLOAD MANAGEMENT

Teradata Active System Management provides the necessary tools for comprehensive workload management; therefore, no outside tools or resources are needed. The product has three components:

Dynamic Query Manager enables the DBA to classify and govern the query before its execution in the database.

Priority Scheduler defines resource partitions where varying workloads can be controlled and monitored.

Database Query Log provides post-execution performance analysis.

Because Teradata tools administer all performance management and tuning needs, the DBA no longer has to be an expert in the OS, database and third-party tools.

SYSTEM EXPANSION

One common characteristic of successful data warehouses is growth. Often, no matter how much detail is put into planning a data warehouse, expansion needs often arise unexpectedly and in unanticipated areas. The scalable Teradata system makes growth an easy process. In fact, Teradata’s Customer Support Team performs the expansion—the DBA’s role is simply as an observer.

Expanding the Teradata system is similar to ordering the initial platform. With assistance from Teradata Professional Services, the DBA determines the amount of raw data on the existing Teradata system and the number of concurrent users, as well as a few critical queries. Then those numbers are added to the anticipated growth in each area. These values are input into a system-sizing calculator, which produces the additional platform requirements.

As with the original Teradata system, the additional platform and software will be pre-configured, delivered, installed and connected to the existing platform. The data redistribution utility is then run, which automatically rebalances the data on the system. The tool relocates any data that belongs on the new platform nodes had it been installed at the time the data was loaded. Once that data is relocated, the utility tool removes it from the old nodes. (No data movement occurs between the original nodes.) Redistributing the data requires downtime on the system, but the process normally takes less than a shift to complete.

A BETTER VALUE

The features of the Teradata system make it ideal for data warehouse applications. The balanced, purpose-built platform arrives ready to deliver the first application generally in days, instead of weeks or months.

With the automated data management features, DBAs are freed from having to micro-manage the file system and can, therefore, engage in other tasks and responsibilities. For instance, instead of the DBA constantly writing and tuning queries, the query optimizer allows the user to ask any question, anytime. The support and freedom provided by a data warehouse from Teradata empowers DBAs to concentrate on working with the user community to deliver greater business value to their organization.

WHAT TERADATA DBAS DON’T DO:

With the automatic features included in a Teradata Database, DBAs have fewer tasks and responsibilities for implementing and maintaining the system. As identified in this partial list of duties, Teradata DBAs have never been required to:

• Install an operating system (OS)
• Understand and set extensive OS tuning parameters
• Install the Teradata Database
• Understand and set extensive Teradata Database parameters
• Write programs/execute utilities that determine how to divide data into partitions
• Determine size and physical location of each table and index partition or simple tablespace
• Code/allocate/format partitions or underlying file structures
• Embed partition assignment into CREATE TABLE statements
• Determine level/degrees of parallelism to be assigned to tables/partitions/databases
• Assign and manage special buffer pools for parallel processing
• Associate tables/queries with parallel degrees
• Code/allocate/format temporary work space

Join Indexes

Join Indexes

The join index JOIN the two tables together and keeps the result set in the permanent space of TD.

The join index will hold the result set of two table, and at the time of JOIN, parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index.

User never directly query the JOIN index.

In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.

Types of JOIN index-

1.       Multi table Join Index- Suppose we have two BASE tables Employee and Dept, which holds the data of employee and department respectively. Now a JOIN index on these two tables will be somewhat –

Create Join Index emp_dept as

Select empno, empname, emp_dept, emp_sal, emp_mgr

From employee e inner join dept d

On e.emp_dept=d.deptno

Unique primary index (empno);

This way the JOIN index EMP_DEPT holds the result set of two BASE tables and at the time of JOIN, PE will decide whether it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.

2.       Single table JOIN Index – A Single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.

Create Join Index emp_snap as

Select empno, empname, emp_dept

From employee

primary index (empdept);

3.       Aggregate JOIN Index – An aggregate JOIN index will allow the tracking of averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.

Create Join Index AGG_TABLE

Sel

Empno, sum(emp_sal)

From emp_salary

Group by 1;

The main fundamentals of JOIN indexes are:

1.       JOIN index is not a pointer to data it actually store data in PERM space.

2.       Users never query them directly, its PE who decide which result set to take.

3.       Updated when base tables are changed.

4.       Can’t be loaded with fastload or multiload.

Performance Issues With Data Maintenance

The very mention of changing data on disk implies that space must be managed by the AMP(s) owning the row(s) to modify. Data cannot be changed unless it is read from the disk.

For INSERT operations, a new block might be written or an existing block might be modified to contain the new data row. The choice of which to use depends on whether or not there is sufficient space on the disk to contain the original block plus the number of bytes in the new row.

If the new row causes the block to increase beyond the current number of sectors, the AMP must locate an empty slot with enough contiguous sectors to hold the larger block. Then, it can allocate this new area for the larger block.

A DELETE is going to make one or more blocks shorter. Therefore, it should never have to find a larger slot in which to write the block back to disk. However, it still has to read the existing block, remove the appropriate rows and re-write the smaller block.

The UPDATE is more unpredictable than either the DELETE or the INSERT. This is because an UPDATE might increase the size of the block like the INSERT, decrease the size like the DELETE or not change the size at all.

A larger block might occur because one of the following conditions:

·       A NULL value was compressed and now must be expanded to contain a value. This is the most likely situation .

·       A longer character literal is stored into a VARCHAR column.

A smaller block might occur because one of these conditions:

·       A data value is changed to a NULL value with compression. This is the most likely situation .

·       A smaller character literal is stored into a VARCHAR column.

A block size does not change:

·       The column is a fixed length CHAR, regardless of the length of the actual character data value, the length stays at the maximum defined.

·       All numeric columns are stored in their maximum number of bytes.

There are many reasons for performance gains or losses. Another consideration, which was previously mentioned, is the journal entries for the Transient Journal for recovery and rollback processing. The Transient Journal is mandatory and cannot be disabled. Without it, data integrity cannot be guaranteed.

Impact of FALLBACK on Row Modification

When using FALLBACK on tables, it negatively impacts the processing time when changing rows within a table. This is due to the fact that the same change must also be made on the AMP storing the FALLBACK copy of the row(s) involved. These changes involve additional disk I/O operations and the use of two AMPs instead of one for each row INSERT, UPDATE, or DELETE. That equates to twice as much I/O activity.

Impact of PERMANENT JOURNAL Logging on Row Modification

When using PERMANENT JOURNAL logging on tables, it will negatively impact the processing time when changing rows within a table. This is due to the fact that the UPDATE processing also inserts a copy of the row into the journal table. If BEFORE journals are used, a copy of the row as it existed before a change is placed into the log table. When AFTER images are requested, a copy of the row is inserted into the journal table that looks exactly like the changed row.

There is another issue to consider for journaling, based on SINGLE or DUAL journaling. DUAL asks for a second (mirror) copy to be inserted. It is the journals way to provide FALLBACK copies without the table being required to use FALLBACK. The caution here is that if the TABLE is FALLBACK protected, so are the journals. This will further impact the performance of the row modification.

Impact of Primary Index on Row Modification

In Teradata, all tables must have a Primary Index (PI). It is a normal and very important part of the storage and retrieval of rows for all tables. Therefore, there is no additional overhead processing involved in an INSERT or DELETE operation for Primary Indices.

However, when using an UPDATE and the data value of a PI is changed, there is more processing required than when changing the content of any other column. This is due to the fact that the original row must be read, literally deleted from the current AMP and rehashed, redistributed and inserted on another AMP based on the new data value.

Remember that Primary Keys do not allow changes, but Primary Indexes do. Since the PI may be a column that is not the Primary Key, this rule does not apply. However, be aware that it will take more processing and therefore, more time to successfully complete the operation when a PI is the column being modified.

Impact of Secondary Indices on Row Modification

In Teradata, a Secondary Index is optional. Currently, a table may have 32 secondary indices. Each index may be a combination of up 16 columns within a table. Every unique data value in a defined index has a row in the subtable and potentially one on each AMP for a NUSI (Non Unique Secondary Index). Additionally, every index has its own subtable.

When using secondary indices on tables, it may also negatively impact the processing time when changing rows within a table. This is due to the fact that when a column is part of an index and its data value is changed in the base table, the index value must also be changed in the subtable. This normally requires that a row be read, deleted and inserted into a subtable when the column is involved in a USI (Unique Secondary Index). Remember that the delete and insert are probably be on different AMP processors.

For a NUSI, the processing all takes place on the same AMP. This is referred to as AMP Local. At first glance this sounds like a good thing. However, the processing requires a read of the old NUSI, a modification, and a rewrite. Then, most likely it will be necessary to insert an index row into the subtable. However, if the NUSI already exists, Teradata needs to read the existing NUSI, append the new data value to it and re-write it back into the subtable. This is why it is important not to create a Primary Index or a Secondary Index on data that often changes.

The point of this discussion is simple. If secondary indices are used, additional processing is involved when the data value of the index is changed. This is true on an INSERT, a DELETE and an UPDATE. So, if a secondary index is defined, make sure that the SQL is using it to receive the potential access speed benefit. An EXPLAIN can provide this information. If it is not being used, drop the index.

As an added note to consider, when using composite secondary indices, the same column can be included in multiple indices. When this is the case, any data value change requires multiple subtable changes. The result is that the number of indices in which it is defined multiplies the previous AMP and subtable-processing overhead. Therefore, it becomes more important to choose columns with a low probability of change.