Completed the Preinstallation module

Completed the Install Oracle9i Database module

Completed the Postinstallation module

Documentation: Data Warehousing Guide

Create the necessary directory objects.

Create the external table.

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

@create_directory.sql

DROP DIRECTORY data_dir;
DROP DIRECTORY log_dir;
CREATE DIRECTORY data_dir AS 'D:\HandsOn_STUDENTS\RDBMS\FILES';
CREATE DIRECTORY log_dir AS 'D:\TEMP';

The metadata information for the table representation inside the database

The HOW access parameter definition to extract the data from the external file

From a SQL*Plus session logged on to the SH schema, run @create_external_table.sql. The results are as follows:

@create_external_table

set echo on
Rem *****
Rem CREATE EXTERNAL TABLE
Rem *****

CREATE TABLE sales_delta_xt 
  (
   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)
  )
  ORGANIZATION external 
  (
   TYPE oracle_loader
   DEFAULT DIRECTORY data_dir
   ACCESS PARAMETERS 
    (
     RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
     BADFILE log_dir:'sh_sales_%p.bad'
     LOGFILE log_dir:'sh_sales_%p.log_xt'
     FIELDS TERMINATED BY "|" LDRTRIM 
    )
   location 
    (
     'salesDelta.dat'
    )
   )REJECT LIMIT UNLIMITED NOPARALLEL;

Make sure the target file does not exist. Open a DOS prompt and navigate to the directory where your files are located and perform the following:

dir salesDelta.dat

If it exists, delete it.

Copy the source file by performing the following:

copy salesDec00.dat salesDelta.dat

You should now see the file:

dir salesDelta.dat

From a SQL*Plus session logged on to the SH schema, execute the following queries:

SELECT COUNT(*) FROM sales_delta_xt; 
SELECT MAX(time_id) FROM sales_delta_xt;        

If you copied the files correctly, the maximum TIME_ID is ‘30-DEC-00’.

Run the following query, from a SQL*Plus session logged on to the SH schema, to see the current parallel session statistics:

SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in ('Queries Parallelized', 
                     'Allocation Height');

Run the same query you did before to access the External Table with a parallel degree of 4, controlled with a hint. The select statement is:

select /*+ parallel(a,4) */ count(*) 
from sales_delta_XT a;

We’re selecting from our external table in parallel, although the external table points only to one input source file.

SELECT * 
FROM   v$pq_sesstat 
WHERE  statistic in 
       ('Queries Parallelized', 'Allocation Height');

To show the execution plan for SERIAL INSERT behavior, run show_serial_exec_plan.sql, or copy the following SQL statement into your SQL*Plus session:

@show_serial_exec_plan.sql

EXPLAIN PLAN FOR INSERT /*+ APPEND */ INTO sales
       (
       PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
       QUANTITY_SOLD, AMOUNT_SOLD
       )
       SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
              sum(QUANTITY_SOLD), sum(AMOUNT_SOLD)
       FROM   SALES_DELTA_XT
       GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

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

You can see that the 'LOAD AS SELECT' operation (shaded), representing the INSERT is done in serial.

To show the PARALLEL INSERT execution plan you will need to run show_parallel_exec_plan.sql logged in to the SH schema. A parallel DML must always be the first statement of a transaction. Furthermore, a DML operation cannot run in the presence of primary keyforeign key constraints. We therefore have to disable the constraints prior to the parallel DML operation:

@show_parallel_exec_plan.sql

ALTER TABLE sales DISABLE CONSTRAINT sales_product_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_customer_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_time_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_channel_fk;
ALTER TABLE sales DISABLE CONSTRAINT sales_promo_fk;

COMMIT;

alter session enable parallel dml;

EXPLAIN PLAN FOR INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
   (
     PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
     QUANTITY_SOLD, AMOUNT_SOLD
   )
   SELECT /*+ parallel (sales_delta_XT,4) */
          PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
          sum(QUANTITY_SOLD), sum(AMOUNT_SOLD)
   FROM   SALES_DELTA_XT
   GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

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

Unlike in the previous plan, the 'LOAD AS SELECT' operation is done in parallel.

You will now run the sql to perform a parallel insert. Run parallel_insert_file.sql or copy the following statement into your SQL*Plus session set timing on

@parallel_insert_file.sql

connect sh/sh@orcl.world

set timing on 
set echo on 

COLUMN statistic FORMAT a20

Rem ***** 
Rem PARALLEL INSERT 
Rem ***** 

INSERT /*+ APPEND PARALLEL(SALES,4) */ 
   INTO sales ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, 
                PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD ) 
   SELECT /*+ PARALLEL (sales_delta_XT,4) */ PROD_ID, CUST_ID, 
          TIME_ID, CHANNEL_ID, PROMO_ID, sum(QUANTITY_SOLD), 
          sum(AMOUNT_SOLD) 
   FROM   SALES_DELTA_XT 
   GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;  

set timing off  

Rem   we can control the successful parallel DML 

SELECT * 
FROM   v$pq_sesstat 
WHERE statistic in ('DML Parallelized','Allocation Height'); 

Write down the execution time of this statement and compare it to the total amount of time you will need with SQL*Loader and a subsequent insertion. Note that we won’t see the full benefit of parallelizing the external table access and combining the transformation with the loading, since we’re accessing a very small amount of data in parallel on a single CPU machine with one disk.

Row rollback. In the next lesson the same data is inserted using SQL*Loader. If you do not rollback now there will be duplicate rows in the table and other lessons will not work. You can also choose not to rollback and skip the SQL*Loader lesson.

Rollback;

if you plan to SKIP the SQL*Loader section, you should COMMIT your transaction now:

COMMIT;

You can now continue with the section ROLLING WINDOW OPERATIONS

After issuing a commit or rollback, we will enable the constraints again.

@enable_cons.sql
          
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_product_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_customer_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_time_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_channel_fk ENABLE NOVALIDATE; 
ALTER TABLE sales 
   MODIFY CONSTRAINT sales_promo_fk ENABLE NOVALIDATE;

Create a staging table.

Load the data into the staging table by using SQL*Loader.

You will now run the sql to create a staging table:

create table sales_dec00 as
   select * 
   from   sales 
   where 1=0;

You perform this operation from the OS command line, in the D:\wkdir directory..

sqlldr sh/sh@orcl.world control=sales_dec00.ctl direct=true



If your database alias is not orcl.world change the alias accordingly.

Note that you cannot parallelize this task. Check the SQL*Loader log file, D:\wkdir\sales_dec00.log. Write down the execution time for the loading process.

You will now run the SQL to transform and insert the external data, which is already staged in the database, into the sales fact table by issuing @load_stage_table.sql

@load_stage_table.sql

Rem Load from staging table into target 

set timing on

INSERT /*+ APPEND PARALLEL(SALES,4) */ INTO sales
   ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
   QUANTITY_SOLD, AMOUNT_SOLD
   )
   SELECT /*+ parallel (sales_dec00,4) */
          PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
          sum(QUANTITY_SOLD), sum(AMOUNT_SOLD)
   FROM   sales_dec00
   GROUP BY prod_id, time_id, cust_id, channel_id, promo_id;

COMMIT;

Write down the execution time of this statement and add the time for the loading with SQL*Loader. Compare the total amount of time you will need with the time for the external table insertion. Note that we won’t see the full benefit of parallelizing the external table access and combining the transformation with the loading, since we’re accessing a very small amount of data in parallel on a single CPU machine with one disk.

Create a link to the sales Q1 data.

Create the table for the new sales Q1 data.

Make sure the target file does not exist. Open a DOS prompt and navigate to the directory where your files are located (D:\wkdir) and perform the following:

dir salesDelta.dat

If it exists, delete it.

Copy the source file by performing the following:

copy salesQ1_01.dat salesDelta.dat

You should now see the file:

dir salesDelta.dat

Let’s query our external table again and compare the number of records and MAX(time_id):

SELECT COUNT(*) FROM sales_delta_xt;
SELECT MAX(time_id) FROM sales_delta_xt;

Note that the number os rows as well as the maximum TIME_ID has changed. If your external table file is correct, the maximum TIME_ID is ‘30-MAR-2001’.

Run the SQL to create the table:

create table sales_delta nologging as 
   select * 
   from   sales 
   where  1=0;

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@load_stage_table2.sql

INSERT /*+ APPEND */ INTO sales_delta
   SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */
          PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID,
          sum(QUANTITY_SOLD) quantity_sold,
          sum(AMOUNT_SOLD) amount_sold
   FROM   SALES_DELTA_XT
   GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

.From a SQL*Plus session logged on to the SH schema, execute the following statement:

@gather_stat_stage_table.sql

exec dbms_stats.gather_table_stats('SH', 'sales_delta', 
   estimate_percent=>20);

From a SQL*Plus session logged on to the SH schema, execute the following statements:

@create_static_bitmap_index.sql

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 ;

Note that the statistics for those indexes will be created as part of the index creation.

From a SQL*Plus session logged on to the SH schema, execute the following query:

@create_constraints.sql

set echo on
Rem *****
Rem CREATE CONSTRAINTS
Rem *****

ALTER TABLE sales_delta
  ADD ( CONSTRAINT sales_product_delta_fk
    FOREIGN KEY (prod_id) 
      REFERENCES products RELY ENABLE NOVALIDATE
    , CONSTRAINT sales_customer_delta_fk 
    FOREIGN KEY (cust_id)
      REFERENCES customers RELY ENABLE NOVALIDATE 
    , CONSTRAINT sales_time_delta_fk
    FOREIGN KEY (time_id)
      REFERENCES times RELY ENABLE NOVALIDATE
    , CONSTRAINT sales_channel_delta_fk
    FOREIGN KEY (channel_id)
      REFERENCES channels RELY ENABLE NOVALIDATE
    , CONSTRAINT sales_promo_delta_fk
    FOREIGN KEY (promo_id)
      REFERENCES promotions RELY ENABLE NOVALIDATE
   ) ;

Create a new partition, if one does not already exist.

Exchange the partition. This a data dictionary operation only and does not touch any data

From a SQL*Plus session logged on to the SH schema, execute the following query:

@create_partition_for_sales.sql

Rem *****
Rem current partitions
Rem *****
                 
select partition_name, high_value 
from   user_tab_partitions 
where  table_name='SALES'
order by partition_position;

Rem *****
Rem create ADDITIONAL PARTITION on sales
Rem *****

ALTER TABLE sales 
ADD PARTITION sales_q1_2001
VALUES LESS THAN (MAXVALUE);

Rem *****
Rem what is in the partition now?
Rem empty
Rem *****

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2001);

From a SQL*Plus session logged on to the SH schema, execute the following query:

@exchange_partition_wo_gim.sql

set echo on

Rem *****
Rem EXCHANGE IT
Rem *****

ALTER TABLE sales EXCHANGE PARTITION sales_q1_2001 
   WITH TABLE sales_delta INCLUDING INDEXES;

From a SQL*Plus session logged on to the SH schema, execute the following queries. This will show you the number of rows in the exchanged partition and - now empty - standalone table:

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2001);

SELECT COUNT(*) 
FROM   sales_delta;          

Note that all local indexes of table sales are valid.

@show_sales_idx_status.sql

SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, 
         DECODE(uip.status,null,ui.status,uip.status);

From a SQL*Plus session logged on to the SH schema, execute the following queries, @fast_split_sales.sql:

@fast_split_sales.sql

ALTER TABLE sales 
   SPLIT PARTITION sales_q1_2001 
      AT (TO_DATE('01-APR-2001', 'DD-MON-YYYY')) 
   INTO (PARTITION sales_q1_2001, 
         PARTITION sales_beyond_q1_2001);

PROMPT you should recognize that is was fast.
PROMPT Let's control the count in the most recent partition. Empty

SELECT COUNT(*) 
FROM   sales PARTITION (sales_beyond_q1_2001);

PROMPT now you can drop it

ALTER TABLE sales DROP PARTITION sales_beyond_q1_2001;

Note that all local indexes of table sales are still valid.

@show_sales_idx_status.sql

SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, 
         DECODE(uip.status,null,ui.status,uip.status);

Create an empty standalone table.

Create bitmap indexes for this table.

From a SQL*Plus session logged on to the SH schema, execute the following statements:

DROP TABLE sales_old_q1_1998;
create table sales_old_q1_1998 nologging as 
   select * 
   from   sales 
   where  1=0;

From a SQL*Plus session logged on to the SH schema, execute the following query:

@create_ndx.sql

set echo on

Rem *****
Rem CREATE LOCAL INDEXES
Rem *****

CREATE BITMAP INDEX sales_prod_old_bix
   ON sales_old_q1_1998 (prod_id)
   NOLOGGING COMPUTE STATISTICS ;

 CREATE BITMAP INDEX sales_cust_old_bix
   ON sales_old_q1_1998 (cust_id)
   NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_time_old_bix
   ON sales_old_q1_1998 (time_id)
   NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_channel_old_bix
   ON sales_old_q1_1998 (channel_id)
   NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_promo_old_bix
   ON sales_old_q1_1998 (promo_id)
   NOLOGGING COMPUTE STATISTICS ;

From a SQL*Plus session logged on to the SH schema, execute the following query:

@create_constraints_old.sql

set echo on 

Rem *****
Rem CREATE CONSTRAINTS
Rem *****

ALTER TABLE sales_old_q1_1998
  ADD ( CONSTRAINT sales_product_old_fk
        FOREIGN KEY (prod_id) 
        REFERENCES products RELY ENABLE NOVALIDATE
      , CONSTRAINT sales_customer_old_fk 
        FOREIGN KEY (cust_id)
        REFERENCES customers RELY ENABLE NOVALIDATE 
      , CONSTRAINT sales_time_old_fk
        FOREIGN KEY (time_id)
        REFERENCES times RELY ENABLE NOVALIDATE
      , CONSTRAINT sales_channel_old_fk
        FOREIGN KEY (channel_id)
        REFERENCES channels RELY ENABLE NOVALIDATE
      , CONSTRAINT sales_promo_old_fk
        FOREIGN KEY (promo_id)
        REFERENCES promotions RELY ENABLE NOVALIDATE
      ) ;

From a SQL*Plus session logged on to the SH schema, execute the following query:

@show_partition.sql

set echo on

Rem *****
Rem show the actual content of the partition to be aged out 
Rem BEFORE exchange
Rem *****
  
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

From a SQL*Plus session logged on to the SH schema, execute the following SQL statement:

@exchange_old_partition.sql

ALTER TABLE sales 
   EXCHANGE PARTITION sales_q1_1998 
   WITH TABLE sales_old_q1_1998
   INCLUDING INDEXES;

Note that we could have used a DROP PARTITION instead. Table sales_old_q1_1998 now stores all the data of the first quarter of 1998. We could drop this table to remove the data entirely from the system.

From a SQL*Plus session logged on to the SH schema, execute the following queries:

SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

SELECT COUNT(*) 
FROM   sales_old_q1_1998;
       

Unlike before the EXCHANGE command, the standalone table now stores thousands of rows whereas the equivalent partition of SALES is empty.

Local Indexes are not affected.

@show_sales_idx_status.sql

SELECT   ui.index_name,
         DECODE(uip.status,null,ui.status,uip.status) status, 
         count(*) num_of_part
FROM     user_ind_partitions uip, user_indexes ui
WHERE    ui.index_name=uip.index_name(+)
AND      ui.table_name='SALES'
GROUP BY ui.index_name, 
         DECODE(uip.status,null,ui.status,uip.status);

From a SQL*Plus session logged on to the SH schema, execute the following query:

@exchange_back_to_start.sql
       
set echo on 

Rem *****
Rem let's bring the data back in original state
Rem *****

Rem added partition data
      
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2001);
       
ALTER TABLE sales 
   EXCHANGE PARTITION sales_q1_2001 
   WITH TABLE sales_delta INCLUDING INDEXES;
       
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_2001);
       
Rem deleted partition data
       
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);
       
ALTER TABLE sales 
   EXCHANGE PARTITION sales_q1_1998 
   WITH TABLE sales_old_q1_1998 
   INCLUDING INDEXES;
       
SELECT COUNT(*) 
FROM   sales PARTITION (sales_q1_1998);

You will create a compressed table for the first six months of the 1998 data. To set up the table including its constraints, you can run create_comp_h1_1998_1.sql.

@create_comp_h1_1998_1.sql

DROP TABLE sales_1_1998_compress;

CREATE TABLE sales_1_1998_compress COMPRESS
AS SELECT * 
   FROM   sales 
   WHERE  time_id >= TO_DATE('01-JAN-1998','dd-mon-yyyy') 
   AND    time_id < TO_DATE('01-JUN-1998','dd-mon-yyyy');
   
exec dbms_stats.gather_table_stats('SH','sales_1_1998_compress', 
                                   estimate_percent=>10);

From a SQL*Plus session logged on to the SH schema, execute the following statements:

@compression_rate1.sql

 select segment_name||
        decode(partition_name,
               null,
               ': compressed table',
               ': partitions SALES_Q1_1998,SALES_Q2_1998') what, 
        sum(bytes) 
 from   user_segments 
 where  segment_name ='SALES_1_1998_COMPRESS' 
 or     ( segment_name='SALES' and 
          partition_name in ('SALES_Q1_1998','SALES_Q2_1998'))
 and    segment_type in('TABLE','TABLE PARTITION')
 group by segment_name||decode(partition_name,
                        null,
                        ': compressed table',
                        ': partitions SALES_Q1_1998,SALES_Q2_1998');

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@create_compress_constr.sql

ALTER TABLE sales_1_1998_compress
   ADD ( CONSTRAINT sales_product_fk_compress
         FOREIGN KEY (prod_id)
         REFERENCES products RELY ENABLE NOVALIDATE
        , CONSTRAINT sales_customer_fk_compress
          FOREIGN KEY (cust_id)
          REFERENCES customers RELY ENABLE NOVALIDATE
        , CONSTRAINT sales_time_fk_compress
          FOREIGN KEY (time_id)
          REFERENCES times RELY ENABLE NOVALIDATE
        , CONSTRAINT sales_channel_fk_compress
          FOREIGN KEY (channel_id)
          REFERENCES channels RELY ENABLE NOVALIDATE
        , CONSTRAINT sales_promo_fk_compress
          FOREIGN KEY (promo_id)
          REFERENCES promotions RELY ENABLE NOVALIDATE
        ) ;

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@prep_exchange_comp1.sql

PROMPT CURRENT PARTITIONS
Rem *****

SELECT   partition_name, high_value
FROM     user_tab_partitions
WHERE    table_name='SALES'
ORDER BY partition_position;

REM since we compressed data and merged two partitions, we have
REM to drop partition SALES_Q1_1998; this will bring down the 
REM lower boundary of the 'new' sales_q2_1998 to the upper 
REM boundary of sales_2_1997

ALTER TABLE sales DROP PARTITION sales_q1_1998;

PROMPT CURRENT PARTITIONS

SELECT   partition_name, high_value
FROM     user_tab_partitions
WHERE    table_name='SALES'
ORDER BY partition_position;

Create the new compressed standalone table containing data

Drop the existing bitmap index structures. From a SQL*Plus session logged on to the SH schema, execute the following statement:

@drop_bix_for_sales.sql

Rem usable bitmap indexes on a partitioned table before we can
Rem add a compressed partition
Rem ORA-14646: Specified alter table operation involving 
Rem compression cannot be performed in the presence of usable
Rem bitmap indexes

DROP INDEX sales_channel_bix ;
DROP INDEX sales_cust_bix ;
DROP INDEX sales_prod_bix ;
DROP INDEX sales_promo_bix ;
DROP INDEX sales_time_bix ;

You can now exchange the compressed table with the existing uncompressed partition.

In this case, exchange the former uncompressed partition with the compressed standalone table, having the same logical structure. This could also be an ALTER TABLE MOVE PARTITION command. We will demonstrate an ALTER TABLE MERGE command later

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@exchange_comp_part.sql

REM now exchange the newly created and compressed 
REM partition back ...

ALTER TABLE sales 
   EXCHANGE PARTITION sales_q2_1998 
   WITH TABLE sales_1_1998_compress;

REM rename it

ALTER TABLE sales RENAME 
   PARTITION sales_q2_1998 
   TO sales_H1_1998_compress;

REM  drop exchanged table

DROP TABLE sales_1_1998_compress;

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@show_comp_parts.sql

PROMPT control the compress attribute for the partitioned table

SELECT partition_name, compression 
FROM   user_tab_partitions 
WHERE  table_name='SALES' 
ORDER BY partition_position;

From a SQL*Plus session logged on to the SH schema, execute the following statement:

@recreate_bix_for_sales.sql

PROMPT recreate indexes for table SALES
CREATE BITMAP INDEX sales_prod_bix
   ON sales (prod_id) LOCAL
   NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_cust_bix
   ON sales (cust_id) LOCAL
   NOLOGGING COMPUTE STATISTICS ;