page144
127
FROM v_table_storage_metrics
WHERE path_to_object = 'TPC.TPC_OWNER.LINEITEM_BASELINE';
Figure 4-8 shows three rows.
•
The first row is the original expected data set size.
•
The second row is after the
UPDATE
statement.
•
The third row is after the
INSERT
statement; note that
time_travel_
GB
reflects the storage retained for the
UPDATE
rows.
Figure 4-8.
INSERT storage consumption
You know that new micro-partitions will have been added due to the
INSERT
, the
new data volume is reflected in the
active_GB
column for the third record. As you are
inserting new records, no Time Travel storage is created; this is expected behavior.
You expect the number of micro-partitions to be about 50% more than 9405:
SELECT parse_json(system$clustering_information ( 'lineitem_baseline', '(l_
shipdate)' )):total_partition_count;
The returned micro-partition count is 14,074 indicating 4,669 new micro-partitions
were created in line with expectations.
The last SQL statement is a
DELETE
operation using an X-Small warehouse, which I
timed at more than six minutes to complete. The
DELETE
removed 3,000,013,782 records
and took around 20 minutes less to complete than the
UPDATE
and 12 minutes less than
the
INSERT
.
DELETE FROM tpc.tpc_owner.lineitem_baseline
WHERE l_linestatus = 'X';
ChaPTer 4
MiCro-ParTiTionS