page60
42
SET tpc_warehouse_M = 'tpc_wh_medium';
SET tpc_warehouse_L = 'tpc_wh_large';
SET tpc_warehouse_XL = 'tpc_wh_xlarge';
SET tpc_database = 'tpc';
SET tpc_owner_schema = 'tpc.tpc_owner';
You can use the sysadmin role to create first-order database objects such as
databases, schemas, warehouses, and shares.
Create a database called TPC and a schema within TPC called
tpc_owner
.
USE ROLE sysadmin;
CREATE OR REPLACE DATABASE IDENTIFIER ( $tpc_database ) DATA_RETENTION_
TIME_IN_DAYS = 90;
CREATE OR REPLACE SCHEMA IDENTIFIER ( $tpc_owner_schema );
Now create five warehouses of increasing size up to XL. You could add larger
warehouses from 2XL up to 6XL, but these five declared warehouses are sufficient for our
purposes right now.
Remember, unless explicitly declared with
"INITIALLY_SUSPENDED" = TRUE
,
warehouses run when they are declared and run when invoked to process a query.
For the warehouse declarations, set the default clustering to 1. I will explain
warehouse clustering later as the subject is worthy of its own chapter.
CREATE OR REPLACE WAREHOUSE IDENTIFIER ( $tpc_warehouse_XS ) WITH
WAREHOUSE_SIZE = 'X-SMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
SCALING_POLICY = 'STANDARD'
INITIALLY_SUSPENDED = TRUE;
CREATE OR REPLACE WAREHOUSE IDENTIFIER ( $tpc_warehouse_S ) WITH
WAREHOUSE_SIZE = 'SMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE
MIN_CLUSTER_COUNT = 1
ChapTer 3
The Query profiler