page79
61
ps.ps_supplycost / ps.ps_availqty AS unit_price,
s.s_acctbal AS supplier_account_balance,
s.s_name AS supplier_name
FROM partsupp_baseline ps,
part_baseline p,
supplier_baseline s
WHERE ps.ps_partkey = p.p_partkey
AND ps.ps_suppkey = s.s_suppkey;
When the dynamic table is declared, the TARGET_LAG value dictates the first
runtime; in this example, 30 minutes will elapse before the first refresh.
You must now resume the dynamic table.
ALTER DYNAMIC TABLE dt_supplier_part RESUME;
If you see the error message “Dynamic Table 'TPC.TPC_OWNER.DT_SUPPLIER_
PART' is not initialized. Please run a manual refresh or wait for a scheduled refresh
before querying.” then you must refresh the dynamic table to force a refresh.
ALTER DYNAMIC TABLE dt_supplier_part REFRESH;
On completion you should see status information similar to that shown in Figure 3-9.
Figure 3-9.
Dynamic table refresh output
After refreshing the dynamic table, you can clear the cache.
ALTER SESSION SET use_cached_result = FALSE;
Then ensure you are using a Large warehouse to guarantee comparable execution
context as before.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_l );
Suspend and restart the warehouse.
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_l ) SUSPEND;
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_l ) RESUME;
ChapTer 3
The Query profiler