Overview

All relevant schema scripts can be found from a simple menu on my web site at the following URL, along with many other goodies including my resume:

<a class="url"> href="http://www.oracledbaexpert.com/oracle/OracleDataWarehouseTuning/index.html"> target="_top">http://www.oracledbaexpert.com/oracle/OracleDataWarehouseTuning/index.html</a>

I have also added datafiles for fast loading of a database on my web site. Datafiles are too large to include in this text.

C.1 EXPLAINP.SQL

<a name="1149"></a><a name="beginpage.9CEB75D9-FBEC-49EB-9109-AFE314561B57"></a>set linesize 132 wrap off echo off feedback off termout on;spool ./explainp.log;----explain plan set statement_id='TEST' for----COL Query FORMAT a64 WRAP ON;COL Pos FORMAT 990;COL Cost FORMAT 999999990;COL Rows FORMAT 99999999990;COL Bytes FORMAT 999999999990;COL Sort FORMAT 990;COL IO FORMAT 9990;COL CPU FORMAT 9990;--COL Cost FORMAT 990;--COL Rows FORMAT 99990;--COL Bytes FORMAT 999990;--COL Sort FORMAT 999990;SELECT  TRIM(LEVEL)'. 'LPAD (' ', LEVEL - 1)operation' 'options' on 'object_name' 'other_tag' 'TRIM(TO_CHAR(partition_start))' 'TRIM(TO_CHAR(partition_stop)) "Query"        ,cost "Cost"        ,cardinality "Rows"        ,bytes "Bytes"        --,search_columns "SC"--      ,decode(level,1,0,position) "Pos"--      ,temp_space "Sort"--      ,io_cost "IO"--      ,cpu_cost "CPU"FROM    plan_tableWHERE   statement_id = 'TEST'CONNECT BY prior id = parent_id AND prior statement_id = statement_idSTART WITH id = 0 AND statement_id = 'TEST'ORDER BY id;delete from plan_table where statement_id='TEST';commit;spool off;set echo on feedback on;

C.2 Create Tablespaces

<a name="1151"></a><a name="beginpage.2656214D-EF13-41D5-8903-63D4F846D6BB"></a><a name="1152"></a><a name="beginpage.9FD139A0-0440-410E-8D68-257B6C381BBA"></a>CONNECT SYS/password@DW AS SYSDBA----Primary tablespaces--CREATE TABLESPACE DATA DATAFILE 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\DWS\DATA01.DBF' SIZE 250M...