page72
54
failure to set warehouse size correctly will result in excessive consumption
charges.
Cross-referencing the record count to the query execution times in Table 3-1, you
can see that a reasonable starting point for the warehouse is Large. You can adjust this
up or down according to actual performance, an exercise I will leave for you.
Don’t be frightened of using large or bigger warehouses, but they can be more
time and cost effective than using a smaller warehouse.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_l );
Suspend and restart your warehouse.
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_l ) SUSPEND;
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_l ) RESUME;
Developing an Example Query
Having set your warehouse and assuming your context (database, schema, and role) has
not changed, let’s define an imaginary scenario using the TPC data to base your example
query on.
You might imagine yourself working within the IT department of a machine parts
supplier. You hold stock purchased over time; therefore, you will have multiple records
for a single named part. Because of currency fluctuations, the purchase price has varied.
Your objective is to identify the costliest stock and sell this first for accounting reasons.
Let’s first create a view called
v_supplier_part
to both denormalize the data model
and simplify your data access path. You can remove extraneous attributes to keep the
query small.
ChapTer 3
The Query profiler