page210
296
Good table segmentation candidates include the following:
•
Geographic region
•
Summary date/year range
Returning to the customer complaint, let’s investigate how the application process
can be improved by segmenting a core table.
While you have created sample data with known numbers of
INSERT
,
UPDATE
, and
DELETE
operations, you cannot parallelize using this technical dimension as the target
core table is clustered using
region_key
and
nation_key
. The DML will block as the
micro-partitions will be locked until each operation has completed.
Figure 8-18 illustrates the objective for this section, which is to take a single core
table and segment according to the current clustering key definition.
Figure 8-18.
Segmentation objective
Before proceeding, ensure all grants to the original core table are preserved for later
modification and reuse.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
SHOW GRANTS ON TABLE core_customer_order;
SELECT 'GRANT '||"privilege"||' ON '||"granted_on"||' '||
LOWER ( REPLACE ( "name", '"', '' ))||
' TO ROLE '||LOWER ( "grantee_name" )||';'
FROM TABLE ( RESULT_SCAN ( last_query_id()));
From the previous check, you know there are five regions. You will use this
information to derive the segmented tables for the target core table.
Chapter 8
parallelization