• Product: Aleph
• Product Version: 20, 21, 22, 23
• Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

Question    
After finishing parallel indexing, util e/5/2 was run to dump the z07H into z07 (Update Doc). The resulting counts are puzzling.

SQL> select count(*) from z07h;
COUNT(*)
----------
14317

SQL> select count(*) from z07;
COUNT(*)
----------
10302

UE-01 (indexing daemon) has only been running for a few minutes and the z07 count was never 14,317. 
Also, z07h is still there, although the understanding was that it would be emptied after this action.

Answer    
Unlike the z07_rec_key, the z07h_rec_key does not have a unique Oracle index defined for it. Thus, unlike the z07, the z07h contains duplicates. This can be seen in the following SQLs:

SQL> select count (*) from z07h where z07h_rec_key in (select z07h_rec_key from z07h group by z07h_rec_key having count(*) = 1);
COUNT(*)
----------
9274

SQL> select count (*) from z07h where z07h_rec_key in (select z07h_rec_key from z07h group by z07h_rec_key having count(*) > 2);
COUNT(*)
----------
5043

SQL> select count (*) from z07h where z07h_rec_key in (select z07h_rec_key from z07h group by z07h_rec_key having count(*) > 10);
COUNT(*)
----------
487

When these z07H records are written by util e/5/2 as z07's, the duplicates are eliminated.

{ util_e_05_02:
INSERT INTO &&1.Z07 USING SELECT DISTINCT
...
FROM &&1.Z07H
WHERE Z07H_REC_KEY NOT IN (SELECT Z07_REC_KEY FROM &&1.Z07) }

The Z07H records are left in place in case they need to be looked at, reprocessed, etc. But when util e/5/1 is next run, it drops the previous Z07H:
echo "This option will recreate Z07H and drop all previously stored Z07H records."

• Article last edited: 12-Mar-2016