page143
126
You might also want to compare the number of micro-partitions for
lineitem_
baseline
.
SELECT parse_json(system$clustering_information ( 'lineitem_baseline',
'(l_shipdate)' )):total_partition_count;
The previous query should return 9405; the initial micro-partition count was 9398.
From your investigation, three facts emerge.
•
The amount of storage required to hold the same amount of data has
increased.
•
The micro-partition count has increased from 9398 to 9405.
•
Time Travel storage has been created; this is expected behavior.
You should expect a single character update to use the same amount of storage as
the original data set, but the evidence proves there has been an increase in storage.
you should also expect the number of micro-partitions for both before and after your
UPDATE
. Why do your figures not match?
The answer may lie with how the internal cluster key has been declared, and you
know from the earlier discussion it is not possible to determine the internal clustering
key attribute order. I discuss cluster keys in the next chapter.
Let’s investigate these scenarios further; I started with timing an
UPDATE
operation
using an X-Small warehouse, which I timed at more than 18 minutes to complete. My
INSERT
created 3,000,013,782 records and took around 8 minutes less to complete than
an
UPDATE
.
INSERT INTO tpc.tpc_owner.lineitem_baseline
SELECT *
FROM snowflake_sample_data.tpch_sf1000.lineitem
WHERE l_linestatus = 'F';
Then check the effect on storage by repeating the earlier query; note that you may
experience latency.
SELECT active_GB,
time_travel_GB,
failsafe_GB,
retained_for_clone_GB
ChaPTer 4
MiCro-ParTiTionS