Install Oracle Database 11g.

Create a directory named wkdir. Download and unzip etl.zip into the wkdir directory.

In a SQL*Plus session logged in as the SH user, execute the following queries or the select_et.sql file:

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 the last day of December, 2001.

The parallel_select_from_ET.sql script contains the SQL statements for the next three steps.

In a SQL*Plus session logged on as the SH user, execute the following query or the parallel_select_from_ET.sql file to see the current parallel session statistics:

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

Execute the same query you used before to access the external table with a parallel degree of 4, controlled with a hint. You can use the command below or the parallel_select_from_ET_2.sql script.

SELECT /*+ parallel(a,4) */ COUNT(*) 
FROM   sales_delta_XT a;

You are selecting from the external table in parallel, although the external table points only to one input source file. Alternatively, you could change the PARALLEL property of the external table with an ALTER TABLE command as follows:

ALTER TABLE sales_delta_XT PARALLEL 4;

View the session statistics again to see the differences. Execute the command below or the parallel_select_from_ET.sql script. Note that the parallel session statistics have changed. The display shows that the last query was parallelized and it also shows the degree of parallelism.

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

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

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,
   case CHANNEL_ID
   when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;

set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);

To show the PARALLEL INSERT execution plan, execute the commands below or the show_parallel_exec_plan.sql script logged in as the SH user. A parallel DML command must always be the first statement of a transaction. Furthermore, a DML operation cannot execute when there are primary key and foreign key constraints. Therefore, you have to disable the constraints prior to the parallel DML operation:

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,
   case CHANNEL_ID
   when 'S' then 3
   when 'T' then 9
   when 'C' then 5
   when 'I' then 4
   when 'P' then 2
   else 99
   end,
   PROMO_ID,
   sum(QUANTITY_SOLD),
   sum(AMOUNT_SOLD)
   FROM SALES_DELTA_XT
   GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;

set linesize 140
set pagesize 40
SELECT * FROM TABLE(dbms_xplan.display);

Execute the following SQL statements or the parallel_insert_file.sql file to perform a parallel INSERT. Set timing ON.

set timing on
COMMIT;

ALTER SESSION ENABLE PARALLEL DML;

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,
  case CHANNEL_ID
  when 'S' then 3
  when 'T' then 9
  when 'C' then 5
  when 'I' then 4
  when 'P' then 2
  else 99
  end,
  PROMO_ID,
  sum(QUANTITY_SOLD),
  sum(AMOUNT_SOLD)
FROM SALES_DELTA_XT
GROUP BY 1, prod_id,time_id,cust_id,channel_id,promo_id;
set timing off

SELECT * FROM TABLE(dbms_xplan.display_cursor);


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

Record the execution time of this statement and compare it to the total amount of time you need with SQL*Loader and a subsequent insertion. Note that you do not see the full benefit of parallelizing the external table access and combining the transformation with the loading, because you are accessing a very small amount of data in parallel on a single CPU machine using one disk.

Perform a ROLLBACK to return the data to its previous state. (In the next example, you insert the same data by using SQL*Loader.)

ROLLBACK;

After issuing a ROLLBACK, you need to reenable the constraints. Execute the commands below or the enable_cons.sql script.

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;

You loaded and transformed the data as one step. To load and transform the data with SQL*Loader, you need two process steps, which means more work effort and slower performance for the complete process.

In a SQL*Plus session connected as the SH user, execute the commands below or the create_stage.sql script to create a staging table:

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

 ALTER TABLE sales_dec01 MODIFY (channel_id CHAR(2) null);

Execute the following command from the OS command line:

cd /home/oracle/wkdir
sqlldr sh/sh control=sales_dec01.ctl direct=true

Note: You may need to specify your database alias when connecting using SQL*Loader. Start SQL*Loader with the statement:

sqlldr sh/sh@<database alias> control=sales_dec01.ctl direct=true

Note that you cannot parallelize this task. Check the SQL*Loader sales_dec01.log log file and record the execution time for the loading process.

You can check the sales_dec01.log file using any editor. The file is located in the /home/oracle/wkdir directory.

Space is consumed in the database for making the data accessible from within the database, unlike an external table. The space consumed by the staging table is linearly dependent on the amount of data to be loaded for further transformation.

Also note that it is not possible to parallelize the loading with SQL*Loader without having several external files. You can use the SKIP option for several SQL*Loader processes, accessing the same file. However, this enforces every SQL*Loader process to scan the whole external file. This is detrimental to the overall performance.

Log in to SQL*Plus as the SH user. Execute the SQL statements to transform and insert the external data (which is already staged in the database) into the SALES fact table by executing the commands below or the load_stage_table.sql script.

set timing on
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,
    case CHANNEL_ID
    when 'S' then 3
    when 'T' then 9
    when 'C' then 5
    when 'I' then 4
    when 'P' then 2
    else 99
    end,
    PROMO_ID,
    sum(QUANTITY_SOLD),
    sum(AMOUNT_SOLD)
  FROM sales_dec01
  GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;

set timing off

Use the command below or the drop_sales_dec01.sql script to drop the staging table information.

  
DROP TABLE sales_dec01;  

Execute the part_before_compression.sql script or copy the following SQL statements into your SQL*Plus session:

COLUMN partition_name FORMAT a50
COLUMN segment_name FORMAT a50

SELECT partition_name, compression FROM user_tab_partitions
WHERE partition_name='SALES_Q4_2001';


SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';


          

Now compress the partition and transparently maintain all existing indexes. All local and global indexes will be maintained as part of this SQL statement. The functionality of online index maintenance for partition maintenance operations is discussed later in this tutorial.

Note that compressing a partition is not an in-place compression. You create a new compressed segment and remove the old uncompressed segment at the end of the operation.

In a SQL*Plus session logged in as the SH user, execute the compress_salesQ4_2001.sql script or the following SQL statement:

ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

Identify how much space the new compressed partition is allocated and compare it to the size of the uncompressed partition by executing the following commands or the part_after_compression.sql script.

SELECT partition_name, compression FROM user_tab_partitions
WHERE partition_name='SALES_Q4_2001';

SELECT segment_name, bytes/(1024*1024) MB FROM user_segments
WHERE segment_name='SALES' AND partition_name='SALES_Q4_2001';

Typically the compression ratio with real-world data will be higher than the one experienced with the Sales History schema. The data in the SALES fact table is artificially generated and does not show the typical "natural sorting" that you find in any data warehouse environment where the data was cleansed, consolidated, or even aggregated prior to its INSERT.

First, check the number of rows in the current external table by executing the select_et.sql script file or the following SQL statements:

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

The file, containing all sales transactions for December 2001, will show the value for the last day of December, 2001. You see that the number of rows and the MAX(time_id) will be different after changing the external file at the OS level.

Change the LOCATION attribute. Execute the command below or the alter_loc_attrib.sql script to change the LOCATION attribute:

ALTER TABLE sales_delta_xt location ( 'salesQ1.dat' );

To check the new data, execute the commands below or the select_et.sql script to check the new data:

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

The number of rows as well as the maximum TIME_ID have changed. If your external table file is correct, the maximum TIME_ID is the last day of March, 2002.

Execute the commands below or the create_stage_table.sql script to create the table:

DROP TABLE sales_delta;

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

In a SQL*Plus session logged on as the SH user, execute the commands below or the load_stage_table2.sql script to load the table:

INSERT /*+ APPEND */ INTO sales_delta
SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */
  PROD_ID,
  CUST_ID,
  TIME_ID,
  case CHANNEL_ID
  when 'S' then 3
  when 'T' then 9
  when 'C' then 5
  when 'I' then 4
  when 'P' then 2
  else 99
  end,
  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;

COMMIT;

After loading the SALES_DELTA table, gather statistics for this newly created table. In a SQL*Plus session logged in as the SH user, execute the commands below or the gather_stat_stage_table.sql script to gather statistics for the table.

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

Before creating any bitmap indexes, you need to modify the newly created table into a compressed table without actually compressing any data. This operation is necessary to create bitmap indexes that are valid to be exchanged into the partitioned table that already contains compressed partitions. Execute the commands below or the alter_sales_delta.sql script to modify the table.

ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS;

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_static_bitmap_index.sql script to create the bitmapped indexes on the SALES_DELTA table.

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.

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_constraints.sql script to modify the constraints on the SALES table:

ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY;

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
   ) ;

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_partition_for_sales_etl.sql script to add a partition to the SALES table.

COLUMN partition_name FORMAT a20
                 
select partition_name, high_value 
from   user_tab_partitions 
where  table_name='SALES'
order by partition_position;

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

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

In a SQL*Plus session logged on as the SH user, execute the command below or the exchange_partition_wo_gim.sql script to the ALTER the SALES table, enabling the partition to be exchanged:

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

In a SQL*Plus session logged on as the SH user, execute the following queries or the select_count.sql script. They will show you the number of rows in the exchanged partition and the stand-alone table (which is now empty).

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

SELECT COUNT(*)
FROM   sales_delta;

Note that all local indexes of the SALES table are valid. Execute the command below or the show_sales_idx_status.sql script to view the status of the indexes:

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
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);

You can also use the WITHOUT VALIDATION clause as part of the PARTITION EXCHANGE command. This causes the Oracle Database server to suppress the validity checking of the table that is going to be exchanged. Otherwise, the Oracle Database server guarantees that all values of the partition key match within the partition boundaries.

In a SQL*Plus session logged on as the SH user, execute the commands below or the fast_split_sales.sql script to alter the SALES table and view the indexes status.

ALTER TABLE sales SPLIT PARTITION sales_q1_2002 
  AT (TO_DATE('01-APR-2002','DD-MON-YYYY')) 
  INTO (PARTITION sales_q1_2002, PARTITION sales_beyond_q1_2002);

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

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
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);

ALTER TABLE sales DROP PARTITION sales_beyond_q1_2002;

Note that all local indexes of the SALES table are still valid. Execute the show_sales_idx_status.sql script to view the status of the local indexes on the SALES table.

@show_sales_idx_status.sql

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_empty_sat.sql script to create an empty table that will hold the old 1998 data:

DROP TABLE sales_old_q1_1998;

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

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_ndx.sql script to create the local indexes.

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 ;

In a SQL*Plus session logged on as the SH schema, execute the commands below or the create_constraints_old.sql script to modify and create the constraints.

ALTER TABLE channels MODIFY CONSTRAINT CHANNELS_PK RELY;
ALTER TABLE countries MODIFY CONSTRAINT COUNTRIES_PK RELY;
ALTER TABLE customers MODIFY CONSTRAINT CUSTOMERS_PK RELY;
ALTER TABLE products MODIFY CONSTRAINT PRODUCTS_PK RELY;
ALTER TABLE promotions MODIFY CONSTRAINT PROMO_PK RELY;
ALTER TABLE times MODIFY CONSTRAINT TIMES_PK RELY; 

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
  ) ;

In a SQL*Plus session logged on as the SH user, execute the command below or the show_partition.sql script to view the data that will be aged out of the partition:

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

In a SQL*Plus session logged on as the SH user, execute the command below or the exchange_old_partition.sql script to exchange the partition:

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

Note that you could have used a DROP PARTITION statement instead. The SALES_OLD_Q1_1998 table now stores all the data of the first quarter of 1998. You could drop this table to remove the data entirely from the system.

In a SQL*Plus session logged on as the SH user, execute the commands below or the count_sales.sql script to view the data in the partition:

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

SELECT COUNT(*) 
FROM   sales_old_q1_1998;

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

Local indexes are not affected by the exchange. Execute the command below or the show_sales_idx_status.sql script to view the index information.

SELECT ui.index_name, DECODE(uip.status,null,ui.status,uip.status)
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);

In a SQL*Plus session logged on as the SH user, execute the following SQL statement to split off the most recent month (March 2002) from the quarterly partition, including local index maintenance. You can execute the command below or the split1_10g.sql script to accomplish this task.

ALTER TABLE sales SPLIT PARTITION sales_q1_2002
AT (TO_DATE('01-MAR-2002','DD-MON-YYYY'))
INTO (PARTITION sales_1_2_2002 TABLESPACE example, 
PARTITION sales_MAR_2002 TABLESPACE example NOCOMPRESS)
UPDATE INDEXES;

You can see that the new index partitions are co-located with the table partitions and that the index partition naming is inherited from the partition naming. Execute the commands below or the see_split.sql script to view the partition information.

COL segment_name format a25
COL partition_name format a25
COL tablespace_name format a25

SELECT segment_name, partition_name, tablespace_name 
FROM user_segments
WHERE segment_type='INDEX PARTITION'
AND segment_name IN 
   (SELECT index_name 
    FROM   user_indexes 
    WHERE  table_name='SALES');

...

In a SQL*Plus session logged on as the SH user, execute the following SQL statement to split the remainder partition, including local index maintenance. You can execute the command below or the split2_10g.sql script.

ALTER TABLE sales SPLIT PARTITION sales_1_2_2002 
  AT (TO_DATE('01-FEB-2002','DD-MON-YYYY'))
  INTO 
   (PARTITION sales_JAN_2002 TABLESPACE sysaux COMPRESS,
    PARTITION sales_FEB_2002 TABLESPACE example NOCOMPRESS)
  UPDATE INDEXES (sales_time_bix 
   (PARTITION jan_02 TABLESPACE example,
    PARTITION feb_02 TABLESPACE system));

You can see that the new index partitions are colocated with the table partitions and that the index partition naming is inherited from the partition. Execute the command below or the see_split2.sql script to view the partition and segment information:

SELECT segment_name, partition_name, tablespace_name 
FROM   user_segments
WHERE  segment_type='INDEX PARTITION'
  AND  tablespace_name <>'EXAMPLE'
  AND  segment_name IN 
    (SELECT index_name 
     FROM   user_indexes 
     WHERE  table_name='SALES');

In a SQL*Plus session logged on as the SH user, execute the commands below or the cleanup_split_10g.sql script to move the partition and update the indexes.

ALTER TABLE sales MOVE PARTITION sales_JAN_2002 TABLESPACE example COMPRESS
UPDATE INDEXES (sales_time_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_cust_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_channel_bix (PARTITION sales_jan_2002 TABLESPACE example),
                sales_prod_bix    (PARTITION sales_jan_2002 TABLESPACE example),
                sales_promo_bix   (PARTITION sales_jan_2002 TABLESPACE example))
;

ALTER INDEX sales_time_bix 
  REBUILD PARTITION feb_02 TABLESPACE example;

SELECT segment_name, partition_name, tablespace_name 
FROM   user_segments
WHERE  segment_type='INDEX PARTITION'
  AND  segment_name IN (SELECT index_name 
                        FROM   user_indexes 
                        WHERE  table_name='SALES')
  AND  tablespace_name <> 'EXAMPLE';

In a SQL*Plus session logged on as the SH user, execute the commands below or the prep4_global_index.sql script to prepare for global index maintenance.

CREATE TABLE sales_mar_2002_temp 
  NOLOGGING AS SELECT * FROM sales 
    PARTITION (sales_MAR_2002);

ALTER TABLE sales TRUNCATE PARTITION sales_MAR_2002;

SELECT COUNT(*) 
               FROM sales PARTITION (sales_MAR_2002);
               ALTER TABLE sales_mar_2002_temp COMPRESS;
               ALTER TABLE sales_mar_2002_temp NOCOMPRESS;

CREATE BITMAP INDEX sales_prod_mar_2002_bix
               ON sales_mar_2002_temp (prod_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_cust_mar_2002_bix
               ON sales_mar_2002_temp (cust_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_time_mar_2002_bix
               ON sales_mar_2002_temp (time_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_channel_mar_2002_bix
               ON sales_mar_2002_temp (channel_id)
               NOLOGGING COMPUTE STATISTICS ;

CREATE BITMAP INDEX sales_promo_mar_2002_bix
               ON sales_mar_2002_temp (promo_id)
               NOLOGGING COMPUTE STATISTICS ;

In a SQL*Plus session logged on as the SH user, execute the commands below or the create_global_index.sql script to create a concatenated unique index on the SALES table:

CREATE UNIQUE INDEX sales_pk
   ON sales (prod_id, cust_id, promo_id, channel_id, time_id)
   NOLOGGING COMPUTE STATISTICS;

This may take up to a minute.

Build a constraint leveraging this index by executing the command below or the add_sales_pk.sql script:

ALTER TABLE sales ADD CONSTRAINT sales_pk
PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id) USING INDEX;

Note that if a constraint is defined using the global index, the same constraint must be defined for the table to be exchanged as well! Execute the command below or the add_salestemp_pk.sql script to accomplish this task.

ALTER TABLE sales_mar_2002_temp 
  ADD CONSTRAINT sales_mar_2002_temp_pk
  PRIMARY KEY (prod_id, cust_id, promo_id, channel_id, time_id)
  DISABLE VALIDATE;

In a SQL*Plus session logged on as the SH user, execute the commands below or the use_global_index.sql script to create an explain plan and view the information.

EXPLAIN PLAN FOR
   SELECT /*+ INDEX(sales, sales_pk) */ count(*)
   FROM   sales
   WHERE  prod_id BETWEEN 100 AND 500;

set linesize 140
SELECT * 
FROM TABLE(dbms_xplan.display);

After verifying the plan and that you are using the global index, execute the following statement or the run_select.sql file repeatedly. You can use the SQL*Plus functionality "r" or "/" to rerun the last executed statement.

SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales
WHERE  prod_id BETWEEN 100 AND 500;

While you are executing the query, perform the steps below in Window Two. You will see that there is no impact on concurrent query access using a global index when the partition maintenance operation takes place.

The query will not fail. Recognize that the query result will change as soon as the partition exchange command succeeds. The Oracle Database server guarantees READ CONSISTENCY in this situation and provides the most efficient partition table and index maintenance operations without restricting the online usage.

In a SQL*Plus session logged on as the SH user, execute the following query or the exchange_partition_w_gim.sql script.

ALTER TABLE sales 
  EXCHANGE PARTITION sales_mar_2002
  WITH TABLE sales_mar_2002_temp
  INCLUDING INDEXES UPDATE GLOBAL INDEXES;

Although it is a DDL command, it might take some time because the global indexes are maintained as part of the atomic PARTITION EXCHANGE command.

You will see that all indexes are still valid after the partition maintenance operation. Execute the command below or the show_sales_idx_status.sql script.

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);

View the information in the exchanged partition and the stand-alone table. Execute the commands below or the count_mar_sales.sql script.

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


SELECT COUNT(*) 
FROM   sales_mar_2002_temp;

Thousands of rows were added to the partitioned table with this command and the stand-alone table is empty now.

In a SQL*Plus session logged on as the SH user, execute the following query or the use_global_index.sql script.

explain plan for
  SELECT /*+ INDEX(sales, sales_pk) */ count(*)
  FROM  sales 
  WHERE prod_id BETWEEN 100 AND 500;

set linesize 140

SELECT * 
FROM   table(dbms_xplan.display);

SELECT /*+ INDEX(sales, sales_pk) */ count(*)
FROM   sales 
WHERE prod_id BETWEEN 100 AND 500;

Perform the steps in Window Two below and then execute the query above to see the difference.

It will fail as soon as the partition maintenance command is processed.

In a SQL*Plus session logged on as the SH user, execute the following query or the exchange_partition_wo_gim2.sql script.

ALTER TABLE sales 
    EXCHANGE PARTITION sales_mar_2002 
    WITH TABLE sales_mar_2002_temp
    INCLUDING INDEXES;

The global index is now marked unusable. Execute the following command or the show_sales_idx_status.sql script to view this information.

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);

In a SQL*Plus session logged on as the SH user, execute the following statements or the cleanup_mod1.sql script to clean up the OBE-specific modifications.

ALTER TABLE sales DROP CONSTRAINT sales_pk;
DROP INDEX sales_pk;

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


ALTER TABLE sales DROP PARTITION sales_jan_2002;


ALTER TABLE sales DROP PARTITION sales_feb_2002;


ALTER TABLE sales DROP PARTITION sales_mar_2002;

DROP TABLE sales_mar_2002_temp;

DROP TABLE sales_delta;

DROP TABLE sales_old_q1_1998;
         
set serveroutput on
exec dw_handsOn.cleanup_modules

SELECT * FROM TABLE(dw_handsOn.verify_env)