Completed the Preinstallation module

Completed the Install Oracle9i Database module

Completed the Postinstallation module

Documentation: Data Warehousing Guide

From a SQL*Plus session logged on to the SH schema, run show_star1.sql, or copy the following SQL statements into your SQL*Plus session:

The following ALTER SESSION command enables Oracle’s star query transformation without the use of temporary tables.

Beginning with Oracle8i, temporary tables might be used by the optimizer to further improve a star transformation. The next example will illustrate the difference between a true star transformation (introduced in Oracle8i) and the behavior shown here.

Note: The NOREWRITE hint is used to avoid any interference with possibly existing materialized views. Alternatively, you could disable query_rewrite for this particular session, or the complete instance. This is true for all subsequent statements.

The STAR_TRANSFORMATION hint is to enforce the ability within Oracle to use star transformation even with a small set of data, where another plan might be better.

@show_star1.sql

ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;
show parameters star_transformation

set sqlprompt "STAR - NO TEMP - SQL> "

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            t.calendar_month_desc
          , p.prod_subcategory
          , c.channel_desc
          , sum(s.amount_sold) AS dollars
   FROM     sales s
          , times t
          , products p
          , channels c
   WHERE    s.time_id = t.time_id
   AND      s.prod_id = p.prod_id
   AND      s.channel_id = c.channel_id
   AND      c.channel_desc in ('Internet','Catalog')
   AND      p.prod_category in ('Men')
   AND      t.fiscal_year in (2000,1999)
   GROUP BY   p.prod_subcategory, calendar_month_desc
            , c.channel_desc;

set linesize 132
select * from table(dbms_xplan.display);

Note: Disregard the PSTART and PSTOP columns in the output for the moment. They will be dicussed later.

The plan shown above represents a typical star query transformation. The records in the fact table, satisfying the query WHERE condition, are found by scanning only the bitmap index structures rather than the whole large sales fact table.

In a first, internal recursive step, the Oracle database selects all records of the three dimension tables (products, times, and channels), which satisfy the WHERE condition. You can see this table access of the dimension tables underneath the key iteration.

The Oracle database then uses the key values of these records as predicates for probing against the bitmap index structures of the sales fact table itself. You see that the predicates of the query - on the channels, products, and times dimension tables - are used for a high-selective preselection of only the relevant records of the table sales, using the bitmap index structures sales_prod_bix, sales_channel_bix, and sales_time_bix.

In a second step, the result set is joined back with all the dimension tables to get the final query result. These are all operations after PARTITION RANGE ITERATION.

From a SQL*Plus session logged on to the SH schema, run show_star2.sql, or copy the following SQL statements into your SQL*Plus session:

@show_star2.sql

ALTER SESSION SET star_transformation_enabled=TRUE;
show parameters star_transformation

set sqlprompt "STAR - SQL> "

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            t.calendar_month_desc
          , p.prod_subcategory
          , c.channel_desc
          , sum(s.amount_sold) AS dollars
   FROM     sales s
          , times t
          , products p
          , channels c
   WHERE  s.time_id = t.time_id
   AND    s.prod_id = p.prod_id
   AND    s.channel_id = c.channel_id
   AND    c.channel_desc in ('Internet','Catalog')
   AND    p.prod_category in ('Men')
   AND    t.fiscal_year in (2000,1999)
   GROUP BY   p.prod_subcategory, calendar_month_desc
            , c.channel_desc;

Rem show plan
set linesize 132
select * from table(dbms_xplan.display);

Rem explain usage of TEMP TABLES
set long 400
select other from plan_table where other is not null;

The following plan looks similar to the one shown before, although it is not identical. The difference between the plans is that the Oracle database now uses a table named ora_temp_1_4e (which is not part of the query) for satisfying the SQL statement. Note that this name is system-generated and will vary.

Note: The SELECT DISTINCT statement in the output should be disregarded and will be discussed later.

What Does This Mean?

The second output gives us an answer: The optimizer evaluated the selectivity of the WHERE conditions on the products, times, and channels dimension tables and the size of those dimension tables. It detected that there is a high selectivity on the products table and that this table is "large" enough. The costs for creating a temporary table, consisting of the result set for the predicate on the PRODUCTS table, is cheaper than accessing the PRODUCTS table twice, as in the first plan without temporary table usage. The temporary table is then used instead of the products table itself.

The creation of this temporary table and the data insertion are shown in the OTHER column of the plan table.

From a SQL*Plus session logged on to the SH schema, run show_star3.sql, or copy the following SQL statements into your SQL*Plus session. The STAR hint is used to enforce Oracle7 behavior.

@show_star3.sql
          
set sqlprompt "STAR - 7.x - SQL> "

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite star */
            t.calendar_month_desc
          , p.prod_subcategory
          , c.channel_desc
          , sum(s.amount_sold) AS dollars
   FROM   sales s, times t, products p, channels c
   WHERE  s.time_id = t.time_id
   AND    s.prod_id = p.prod_id
   AND    s.channel_id = c.channel_id
   AND    c.channel_desc in ('Internet','Catalog')
   AND    p.prod_category in ('Men')
   AND    t.fiscal_year in (2000,1999)
   GROUP BY   p.prod_subcategory, calendar_month_desc
            , c.channel_desc;

Rem show plan
set linesize 132
select * from table(dbms_xplan.display);

Here you see the so-called star query optimization, which was introduced in Oracle7. To avoid several joins with the large fact table, the optimizer builds a Cartesian product of the times, products, and channels dimension tables and joins the Cartesian result once with the sales fact table.

Note that this star optimization technique has nothing to do with the current star query transformation, and its usage is not dependent on the setting of the star_transformation_enabled parameter.

From a SQL*Plus session logged on to the SH schema, run show_star4.sql, or copy the following SQL statements into your SQL*Plus session:

@show_star4.sql

ALTER SESSION SET star_transformation_enabled=false;
set sqlprompt "no STAR - SQL> "

TRUNCATE TABLE plan_table;
EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */
            t.calendar_month_desc
          , p.prod_subcategory
          , c.channel_desc
          , sum(s.amount_sold) AS dollars
   FROM   sales s, times t, products p, channels c
   WHERE  s.time_id = t.time_id
   AND    s.prod_id = p.prod_id
   AND    s.channel_id = c.channel_id
   AND    c.channel_desc in ('Internet','Catalog')
   AND    p.prod_category in ('Men')
   AND    t.fiscal_year in (2000,1999)
   GROUP BY   p.prod_subcategory, calendar_month_desc
            , c.channel_desc;

Rem show plan
set linesize 132
select * from table(dbms_xplan.display);

From a join perspective, this plan has no optimizations for data warehousing. Subsequently, it joins the four tables.

From a SQL*Plus session logged on to the SH schema, run reset_star_test.sql, or copy the following SQL statements into your SQL*Plus session:

This will reset the session environment to our initial setting with an enabled star query
transformation, the recommended setting for a data warehousing environment.

@reset_star_test.sql

Rem BACK TO NORMALITY
alter session set star_transformation_enabled=TRUE;
set sqlprompt "SQL> "

Create a bitmap join index on fact table sales , for a joined attribute from the channels dimension table. From a SQL*Plus session logged on to the SH schema, run cr_bj_idx.sql, or copy the following SQL statements into your SQL*Plus session:

@cr_bj_idx.sql

CREATE BITMAP INDEX bji_sales_prod_cat
  ON    sales(p.prod_category)
  FROM  sales s, products p
  WHERE s.prod_id = p.prod_id
  LOCAL NOLOGGING COMPUTE STATISTICS;

Look at the execution plan of your query again and compare it with the original star transformation plan in the previous section. For the sake of simplicity, the usage of temp tables for star transformation is disabled.

@plan_bj_idx.sql

AALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT   /*+ norewrite */ 
            p.prod_subcategory, c.channel_desc, 
            sum(s.amount_sold) AS dollars
   FROM     sales s, times t, products p, channels c
   WHERE    s.time_id = t.time_id
   AND      s.prod_id = p.prod_id
   AND      s.channel_id = c.channel_id
   AND      c.channel_desc in ('Internet','Catalog')
   AND      p.prod_category in ('Men')
   AND      t.fiscal_year in (2000,1999)
   GROUP BY p.prod_subcategory, c.channel_desc;

Rem show plan
set linesize 132
select * from table(dbms_xplan.display);

ALTER SESSION SET star_transformation_enabled=TRUE;

When comparing this plan with the equivalent star transformation plan, you can see that now you don’t have to query the dimension table channels for probing the bitmap index on the sales fact table. The predicate in the query is on the channel_desc column, which is stored in the bitmap join index, thus making the join no longer necessary.

Another benefit of the bitmap join index is the CPU (and IO) savings gained by the removal of the bitmap key iteration work (which has to merge all of the bitmaps together).

From a SQL*Plus session logged on to the SH schema, run create_stage_table.sql, or copy the following SQL statements into your SQL*Plus session:

@create_stage_table.sql

DROP TABLE sales_delta;

CREATE TABLE sales_delta 
   NOLOGGING AS 
   SELECT * 
   FROM   sales 
   WHERE  1=0;

Fill the table with some data from the sales fact table.

@load_stage_table3.sql


INSERT /*+ APPEND */ INTO sales_delta
   SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
          PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
   FROM   sales PARTITION ( sales_q1_2000)
;

COMMIT;

Create B-tree indexes on all dimension join columns. Note the time needed for creation.

@cr_btree_idx.sql

set timing on
CREATE INDEX sales_prod_local_bix
   ON sales_delta (prod_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_cust_local_bix
   ON sales_delta (cust_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_time_local_bix
   ON sales_delta (time_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_channel_local_bix
   ON sales_delta (channel_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_promo_local_bix
   ON sales_delta (promo_id)
   NOLOGGING COMPUTE STATISTICS ;

On your system, you can see that you needed 3-5 second to create the B-tree indexes.

Use SQL to store the SIZES in a table for a direct comparison. To do this, select the actual segment sizes of the B-tree indexes from the data dictionary.

@cr_compare_tab.sql

DROP TABLE compare_idx_size;

CREATE TABLE compare_idx_size
   AS
   SELECT   segment_name index_name,'STANDARD BTREE' index_type,
            sum(bytes)/(1024*1024) index_size
   FROM     user_segments us, user_indexes ui
   WHERE    us.segment_name=ui.index_name
   AND      ui.table_name='SALES_DELTA'
   GROUP BY segment_name, index_type;

Determine what the size of the indexes would be if you had used static bitmap indexes. First you have to clean up the environment by running cleanup_idx_comp.sql.

@cleanup_idx_comp.sql           

DROP INDEX sales_prod_local_bix;
DROP INDEX sales_cust_local_bix;
DROP INDEX sales_time_local_bix;
DROP INDEX sales_channel_local_bix;
DROP INDEX sales_promo_local_bix;

Create static bitmap indexes on all dimension join columns. Note the time needed for creation; you will recognize the faster creation, compared to that of B-tree indexes.

@cr_bitmap_idx.sql

Set timing on
CREATE BITMAP INDEX sales_prod_local_bix
   ON sales_delta (prod_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_cust_local_bix
   ON sales_delta (cust_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_time_local_bix
   ON sales_delta (time_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_channel_local_bix
   ON sales_delta (channel_id)
   NOLOGGING COMPUTE STATISTICS ;
CREATE BITMAP INDEX sales_promo_local_bix
   ON sales_delta (promo_id)
NOLOGGING COMPUTE STATISTICS ;

Complete the comparison table, so that you can use SQL to investigate the differences between B-tree and bitmap indexes.

@fill_comp_table.sql

INSERT INTO compare_idx_size
   SELECT   segment_name index_name, 'BITMAP',sum(bytes)/(1024*1024)
   FROM     user_segments us, user_indexes ui
   WHERE    us.segment_name=ui.index_name 
   AND      ui.table_name='SALES_DELTA'
   GROUP BY segment_name;

COMMIT;

Compare the different sizes of those indexes. To show all the facts, you can run comp_idx1.sql as user SH.

@comp_idx1.sql

select substr(a.index_name,1,23) "Index Name",
       a.index_type "Index Type",
       a.index_size "Size [MB]",
       b.index_type "Index Type",
       b.index_size "Size [MB]",
       'btree ' || trunc((b.index_size/a.index_size),2) ||
       ' times bigger than bitmap' "btree X times larger"
FROM   compare_idx_size a, compare_idx_size b
WHERE  a.index_name=b.index_name
AND    a.index_type='BITMAP'
AND    b.index_type='STANDARD BTREE'
ORDER BY 6 asc;

For a shorter version, run the following:

@comp_idx2.sql


SELECT substr(a.index_name,1,23) "Index Name",
       'btree ' || trunc((b.index_size/a.index_size),2) || 
       ' times bigger than bitmap' "btree X times larger"
FROM   compare_idx_size a, compare_idx_size b
WHERE  a.index_name=b.index_name 
AND    a.index_type='BITMAP' 
AND    b.index_type='STANDARD BTREE' 
ORDER BY 2 asc;

Clean up the environment.

@cleanup_idx_test.sql

DROP TABLE compare_idx_size;
DROP TABLE sales_delta;

Partitionwise Joins

Dynamic partition pruning takes place at query run time, when the following criteria are satisfied:

• A predicate condition on a table that is joined to the partitioned object on the partition key column(s)
• A sufficient selectivity for the predicate - and therefore for the partition pruning - to make the additional recursive SQL cheaper than processing the query without dynamic partition pruning

From a SQL*Plus session logged on to the SH schema, run plan_static_pruning.sql, or copy the following SQL statements into your SQL*Plus session:

@plan_static_pruning.sql

truncate table plan_table;

EXAPLIN PLAN FOR
   SELECT c.channel_desc, sum(amount_sold)
   FROM   sales s, channels c
   WHERE  s.channel_id = c.channel_id
   AND    s.time_id >= to_date('01-JAN-2000','DD-MON-YYYY')
   AND    s.time_id <= to_date('31-MAR-2000','DD-MON-YYYY')
   GROUP BY channel_desc;

Rem show plan
set linesize 132
select * from table(dbms_xplan.display);

Look at the PSTART and PSTOP column to see information about partition pruning. You see that the query has to access only one partition for getting the result, namely partition # 13.

To get more information about the partition that must be accessed, you can query the data dictionary with the appropriate partition number.

@select_part_name.sql

SELECT partition_name
FROM   user_tab_partitions 
WHERE  table_name='SALES' 
AND    partition_position=13;

From a SQL*Plus session logged on to the SH schema, run plan_dyn_pruning.sql, or copy the following SQL statements into your SQL*Plus session:

@plan_dyn_pruning.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR 
   SELECT t.day_number_in_month, sum(s.amount_sold)
   FROM   sales s, times t
   WHERE  s.time_id = t.time_id
   AND    t.calendar_month_desc='2000-12'
   GROUP BY t.day_number_in_month;

set linesize 132
Rem show the plan with dynamic partition pruning
select * from table(dbms_xplan.display);

Unlike with static partition pruning, you will not see any absolute numbers in the PSTART and PSTOP column, but the word KEY. This indicates that dynamic partition pruning will take place for this query at run time.

To show the recursive SQL statement that is issued for dynamic partition pruning, run the following query against the plan_table:

@select_other

set long 400

SELECT other 
FROM   plan_table 
WHERE  other IS NOT NULL;

You can see how the Oracle database translates the original predicate condition into an inline view for getting the appropriate partition information. The output is shown in the screenshot above

Import statistics for those two tables.

Experience serial partitionwise joins:

• Non-partitionwise join
• Full partitionwise join

Experience parallel partitionwise joins:

• Non-partitionwise join
• Full partitionwise join
• Partial partitionwise join

From a SQL*Plus session logged on to the SH schema, run the following SQL statements in your SQL*Plus session to create the additional structures:

Create a hash-partitioned table with the same structure as the customers dimension table.

@create_cust_hash.sql

DROP TABLE customers_hash;

CREATE TABLE customers_hash
   (   cust_id NUMBER
     , cust_first_name VARCHAR2(20)
     , cust_last_name VARCHAR2(40)
     , cust_gender CHAR(1)
     , cust_year_of_birth NUMBER(4)
     , cust_marital_status VARCHAR2(20)
     , cust_street_address VARCHAR2(40)
     , cust_postal_code VARCHAR2(10)
     , cust_city VARCHAR2(30)
     , cust_state_province VARCHAR2(40)
     , country_id CHAR(2)
     , cust_main_phone_number VARCHAR2(25)
     , cust_income_level VARCHAR2(30)
     , cust_credit_limit NUMBER
     , cust_email VARCHAR2(30)
   )
   PCTFREE 5
   PARTITION BY HASH (cust_id)
      (PARTITION h1_cust, PARTITION h2_cust, PARTITION h3_cust, 
       PARTITION h4_cust, PARTITION h5_cust, PARTITION h6_cust, 
       PARTITION h7_cust, PARTITION h8_cust, PARTITION h9_cust, 
       PARTITION h10_cust,PARTITION h11_cust,PARTITION h12_cust,
       PARTITION h13_cust,PARTITION h14_cust,PARTITION h15_cust,
       PARTITION h16_cust);

CREATE UNIQUE INDEX customers_hash_pk
   ON customers_hash (cust_id) ;

ALTER TABLE customers_hash
   ADD ( CONSTRAINT customers_hash_pk
         PRIMARY KEY (cust_id) RELY ENABLE VALIDATE
       ) ;

Create a composite range-hash-partitioned table with the same structure as the sales fact table.

@create_sales_hash.sql

DROP TABLE sales_hash;

CREATE TABLE sales_hash
   (   prod_id NUMBER(6)
     , cust_id NUMBER
     , time_id DATE
     , channel_id CHAR(1)
     , promo_id NUMBER(6)
     , quantity_sold NUMBER(3)
     , amount_sold NUMBER(10,2)
   ) PCTFREE 5 NOLOGGING
   PARTITION BY RANGE (time_id)
      SUBPARTITION BY HASH (cust_id)
      SUBPARTITIONS 16
      ( PARTITION SALES_HASH_1995
           VALUES LESS THAN (TO_DATE('01-JAN-1996','DD-MON-YYYY')),
        PARTITION SALES_HASH_1996
           VALUES LESS THAN (TO_DATE('01-JAN-1997','DD-MON-YYYY')),
        PARTITION SALES_HASH_1_1997
           VALUES LESS THAN (TO_DATE('01-JUL-1997','DD-MON-YYYY')),
        PARTITION SALES_HASH_2_1997
           VALUES LESS THAN (TO_DATE('01-JAN-1998','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q1_1998
           VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q2_1998
           VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q3_1998
           VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q4_1998
           VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q1_1999
           VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q2_1999
           VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q3_1999
           VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q4_1999
           VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q1_2000
           VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q2_2000
           VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q3_2000
           VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
        PARTITION SALES_HASH_Q4_2000
           VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
);

CREATE BITMAP INDEX sales_cust_hash_bix
   ON sales_hash (cust_id)
   LOCAL NOLOGGING;

You haven’t gathered any statistics for the two new objects yet:

@show_tab_stats.sql

SELECT table_name, num_rows 
FROM user_tables
WHERE  table_name in ('SALES_HASH','CUSTOMERS_HASH');

@imp_tab_stats.sql

Rem import those statistics from STAT_TABLE
Rem file stat_table.dmp, must be imported at the beginning
exec dbms_stats.import_table_stats('sh','sales_hash',
            stattab =>'stat_table', statid =>'HANDS_ON');

exec dbms_stats.import_table_stats('sh','customers_hash',
            stattab =>'stat_table', statid =>'HANDS_ON')

@show_tab_stats.sql


SELECT table_name, num_rows 
FROM   user_tables
WHERE  table_name IN ('SALES_HASH','CUSTOMERS_HASH');

From a SQL*Plus session logged on to the SH schema, run set_noparallel.sql, or copy the following SQL statements into your SQL*Plus session to ensure that you get serial execution plans:

@set_noparallel

ALTER TABLE sales NOPARALLEL;
ALTER TABLE sales_hash NOPARALLEL;
ALTER TABLE customers NOPARALLEL;
ALTER TABLE customers_hash NOPARALLEL;

From a SQL*Plus session logged on to the SH schema, run serial_nopwj.sql, or copy the following SQL statements into your SQL*Plus session:

@serial_nopwj.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ cust_last_name, sum(amount_sold)
   FROM   sales s, customers c
   WHERE  s.cust_id = c.cust_id
   GROUP BY cust_last_name;

set linesize 132
Rem show the plan with non partition-wise join
select * from table(dbms_xplan.display);

The plan shows full table access for both tables, sales and customers, and a hash join.

From a SQL*Plus session logged on to the SH schema, run serial_pwj.sql, or copy the following SQL statements into your SQL*Plus session:

@serial_pwj.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ cust_last_name, sum(amount_sold)
   FROM   sales_hash s, customers_hash c
   WHERE  s.cust_id = c.cust_id
   GROUP BY cust_last_name;

set linesize 132
Rem show the plan with full partition-wise join
select * from table(dbms_xplan.display);

The plan looks slightly different than the non-partitioned plan. You see an additional operation, ‘PARTITION RANGE ALL’, in the plan. You can read this operation as specifying how to process the hash-join, or, in pseudocode:

FOR LOOP over all partitions of customers_hash
DO 
  hash-join equivalent partitions 
DONE 

This full partitionwise join is done in serial.

From a SQL*Plus session logged on to the SH schema, run set_parallel4.sql, or copy the following SQL statements into your SQL*Plus session to ensure that you get serial execution plans:

@set_parallel4.sql

ALTER TABLE sales PARALLEL 4;
ALTER TABLE sales_hash PARALLEL 4;
ALTER TABLE customers PARALLEL 4;
ALTER TABLE customers_hash PARALLEL 4;

From a SQL*Plus session logged on to the SH schema, run par_nopwj.sql, or copy the following SQL statements into your SQL*Plus session:

@par_nopwj.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ cust_last_name, sum(amount_sold)
   FROM   sales s, customers c
   WHERE s.cust_id = c.cust_id
   GROUP BY cust_last_name;

set linesize 132
Rem show the plan with non partition-wise join
select * from table(dbms_xplan.display);

You can see that the statement is executed in parallel; because there is no existing physical partitioning of one of the tables which could be used for this query, no partitionwise join takes place.

Both tables are scanned in parallel and redistributed to the next set of slaves based on a hash distribution; those slaves are executing the HASH JOIN and the first SORT GROUP BY (TQ 0,02), before they redistribute their result sets to the next set of slaves for the final SORT GROUP BY operation.

If the query contains an additional ORDER BY, you could see a range-based redistribution for feeding the last SORT GROUP BY (PQ Distribution method of TQ 0,02).

1.

From a SQL*Plus session logged on to the SH schema, run par_fullpwj.sql, or copy the following SQL statements into your SQL*Plus session:

@par_fullpwj.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ cust_last_name, sum(amount_sold)
   FROM   sales_hash s, customers_hash c
   WHERE  s.cust_id = c.cust_id
   GROUP BY cust_last_name;

set linesize 132
Rem show the plan with full partition-wise join
select * from table(dbms_xplan.display);

You see the same ‘PARTITION HASH ALL’ operation you know from a serial full partitionwise join.

Furthermore, this plan shows one of the benefits of partitionwise joins: When executed in parallel, partitionwise joins reduce communications overhead. The Oracle database can avoid redistributing the partitions because the two tables are already partitioned on the join column. This enables each parallel execution server to join a pair of matching partitions.

You can see that the same slave set (TQ 1,00), which is doing the first SORT GROUP BY operation, is also doing the table scans and the hash-join operation for a partition pair (PQ Distribution method Parallel Combined With Parent, PCWP). There is no data redistribution up to this point in the execution plan.

1.

From a SQL*Plus session logged on to the SH schema, run par_partpwj.sql, or copy the following SQL statements into your SQL*Plus session:

@par_partpwj.sql

TRUNCATE TABLE plan_table;

EXPLAIN PLAN FOR
   SELECT /*+ norewrite */ cust_last_name, sum(amount_sold)
   FROM   sales_hash s, customers c
   WHERE  s.cust_id = c.cust_id
   GROUP BY cust_last_name;

set linesize 132
Rem show the plan with partial partition-wise join
select * from table(dbms_xplan.display);

You can see that customers table is dynamically redistributed based on the join key column, cust_id, in the same way that the sales_hash table is subpartitioned on this column.

This is shown in the fact that the result sets of the parallel table scan of the customers table are fed to the HASH JOIN operation (TQ 2,01); the same slave set that does the parallel (partitionwise) scan of the composite partitioned table, sales_hash.

The hash partitions for the sales_hash table are composed of a set of 16 subpartitions, one from each range partition.

1.

From a SQL*Plus session logged on to the SH schema, run cleanup_mod3.sql, or copy the following SQL statements into your SQL*Plus session:

@cleanup_mod3.sql

ALTER TABLE sales NOPARALLEL;
ALTER TABLE sales_hash NOPARALLEL;
ALTER TABLE customers NOPARALLEL;
ALTER TABLE customers_hash NOPARALLEL;

DROP  TABLE customers_hash;
DROP  TABLE sales_hash;

SET SERVEROUTPUT ON
EXEC dw_handsOn.cleanup_modules

Do you currently have any table with monitoring enabled?

From a SQL*Plus session logged on to the SH schema, run tab_status_mon.sql, or copy the following SQL statements into your SQL*Plus session:

@tab_status_mon.sql

Rem actual tables and monitoring status
SELECT table_name, 
       to_char(last_analyzed,'dd.mm.yy hh24:mi:ss') la, 
       num_rows, monitoring 
FROM   user_tables;

Oracle9i provides a procedural interface that you use to enable or disable monitoring for a schema or a complete database.

@enable_monitoring.sql

Rem enable or disable monitoring for a complete schema
exec dbms_stats.alter_schema_tab_monitoring('SH',FALSE);
exec dbms_stats.alter_schema_tab_monitoring('SH',TRUE);

The user_tab_modifications data dictionary view lists all changes to your table with monitoring enabled.

@show_tab_modifications.sql

SELECT * 
FROM   user_tab_modifications;

From a SQL*Plus session logged on to the SH schema, run gather_auto.sql, or copy the following SQL statements into your SQL*Plus session:

@gather_auto.sql

set serveroutput on
declare
   list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();
begin
   dbms_output.enable(200000);
   dbms_stats.gather_schema_stats('SH',options=>'GATHER AUTO',
      objlist=>list_of_objects);
   for i in 1 ..list_of_objects.count loop
   dbms_output.put_line('updated:'||list_of_objects(i).objtype||' '||
   list_of_objects(i).objname||' '||list_of_objects(i).partname);
   end loop;
end;
/

Depending on the number of objects without any statistics you might have left over from previous modules, this might take up to a couple of minutes.

Note that we’re only passing the schema name and the request for automatic gathering of statistics to Oracle. All other parameters are derived internally. You might be astonished to see what statistics you’re missing …

Perform DML on one of the tables.

@stat_dml1.sql

Rem some DML
Rem 499 rows
update products 
   set prod_min_price=prod_min_price-1 
   where prod_id < 2500;
   
commit;

You are modifying less than 10% of the data, so that the threshold for gathering new statistics has not been reached. We will experience this in a minute.

Take a look at what in user_tab_modifications now. The information about table modifications is flushed periodically from the SGA unless manually requested. You won’t see any changes yet (unless you’re