page142
125
Typical data processing operations are to merge staged data into target tables using
MERGE
,
INSERT
,
UPDATE
, and
DELETE
operators.
For a 2.5-billion row unclustered table, I found
INSERT
and
DELETE
operations to
perform best.
UPDATE
operations were problematic and took much longer to complete.
Let’s investigate these scenarios further. You start with timing an
UPDATE
operation
using an X-Small warehouse, which I timed at over 26 minutes to complete; you may
want to experiment with a larger warehouse sizing. The
UPDATE
will affect 3,000,013,782
records.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
UPDATE tpc.tpc_owner.lineitem_baseline
SET l_linestatus = 'X'
WHERE l_linestatus = 'F';
Let’s see the effect on storage by repeating the earlier query noting that you may
experience latency.
SELECT active_GB,
time_travel_GB,
failsafe_GB,
retained_for_clone_GB
FROM v_table_storage_metrics
WHERE path_to_object = 'TPC.TPC_OWNER.LINEITEM_BASELINE';
Figure 4-7 shows two rows.
•
The first row is the original expected data set size.
•
The second row is after the
UPDATE
statement. Note that
TIME_
TRAVEL_GB
reflects the storage retained for the
UPDATED
rows.
Figure 4-7.
UPDATE storage consumption
ChaPTer 4
MiCro-ParTiTionS