Doing the join-index shuffle with Unity Director/Loader

Unity
Unity is Teradata’s data synchronization and workload routing tool providing Active – Active database availability delivering near real-time RTO/RPO
Teradata Employee

Doing the join-index shuffle with Unity Director/Loader

Life often requires comprises. In a data warehouse, there's often a trade-off between providing quick response times to reporting users running complex reports, and loading tables with new up-to-date data. Adding join-indexes to a table can speed up reports, and solve redistribution problems, but do come at a cost to table updates.

Let’s consider a common situation; a table, with several join-indexes, that is updated with new data using a merge statement from a staging table that is loaded every day.

To illustrate this example, let’s define some simple example tables. Consider the situation when we want to update the base table employee:

CREATE SET TABLE PERSONNEL.employee, FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
EmpNo SMALLINT NOT NULL,
Name VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
DeptNo SMALLINT,
JobTitle VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
Salary DECIMAL(8,2) FORMAT 'ZZZ,ZZ9.99' CHECK ( (Salary >=1.00 ) AND(Salary <= 999000.00 ) )
)
UNIQUE PRIMARY INDEX ( EmpNo );

...from a staging table PERSONNEL.employee_STAGING, which has a matching definition, using the merge statement:

MERGE INTO PERSONNEL.employee as t1
USING PERSONNEL.employee_STAGING as t2 ON t1.EmpNo=t2.EmpNo AND t1.Name=t2.Name
WHEN MATCHED THEN UPDATE SET Salary=t2.Salary, JobTitle=t2.JobTitle, DeptNo=t2.DeptNo
WHEN NOT MATCHED THEN INSERT (t2.EmpNo, t2.Name, t2.DeptNo, t2.JobTitle, t2.Salary);

Suppose that to support reports that execute on the base employee table, it has a set of join indexes defined:

create join index PERSONNEL.employee_deptno as select EmpNo,name,DeptNo fromPERSONNEL.employee primary index(DeptNo);
create join index PERSONNEL.employee_name as select EmpNo,name,DeptNo fromPERSONNEL.employee primary index(name);
create join index PERSONNEL.employee_JobTitleas select EmpNo,name,DeptNo,JobTitle from PERSONNEL.employee primary index(JobTitle)

There are a few reasons why this table may need join indexes. In this example, the join indexes might be questionable, since they were created simply to illustrate a point, but let’s assume there are solid reasons for them and that some reports that rely on these single-table  join indexes to provide an alternate primary indexes that use the deptNo,  Name or JobTitle column. These could, for example, possibly allow the optimizer to avoid redistributing the table during some reports and potentially exhausting spool space. Why the join-index is required is not relevant here (let’s assume it is), only the cost required to maintain it.

During the merge of data into the employee table, there is a cost imposed by the join indexes. This cost is visible in the explain plan for the merge statement:

explain MERGE INTO PERSONNEL.employee as t1
USING PERSONNEL.employee_STAGING as t2
ON t1.EmpNo=t2.EmpNo AND t1.Name=t2.Name
WHEN MATCHED THEN UPDATE SET Salary=t2.Salary,JobTitle=t2.JobTitle, DeptNo=t2.DeptNo
WHEN NOT MATCHED THEN INSERT (t2.EmpNo, t2.Name, t2.DeptNo, t2.JobTitle, t2.Salary);

*** Help information returned. 76 rows.
*** Total elapsed time was 1 second.

Explanation
--------------------------------------------------------------------------
1) First, we lock a distinct personnel."pseudo table" for write on a RowHash to prevent global deadlock for personnel.EMPLOYEE_JOBTITLE.
2) Next, we lock a distinct personnel."pseudo table" for write on a RowHash to prevent global deadlock for personnel.EMPLOYEE_NAME.
3) We lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee_STAGING.
4) We lock a distinct PERSONNEL."pseudo table" for write on a RowHash to prevent global deadlock for PERSONNEL.employee.
5) We lock personnel.EMPLOYEE_JOBTITLE for write, we lock personnel.EMPLOYEE_NAME for write, we lock PERSONNEL.employee_STAGING for read, and we lock PERSONNEL.employee for write.
6) We do an all-AMPs merge with matched updates and unmatched inserts into PERSONNEL.employee from PERSONNEL.employee_STAGING with a condition of ("(PERSONNEL.employee.EmpNo = PERSONNEL.employee_STAGING.EmpNo) AND (PERSONNEL.employee.Name =PERSONNEL.employee_STAGING.Name)").We build Spool 3 with before and after image of updated rows.We build Spool 4 with inserted rows.The number of rows merged is estimated with no confidence to be 102,528 rows.
7) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows scan into Spool 5 (all_amps), which is redistributed by the hash code of (Name) to all AMPs.Then we do a SORT to order Spool 5 by row hash.The size of Spool 5 is estimated with no confidence to be 51,264 rows (1,384,128 bytes).The estimated time for this step is 0.50 seconds.
8) We execute the following steps in parallel.
1) We do a MERGE Update to personnel.EMPLOYEE_NAME from Spool 5 (Last Use) by matching the whole row.The size is estimated with no confidence to be 51,264 rows (1,281,600 bytes).The estimated time for this step is 1 second.
2) We do an all-AMPs RETRIEVE step from Spool 3 by way of an all-rows scan into Spool 6 (all_amps), which is redistributed by hash code to all AMPs.Then we do a SORT to order Spool 6 by row hash.The size of Spool 6 is estimated with no confidence to be 51,264 rows (1,589,184 bytes).The estimated time for this step is 1.79 seconds.
9) We execute the following steps in parallel.
1) We do an all-AMPs MERGE DELETE to personnel.EMPLOYEE_JOBTITLE from Spool 6 (Last Use).The size is estimated with no confidence to be 51,264 rows.The estimated time for this step is 1 minute and 26 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of an all-rows scan into Spool 7 (all_amps), which is redistributed by hash code to all AMPs.Then we do a SORT to order Spool 7 by row hash.The size of Spool 7 is estimated with no confidence to be 51,264 rows (1,589,184 bytes).The estimated time for this step is 0.61 seconds.
10) We execute the following steps in parallel.
1) We do an all-AMPs MERGE into personnel.EMPLOYEE_JOBTITLE from Spool 7 (Last Use).The size is estimated with no confidence to be 51,264 rows.The estimated time for this step is 1.73 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 4 by way of an all-rows scan into Spool 8 (all_amps), which is redistributed by hash code to all AMPs.Then we do a SORT to order Spool 8 by row hash.The size of Spool 8 is estimated with no confidence to be 51,264 rows (1,281,600 bytes).The estimated time for this step is 0.49 seconds.
11) We execute the following steps in parallel.
1) We do an all-AMPs MERGE into personnel.EMPLOYEE_NAME from Spool 8 (Last Use).The size is estimated with no confidence to be 51,264 rows.The estimated time for this step is 1.66 seconds.
2) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan into Spool 9 (all_amps), which is redistributed by hash code to all AMPs.Then we do a SORT to order Spool 9 by row hash.The size of Spool 9 is estimated with no confidence to be 51,264 rows (1,589,184 bytes).The estimated time for this step is 0.61 seconds.
12) We do an all-AMPs MERGE into personnel.EMPLOYEE_JOBTITLE from Spool 9 (Last Use).The size is estimated with no confidence to be 51,264 rows.The estimated time for this step is 1.73 seconds.
13) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

No rows are returned to the user as the result of statement 1.

Essentially, for each row, each join-index might need to be updated.  That comes with a cost in performance overhead that can increase the time the merge statement takes.

To give us some numbers to work with, consider that using a small 1 node Teradata system (running in a virtual machine) this merge statement takes 5 minutes and 27 seconds with 102479 rows in our example table.

But - what if we could execute the same merge on the table, without the join-indexes? By dropping the join-indexes first, the same merge statement becomes much simpler to execute, as the explain plan illustrates:

explain MERGE INTO PERSONNEL.employee as t1
USING PERSONNEL.employee_STAGING as t2
ON t1.EmpNo=t2.EmpNo AND t1.Name=t2.Name
WHEN MATCHED THEN UPDATE SET Salary=t2.Salary, JobTitle=t2.JobTitle, DeptNo=t2.DeptNo
WHEN NOT MATCHED THEN INSERT (t2.EmpNo, t2.Name, t2.DeptNo, t2.JobTitle, t2.Salary);

*** Help information returned. 15 rows.
*** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct PERSONNEL."pseudo table" for read on a RowHash to prevent global deadlock for PERSONNEL.employee_STAGING.
2) Next, we lock a distinct PERSONNEL."pseudo table" for write on a RowHash to prevent global deadlock for PERSONNEL.employee.
3) We lock PERSONNEL.employee_STAGING for read, and we lock PERSONNEL.employee for write.
4) We do an all-AMPs merge with matched updates and unmatched inserts into PERSONNEL.employee from PERSONNEL.employee_STAGING with a condition of ("(PERSONNEL.employee.EmpNo = PERSONNEL.employee_STAGING.EmpNo) AND (PERSONNEL.employee.Name = PERSONNEL.employee_STAGING.Name)").The number of rows merged is estimated with no confidence to be 102,656 rows.
5) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

No rows are returned to the user as the result of statement 1.

The explain certainly is much shorter; so what difference does it make for execution time? In our example, it means the merge could potentially go much faster:

drop join index employee_deptno;
*** Index has been dropped.
*** Total elapsed time was 1 second.

drop join index employee_deptno;
*** Index has been dropped.
*** Total elapsed time was 1 second.

drop join index PERSONNEL.employee_deptno;
*** Index has been dropped.
*** Total elapsed time was 1 second.

drop join index PERSONNEL.employee_name;
*** Index has been dropped.
*** Total elapsed time was 1 second.

drop join index PERSONNEL.employee_JobTitle;
*** Index has been dropped.
*** Total elapsed time was 1 second.

MERGE INTO PERSONNEL.employee as t1
USING PERSONNEL.employee_STAGING as t2
ON t1.EmpNo=t2.EmpNo AND t1.Name=t2.Name
WHEN MATCHED THEN UPDATE SET Salary=t2.Salary, JobTitle=t2.JobTitle, DeptNo=t2.DeptNo
WHEN NOT MATCHED THEN INSERT (t2.EmpNo, t2.Name, t2.DeptNo, t2.JobTitle, t2.Salary);

*** Merge completed. 102479 rows affected.
No rows inserted, 102479 rows updated, no rows deleted.

*** Total elapsed time was 1 second.

To re-create the join index from scratch after loading the table is also very quick:

create join index PERSONNEL.employee_deptno as select EmpNo,name,DeptNo from PERSONNEL.employee primary index(DeptNo);
*** Index has been created.
*** Total elapsed time was 2 seconds.

create join index PERSONNEL.employee_name as select EmpNo,name,DeptNo from PERSONNEL.employee primary index(name);
*** Index has been created.
*** Total elapsed time was 3 seconds.

create join index PERSONNEL.employee_JobTitleas select EmpNo,name,DeptNo, JobTitle from PERSONNEL.employee primary index(JobTitle) ;
*** Index has been created.
*** Total elapsed time was 5 seconds.

In this case, we went from 5 minutes and 27 seconds to approximately 10 seconds. Again, this example is illustrative, but it does reflect a common challenge encountered when loading tables with many indexes. 

So, if we could just drop the join indexes first, there is the potential to save a lot of time. Unfortunately, that would potentially cause issues for the business users that are running reports on that table, since the reports rely on the join-indexes. Without the join-indexes the reports might take substantially longer, or even fail because they run out of spool space while joining tables. It seems we are forced to make a trade-off between reporting and loading.

But wait! Unity Director provides a way we can have our cake and eat it too. In a Director environment, there are two copies of the base table available - one on each system. As long as the reporting uses do not need the data from the latest second (which can be assumed, since this table is being loaded once a day), one copy of the table can be used to support reports while the other copy is being loaded with the latest data. Once the load job on the second copy of the table is complete, the reporting users can switch over to use it while the first copy of the table is being loaded.

Unity Director provides multiple ways to control access to tables and systems. For example, you might be tempted to use routing rules to direct the reporting users to system 1. Since routing rules are selected based on user mappings however, there is the potential that you might miss a user during the switch. You also need to be cautious of existing sessions when switching routing rules. Instead, a better approach is to control access to the tables using table state. This allows better protection against users falling through the cracks, and is easier to script.

Here are the steps:

1. Before doing the merge, the staging data can be loaded on both systems using a single load job and Unity Loader.

2. Using unityadmin, make the base table read-only, in order to ensure there are no writes from client applications connected through Unity Director:

unityadmin> object freeze personnel.employee;
Operation Number : 20
Operation Name: Freezing Table
User: admin
User Name: Main Administration User
Start Time: 05/01 10:08:28
Systems:
[1] db1
[2] db2
Updates:
05/01 10:08:28 [-] Info: Freezing table personnel.employee
05/01 10:08:28 [-] Info: Requesting mgmt R lock on 'personnel.employee'
05/01 10:08:28 [-] Info: Mgmt R lock on 'personnel.employee' granted
05/01 10:08:28 [-] Info: Releasing mgmt R lock on 'personnel.employee'
05/01 10:08:28 [-] Info: Successfully froze table on 2 systems
05/01 10:08:28 [-] Info: Operation finished
Status: Finished (1)
Finish Time : 05/01 10:08:28
Systems:
[1] db1 - Finished (1)
[2] db2 - Finished (1)
unityadmin>

3. Since the copy of the table on system 2 is now going to be out-of-sync with the table on system 1, it’s appropriate to make that copy of the table unrecoverable, by deactivating the table. This prevents any chance of someone accidently accessing the copy on system 2.

unityadmin> object deactivate personnel.employee on db2;
Operation Number : 22
Operation Name: Deactivating Table
User: admin
User Name: Main Administration User
Start Time: 05/01 10:10:37
Systems:
[2] db2
Updates:
05/01 10:10:37 [-] Info: Deactivating table personnel.employee
05/01 10:10:37 [-] Info: Successfully deactivated table on 1 systems
05/01 10:10:37 [-] Info: Operation finished
Status: Finished (1)
Finish Time : 05/01 10:10:37
Systems:
[2] db2 - Finished (1)

4. Now the base table on system 2 can be updated using the merge. To do this, the merge is executed directly on system 2. Before the merge statement, the join index is dropped, and then it’s recreated afterwards. As an added bonus, this is a good time to collect statistics on the table. Since the reporting users are still actively connected to system 1, there’s no performance impact to them from this activity.

5. The base table one system 2 is now updated and ready for use, so the state of the tables is flipped using unityadmin. To ensure no one tries to write to the base data from a client connected through Unity Director, we immediately set the state to READ-ONLY:

unityadmin> object activate personnel.employee on db2 synced from db1;
Operation Number : 24
Operation Name: Copying Table Queue
User: admin
User Name: Main Administration User
Start Time: 05/01 10:12:49
Systems:
[2] db2
Updates:
05/01 10:12:49 [-] Info: Requesting mgmt R lock on 'personnel.employee'
05/01 10:12:49 [-] Info: Mgmt R lock on 'personnel.employee' granted
05/01 10:12:49 [-] Info: Successfully deactivated table on 1 systems
05/01 10:12:49 [-] Info: Operation finished
05/01 10:12:49 [-] Info: Successfully changed the table state on 2 systems
05/01 10:12:49 [-] Info: Operation finished
05/01 10:12:49 [-] Info: Releasing mgmt R lock on 'personnel.employee'
Status: Finished (1)
Finish Time : 05/01 10:12:49
Systems:
[2] db2 - Finished (1)

unityadmin> object freeze personnel.employee on db2;
Operation Number : 26
Operation Name: Freezing Table
User: admin
User Name: Main Administration User
Start Time: 05/01 10:13:30
Systems:
[2] db2
Updates:
05/01 10:13:31 [-] Info: Freezing table personnel.employee
05/01 10:13:31 [-] Info: Requesting mgmt R lock on 'personnel.employee'
05/01 10:13:31 [-] Info: Mgmt R lock on 'personnel.employee' granted
05/01 10:13:31 [-] Info: Releasing mgmt R lock on 'personnel.employee'
05/01 10:13:31 [-] Info: Successfully froze table on 1 systems
05/01 10:13:31 [-] Info: Operation finished
Status: Finished (1)
Finish Time : 05/01 10:13:31
Systems:
[2] db2 - Finished (1)

Now the copy of the base table on system 1 is updated using the same steps as system 2. The copy on system 1 is deactivated using unityadmin. The indexes are dropped, the table is merged and indexes are re-created, all directly on system 1.

unityadmin> object deactivate personnel.employee on db1;
Operation Number : 28
Operation Name: Deactivating Table
User: admin
User Name: Main Administration User
Start Time: 05/01 10:15:39
Systems:
[1] db1
Updates:
05/01 10:15:39 [-] Info: Deactivating table personnel.employee
05/01 10:15:39 [-] Info: Successfully deactivated table on 1 systems
05/01 10:15:39 [-] Info: Operation finished
Status: Finished (1)
Finish Time : 05/01 10:15:39
Systems:
[1] db1 - Finished (1)

7. Finally, the copy of the table on system 1 is activated, and copy on system2 is set back to active to allow writes. Both copies of the table are now up-to-date with the latest date.

This technique offers the best of both worlds - a fast merge and no significant outage to the business users. For users of Unity Director & Loader, this provides another tool to tackle situations where they would normally be forced to make a trade-off between different goals. The general approach of working on one system and then the other could be potentially applied to any data warehouse that could be disruptive to execute on all systems at the same time.

This is just a glimpse of the value Unity Director and Loader can provide. 

Tags (3)