Index Maintenance During Mini-Batch Processing

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Michael McIntire’s recent posting on NoPI tables (read it now!) got me thinking more and more about mini-batch, and how it’s growing in popularity. Mini-batch is a technique that lets you update database tables in small batches several, many times a day using batch approaches. This could be a little batch job every hour, or it could be every 5 minutes, whatever frequency you choose. Not only will the mini-batch approach be a more efficient choice for updating database tables than continuous load, it streamlines the maintenance of any secondary structures because it can batch up those changes and get more done with less effort.  So let's talk about that overhead when you have indexes on the base tables.

First, What is Mini-Batch?

A common mini-batch approach is multi-phased:

1. FastLoad is used to load the new data quickly into a staging table that has no secondary structures (NoPI can help here).

2. An INSERT/SELECT moves data from the staging table into the base table that has been indexed.

3. The rows-just-inserted are deleted from the staging table.

Data transformations can take place before, during, or after the INSERT/SELECT.

NUSI vs. Join Index/Global Index Overhead

With a NUSI on the target table, mini-batch index maintenance is done locally and in parallel across all AMPs during the INSERT/SELECT step. NUSI change rows are collected as the data rows are processed. These changes are sorted and applied to the NUSI subtable after all inserts to the base table have been completed. This activity will be invisible in the Explain text.

If you have a single-table join index (in its simplest form known as a global index) on the target table, there will be some step-to-step data passing involved. A spool file with join index updates is written in the step that performs the base table inserts, and it is then read in a subsequent step. And since in most cases the join index primary index is different from the base table primary index, a redistribution of that spool is required to get the join index maintenance rows to the correct AMP. You will see this as an extra step in the Explain. This is in contrast to NUSI maintenance, which never requires a redistribution.

The overhead you will experience with either of these index strategies will depend on the demographis of the indexed column and other system characteristics. With mini-batch, however, both approaches result in smaller maintenance overhead than would row-at-a-time inserts.

Explain examples that Illustrate these Differences

Below are some simple explains that show the difference in overhead described above.

No NUSIs or Join Indexes on the OnlineTxn table:

EXPLAIN

INSERT INTO OnlineTxn

SELECT * FROM StagingTxn;

Explanation

1) First, we lock a distinct CAB."pseudo table" for read on a

RowHash to prevent global deadlock for CAB.StagingTxn.

2) Next, we lock a distinct CAB."pseudo table" for write on a

RowHash to prevent global deadlock for CAB.OnlineTxn.

3) We lock CAB.StagingTxn for read, and we lock CAB.OnlineTxn

for write.

4) We do an all-AMPs MERGE into CAB.OnlineTxn from

CAB.StagingTxn. The size is estimated with no confidence to

be 26,160 rows. The estimated time for this step is 1 second.

5) We spoil the parser's dictionary cache for the table.

6) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

With one NUSI on the OnlineTxn table the plan is the same:

CREATE INDEX(o_orderdate) ON OnlineTxn;

EXPLAIN

INSERT INTO OnlineTxn

SELECT * FROM StagingTxn;

Explanation

1) First, we lock a distinct CAB."pseudo table" for read on a

RowHash to prevent global deadlock for CAB.StagingTxn.

2) Next, we lock a distinct CAB."pseudo table" for write on a

RowHash to prevent global deadlock for CAB.OnlineTxn.

3) We lock CAB.StagingTxn for read, and we lock CAB.OnlineTxn

for write.

4) We do an all-AMPs MERGE into CAB.OnlineTxn from

CAB.StagingTxn. The size is estimated with no confidence to

be 26,160 rows. The estimated time for this step is 1.20 seconds.

5) We spoil the parser's dictionary cache for the table.

6) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

With one Join Index (Global Index) on the OnlineTxn table, additional processing steps are required:

DROP INDEX(o_orderdate) ON OnlineTxn;

CREATE JOIN INDEX DateJI AS

SELECT (o_orderdate),(rowID)

FROM OnlineTxn

PRIMARY INDEX(o_orderdate);

EXPLAIN

INSERT INTO OnlineTxn

SELECT * FROM StagingTxn;

Explanation

1) First, we lock a distinct CAB."pseudo table" for write on a

RowHash to prevent global deadlock for CAB.DATEJI.

2) Next, we lock a distinct CAB."pseudo table" for read on a

RowHash to prevent global deadlock for CAB.StagingTxn.

3) We lock a distinct CAB."pseudo table" for write on a RowHash

to prevent global deadlock for CAB.OnlineTxn.

4) We lock CAB.DATEJI for write, we lock CAB.StagingTxn for

read, and we lock CAB.OnlineTxn for write.

5) We do an all-AMPs MERGE into CAB.OnlineTxn from

CAB.StagingTxn followed by an insert in Spool 2. The size is

estimated with no confidence to be 26,160 rows. The estimated

time for this step is 1 second.

6) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

an all-rows scan into Spool 3 (all_amps), which is redistributed

by the hash code of (CAB.OnlineTxn.O_ORDERDATE) to all AMPs.

Then we do a SORT to order Spool 3 by join index. The size of

Spool 3 is estimated with high confidence to be 26,160 rows (

706,320 bytes). The estimated time for this step is 0.02 seconds.

7) We do an all-AMPs MERGE into CAB.DATEJI from Spool 3 (Last

Use). The size is estimated with high confidence to be 26,160

rows. The estimated time for this step is 1 second.

8) We spoil the parser's dictionary cache for the table.

9) We spoil the parser's dictionary cache for the table.

10) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

4 Comments
Enthusiast

1) For minibatch, does it mean that in the first phase of fastload, the table loaded is of NoPI structure and in the second phase, the same table itself  TD chooses itself the PI and re-arrange the record sets or how does it happen?

2) I think for  insert/select from staging to target , TD has made it work  same speed or faster than mload or fastload

Thanks and regards,

Raja

Teradata Employee

Raja,

I am sorry if have not fully understood the question you are asking.  A minibatch process would include both Phase I and Phase II of FastLoad and both phases would operate on the same table, which could be a PI table or a NoPI table.   If it is a NoPI table, then during FastLoad the rows will not need to ordered or redistributed by primary index, as the table has no primary index, while a PI table would require that extra work during a FastLoad, and therefore be less efficient.

When loading into a NoPI table, large blocks of rows that are sent to the database in Phase I are either delivered by a session directly to one AMP where they will be stored, or the large arriving blocks are sent randomly to one of the AMPs in the system without having redistribute each individual rowin Phase I or having to sort the rows in Phase II.  So it is much faster than loading a PI table.

For more information on NoPI table, search on NoPI in the DevX search box.  When I did that I found an article titled "Say Yes to NoPI tables" in Dev X that will provide more information on this feature.

Thanks, -Carrie

Enthusiast

Hi Carrie,

Thanks for clarifying.  I am planning to architect the staging.---- So first I will  fastload the data (from a unix file say) to a staging non-partitioned NoPI table as it avoids the hash distribution and sorting in amps. Then next, I will load using insert-select to a target non NoPI or target Column-partitioned or non-column-partitioned table say for example.

I am afraid that in the first step, I may bump into skewness of data.

If NoPI table is not skewed, a direct insert can be performed into column or non-column-partitioned table. Example

INSERT INTO Target_Tbl SELECT * FROM Staging_Tbl

So, I am thinking that I can  use hash by column name, hash by random, local order by or a combination of these to load both to column and non-column-partitioned table. Please correct me if I am wrong.

My second doubt is : Does it mean that a direct fastload load from file to a target is slower than my above process?

Sorry for bothering you.

Thanks and regards,

Raja

Teradata Employee

Hi Raja,

You can certainly use the HASH BY COLUMN or HASH BY RANDOM options, if you are on 14.0.  They work on any NOPI table (column-partitioned or not), but not on tables that have a primary index.   You cannot use the HASH BY clause on an INSERT into a table with a primary index or primary AMP index since that would conflict with the distribution required by the index.

I can't tell you which will perform better, fastload or insert/select, you would need to test that out with your data.  If the table being loaded is a column-partitioned table, you cannot use fastload against it, only insert/select.

Thanks, -Carrie