We saw in Chapter 2 how to create our database using the GUI tools, but many readers may prefer to create the database directly from SQL. The SQL to achieve this is shown below, but it assumes that the database has already been created.
The example shown here has been created for a Windows system, a simple edit of the file specs is all that is required for a different platform. Also note that the file sizes here are very small compared to what you would use in a production environment.
The first step is to connect to the database using a powerful user name.
connect system/manager
The next step is to create the tablespaces where the data will reside and their associated data files.
-- Temporary Tablespace CREATE TEMPORARY TABLESPACE easy_temp TEMPFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWeasy_temp.f' SIZE 10m REUSE AUTOEXTEND ON NEXT 16k ; -- Tablespace to store Materialized Views CREATE TABLESPACE mview DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWeasy_mview.f' SIZE 6m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED); -- Tablespace for Dimensions CREATE TABLESPACE easy_dim DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWdimensions.f' SIZE 5m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED); -- Tablespace for the INDEXES CREATE TABLESPACE easy_idx DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDWindex.f' SIZE 5m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED); -- Default Tablespace CREATE TABLESPACE easydw_default DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW easydw_default.f' SIZE 5m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);
Once the tablespaces have been created for the dimensions, we can now create the tablespaces for the fact table, PURCHASES. Since we will be partitioning the data, we must now create the tablespace for each partition. There will be one partition per month for the data and another partition for the indexes. Here we will create only the January partition for the data and index; simply repeat this process for the other partitions.
-- create the 3 month tablespaces for the fact partitions CREATE TABLESPACE purchases_jan2003 DATAFILE 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW PURCHASESJAN2003.f' SIZE 5m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED); -- create the 3 month tablespaces for the fact indexes CREATE TABLESPACE purchases_jan2003_idx datafile 'D:ORACLEPRODUCT10.1.0ORADATAEASYDW PURCHASESJAN2003_IDX.f' SIZE 3m REUSE AUTOEXTEND ON DEFAULT STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0 MAXEXTENTS UNLIMITED);
Once the tablespaces have been defined, the EASYDW user can be created, which will create that schema where the data will be stored.
-- create a user called EASYDW -- this will be the schema where the objects will reside connect system/manager CREATE USER easydw IDENTIFIED BY easydw DEFAULT TABLESPACE easydw_default TEMPORARY TABLESPACE temp PROFILE DEFAULT ACCOUNT UNLOCK; GRANT unlimited tablespace TO easydw ; GRANT dba TO easydw ; GRANT create session TO easydw;
The DBA privilege has been granted to the user so they can create and manage the tables and indexes.
Don’t forget to connect as user EASYDW before creating the tables and indexes, or the tables and indexes will be defined in the wrong schema.
-- now create the tables CONNECT easydw/easydw -- CUSTOMER Dimension CREATE TABLE easydw.customer (customer_id varchar2(10), city varchar2(15), state varchar2(10), postal_code varchar2(10), gender varchar2(1), region varchar2(15), country varchar2(20), tax_rate number, occupation varchar2(15)) PCTFREE 0 PCTUSED 99 TABLESPACE easy_dim STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ; ALTER TABLE customer ADD CONSTRAINT pk_customer PRIMARY KEY (customer_id) USING INDEX PCTFREE 5 TABLESPACE indx STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
We have defined the constraint here by adding it via the ALTER TABLE command. The constraint will use an index with the parameters that we have specified and the index will inherit the constraint name, PK_CUSTOMER.
-- PRODUCT Dimension CREATE TABLE easydw.product (product_id varchar2(8), product_name varchar2(30), category varchar2(4), cost_price number (6,2) constraint cost_price_not_null NOT NULL, sell_price number (6,2) constraint sell_price_not_null NOT NULL, weight number (6,2), shipping_charge number (5,2) constraint shipping_charge_not_null NOT NULL, manufacturer varchar2(20), supplier varchar2(10)) PCTFREE 0 PCTUSED 99 TABLESPACE easy_dim STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ; ALTER TABLE product ADD CONSTRAINT pk_product PRIMARY KEY (product_id) USING INDEX PCTFREE 5 TABLESPACE easy_idx STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ; -- TIME Dimension CREATE TABLE easydw.time (time_key date, month number (6,0), month_name varchar2(10), quarter number (6,0), year number (4,0), day_number number (3,0), day_of_the_week varchar2(9), week_number number (2,0) ) PCTFREE 0 PCTUSED 99 TABLESPACE easy_dim STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ; ALTER TABLE time ADD CONSTRAINT pk_time PRIMARY KEY (time_key) USING INDEX PCTFREE 5 TABLESPACE easy_idx STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ; -- TODAYS_SPECIAL_OFFERS Dimension CREATE TABLE easydw.todays_special_offers (product_id varchar2(8), offer_date date, special_price number (6,2), offer_price number (6,2)) PCTFREE 0 PCTUSED 99 TABLESPACE easy_dim STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
For the TODAYS_SPECIAL_OFFERS table, we have defined the primary key to include two columns rather than a single column.
ALTER TABLE todays_special_offers ADD CONSTRAINT pk_specials PRIMARY KEY (offer_date,product_id ) USING INDEX PCTFREE 5 TABLESPACE easy_idx STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) ;
Now we come to creating the all-important fact table, which is called PURCHASES. This table definition is quite complex because it includes FOREIGN KEYS to several tables that are identified by the REFERENCES clause. For the column product_id, two constraints have been defined on the table, a NOT NULL and a Foreign key constraint. Provided each constraint is given a unique name, then is allowed on a column in a table.
Here we have also illustrated how to partition the table, which was discussed in detail in Chapter 4. In this SQL statement example, we are only creating the table with three partitions though the table will actually have 24 partitions for the two years of data that it contains.
-- Fact Table PURCHASES CREATE TABLE easydw.purchases (product_id varchar2(8) CONSTRAINT not_null_product_id NOT NULL CONSTRAINT fk_product_id REFERENCES product(product_id), time_key date CONSTRAINT not_null_time NOT NULL CONSTRAINT fk_time REFERENCES time(time_key), customer_id varchar2(10) CONSTRAINT not_null_customer_id NOT NULL CONSTRAINT fk_customer_id REFERENCES customer(customer_id), ship_date date, purchase_price number(6,2), shipping_charge number(5,2), today_special_offer varchar2(1) CONSTRAINT special_offer CHECK (today_special_offer IN ('Y','N')) ) PARTITION BY RANGE (time_key ) ( PARTITION purchases_jan2002 VALUES LESS THAN (TO_DATE('01-02-2002', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE purchases_jan2002 , PARTITION purchases_feb2002 VALUES LESS THAN (TO_DATE('01-03-2002', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE purchases_feb2002 , PARTITION purchases_mar2002 VALUES LESS THAN (TO_DATE('01-04-2002', 'DD-MM-YYYY')) PCTFREE 0 PCTUSED 99 STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0) TABLESPACE purchases_mar2002 );
In this example, we have created the indexes immediately after the table definition. In a real data warehouse, the number of indexes created prior to loading the data is kept to an absolute minimum to ensure that the loading time is as fast as possible. Therefore, indexes exist prior to loading usually only to enable constraints to be executed as efficiently as possible.
-- Now create the indexes -- Partition on the Time Key Local prefixed index CREATE BITMAP INDEX easydw.purchase_time_index ON purchases (time_key ) LOCAL PCTFREE 5 TABLESPACE indx STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0); CREATE BITMAP INDEX easydw.purchase_product_index ON purchases (product_id ) LOCAL PCTFREE 5 TABLESPACE indx STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ; CREATE INDEX easydw.purchase_customer_index ON purchases (customer_id ) LOCAL PCTFREE 5 TABLESPACE indx STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ; CREATE BITMAP INDEX easydw.purchase_special_index ON purchases (today_special_offer ) LOCAL PCTFREE 5 TABLESPACE indx STORAGE (INITIAL 64k NEXT 64k PCTINCREASE 0) ;
The next step is to grant some privileges to our user, EASYDW. We will start with the following ones, which will allow us to use summary management, and, as we progress through this book, we will discuss other privileges that should be granted to users.
connect system/manager -- Add privileges GRANT SELECT ANY TABLE TO easydw; GRANT EXECUTE ANY PROCEDURE TO easydw; -- Add privileges for summary management GRANT CREATE ANY DIMENSION TO easydw; GRANT ALTER ANY DIMENSION TO easydw; GRANT DROP ANY DIMENSION TO easydw; GRANT CREATE ANY MATERIALIZED VIEW TO easydw; GRANT ALTER ANY MATERIALIZED VIEW TO easydw; GRANT DROP ANY MATERIALIZED VIEW TO easydw; GRANT QUERY REWRITE TO easydw; GRANT GLOBAL QUERY REWRITE TO easydw;
You will have to repeat these steps for every user that has been created and the privileges granted will, of course, vary by user.
The final step is actually not completed now, but after the data is loaded. However, it is included here to remind you not to forget this important step, which is to analyze the table and indexes. These statistics are gathered using the package DBMS_STATS and are used by the optimizer. Without these statistics, features like Summary Management will not operate to provide the most efficient access so query performance will suffer. The DBMS_STATS command was explained in Chapters 10 and 12.
-- Now Analyze the Tables and Indexes EXECUTE dbms_stats.gather_table_stats ('EASYDW','CUSTOMER'), EXECUTE dbms_stats.gather_table_stats ('EASYDW','TODAYS_SPECIAL_OFFERS'), EXECUTE dbms_stats.gather_table_stats ('EASYDW','PRODUCT'), EXECUTE dbms_stats.gather_index_stats ('EASYDW','PURCHASE_CUSTOMER_INDEX'),