Perform the Installing Oracle Database 10g on Windows tutorial.

Download and unzip etl.zip into your working directory (i.e. c:\wkdir).

From a SQL*Plus session logged on to the SH schema, execute the following queries (Note that you can run the select_et.sql script file.):

@c:\wkdir\select_et.sql

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 script parallel_select_from_ET.sql contains the SQL statements for the next three steps.

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

@c:\wkdir\parallel_select_from_ET.sql

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

Run the same query you used before to access the external table with a parallel degree of 4, controlled with a hint. The select statement is:

@c:\wkdir\parallel_select_from_ET_2.sql

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. file. Alternatively, you could change the PARALLEL property of the external table with an ALTER TABLE command:

rem ALTER TABLE sales_delta_XT PARALLEL 4;

View the session statistics again to see the differences. You see the parallel session statistics have changed. It shows that the last query was parallelized, and it also show the degree of parallelism.

@c:\wkdir\parallel_select_from_ET.sql

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:

@c:\wkdir\show_serial_exec_plan.sql

Rem 10gR1

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, you 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 key foreign key constraints. Therefore, you have to disable the constraints prior to the parallel DML operation:

@c:\wkdir\show_parallel_exec_plan.sql

Rem 10gR1
Rem *****
Rem SHOW PARALLEL EXECUTION PLAN
Rem *****
Rem   *****
PROMPT   need to set parallel DML explicitly
PROMPT   and to disable constraints !!!
Rem   *****
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;

Rem *****
PROMPT SHOW PARALLEL EXECUTION PLAN
Rem *****
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);

Run SQL to perform a parallel insert. Run parallel_insert_file.sql or copy the following statements into your SQL*Plus session set timing on.

@c:\wkdir\parallel_insert_file.sql

Rem 10gR1
set timing on
COMMIT;

ALTER SESSION ENABLE PARALLEL DML;

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

Rem   Alternatively, you can control the successful parallel DML with a V$ view
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 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 row rollback. In the next tutorial the same data is inserted using SQL*Loader.

ROLLBACK;

After issuing a rollback, you need to enable the constraints again.

@c:\wkdir\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;

You have just loaded and transformed the data as one step. To accomplish the same functionality with SQL*Loader, you would have needed TWO process steps, which means more work effort and slower performance for the complete process.

Run the sql script create_stage.sql to create a staging table:

@c:\wkdir\create_stage.sql

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 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 log file sales_dec01.log and write down the execution time for the loading process.

You can check the sales_dec01.log file using any editor. The file is located in the c:\wkdir folder.

Unlike with an External Table, space is consumed in the database for making the data accessible from within the database. 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 reasonably possible to parallelize the loading with SQL*Loader without having several external files. You can use the SKIP option for several 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.

Run SQL to transform and insert the external data, which is already staged in the database, into the SALES fact table by executing the load_stage_table.sql script.

@c:\wkdir\load_stage_table.sql

REM 10gR1
Rem Load from staging table into target
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

Drop the staging table information. You can execute the drop_sales_dec01.sql script.

@c:\wkdir\drop_sales_dec01.sql  
DROP TABLE sales_dec01;  

Run the part_before_compression.sql script or by copying the following SQL into your SQL*Plus session:

@c:\wkdir\part_before_compression.sql


PROMPT Space consumption before compression
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 Operation 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 one at the end of the operation.

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

@c:\wkdir\compress_salesQ4_2001.sql

PROMPT now compress the partition including index maintenance (details later))
ALTER TABLE sales MOVE PARTITION sales_q4_2001 COMPRESS UPDATE INDEXES;

See how much space the new compressed partition is allocating and compare it to the size of the uncompressed partition:

@c:\wkdir\part_after_compression.sql

PROMPT Space consumption AFTER compression

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

Most likely the compression ratio with real-world data will be higher than the one experienced with the sales history schema. Compression ratios reported from customers are in an average a factor of 2 to 5. This means that a table that formerly allocated 500 GB of disk space is now reduced to a size of 100 GB for a compression factor of five.
The data in the SALES fact table is artificially generated and does not show the typical "natural sorting" 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. (Note that you can run the select_et.sql script file.)

@c:\wkdir\select_et.sql

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 on OS level.

Change the LOCATION attribute. Run the alter_loc_attrib.sql to change the LOCATION attribute:

@c:\wkdir\alter_loc_attrib.sql

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

Then check the new data. Run the select_et.sql to check the new data:

@c:\wkdir\select_et.sql

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.

Run the script create_stage_table.sql to create the table:

@c:\wkdir\create_stage_table.sql

DROP TABLE sales_delta;

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

From a SQL*Plus session logged on to the SH schema, run the script load_stage_tables.sql to load the table:

@c:\wkdir\load_stage_table2.sql

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.

From a SQL*Plus session logged on to the SH schema, run the script gather_stat_stage_table.sql to gather statistics about the table:

@c:\wkdir\gather_stat_stage_table.sql

Rem 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. Run the script alter_sales_delta.sql to modify the table.

@c:\wkdir\alter_sales_delta.sql

ALTER TABLE sales_delta COMPRESS;
ALTER TABLE sales_delta NOCOMPRESS;

From a SQL*Plus session logged on to the SH schema, run the script create_static_bitmap_index.sql to create the bitmapped indexes on the SALES_DELTA table.

@c:\wkdir\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, run the script create_constraints.sql to modify the constraints on the SALES table:

@c:\wkdir\create_constraints.sql

set echo on
Rem *****
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
   ) ;

From a SQL*Plus session logged on to the SH schema, run the script create_partition_for_sales_etl.sql to add a partition to the SALES table.

@c:\wkdir\create_partition_for_sales_etl.sql

Rem *****
Rem current partitions
Rem *****

COLUMN partition_name FORMAT a20
                 
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_2002
VALUES LESS THAN (MAXVALUE);

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

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

From a SQL*Plus session logged on to the SH schema, run the script exchange_partition_wo_gim.sql to alter the SALES table, enabling the partition to be exchanged:

@c:\wkdir\exchange_partition_wo_gim.sql

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

ALTER TABLE sales EXCHANGE PARTITION sales_q1_2002 
    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 the stand-alone table (which is now empty). Run the select_count.sql script.

@c:\wkdir\select_count.sql

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

SELECT COUNT(*)
FROM   sales_delta;

Have you ever inserted that many rows that quickly?

Note that all local indexes of the SALES table are valid. Run the script show_sales_idx_status.sql to view the status of the indexes:

@c:\wkdir\show_sales_idx_status.sql

PROMPT   INDEXES ARE MAINTAINED

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 to suppress the validity checking of the table that is going to be exchanged. Otherwise, the Oracle database guarantees that all values of the partition key match within the partition boundaries.

From a SQL*Plus session logged on to the SH schema, run the script fast_split.sql to alter the SALES table, and view the indexes status.

@c:\wkdir\fast_split_sales.sql

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

PROMPT Let's control the count in the most recent partition. should be empty
SELECT COUNT(*) FROM sales PARTITION (sales_beyond_q1_2002);

PROMPT INDEXES ARE MAINTAINED
Rem since no data is moved with the SPLIT operation, the indexes are still valid
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);

PROMPT Now you can drop the empty overflow partition
ALTER TABLE sales DROP PARTITION sales_beyond_q1_2002;

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

@c:\wkdir\show_sales_idx_status.sql

From a SQL*Plus session logged on to the SH schema, run the script create_empty_sat.sql to create an empty table that will hold the old 1998 data:

@c:\wkdir\create_empty_sat.sql

DROP TABLE sales_old_q1_1998;

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

From a SQL*Plus session logged on to the SH schema, run the script create_ndx.sql to create the local indexes

@c:\wkdir\create_ndx.sql

set echo on

Rem   *****
PROMPT   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, run the script create_constraints_old.sql to modify and create the constraints.

@c:\wkdir\create_constraints_old.sql

Rem   ***10g***
REM   TO DO: cannot enable fk rely when pk is set to norely
Rem   *****

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

From a SQL*Plus session logged on to the SH schema, run the script show_partition.sql to view the data that will be aged out of the partition:

@c:\wkdir\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, run the script exchange_old_partition.sql to exchange the partition:

@c:\wkdir\exchange_old_partition.sql

Rem   *****
PROMPT   EXCHANGE IT TO "REMOVE" OLD DATA
Rem   the exchange happens in a fraction of seconds, 
Rem   since it is only a DDL command
Rem   *****

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 table SALES_OLD_Q1_1998 now stores all the data of the first quarter of 1998. You could drop this table to remove the data entirely from the system.

From a SQL*Plus session logged on to the SH schema, run the script count_sales.sql to view the data in the partition:

@c:\wkdir\count_sales.sql

PROMPT   show the actual content of the partition to be aged out AFTER exchange
Rem   no more data in it
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. Run the script show_sales_idx_status.sql to view the index information.

@c:\wkdir\show_sales_idx_status.sql

PROMPT   INDEXES ARE MAINTAINED
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);

From a SQL*Plus session logged on to the SH schema, execute the following SQL statement to split off the most recent month (March 2002) from the quarterly partition, including local index maintenance. You can run the script split1_10g.sql to accomplish this:

@c:\wkdir\split1_10g.sql

PROMPT Now leverage the new functionality and break down the existing
PROMPT quarter partition into monthly ones, leveraging new 10g functionality

Rem note that an index location is not specified, so they will be colocated with the partitions.
Rem the name will be inherited by the partition name

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 colocated with the table partitions and that the index partition naming is inherited from the partition naming. Run the script see_split.sql to view the partition information:

@c:\wkdir\see_split.sql

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

Rem you will see that the newly created index segments 
Rem are co-located with the table partitions

Rem Also note the automatically derived naming
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');

...

From a SQL*Plus session logged on to the SH schema, execute the following SQL statement to split the remainder partition, including local index maintenance. You can run the split2_10g.sql script to accomplish this:

@c:\wkdir\split2_10g.sql

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 naming. Run the see_split2.sql script to view the partition and segment information:

@c:\wkdir\see_split2.sql

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

From a SQL*Plus session logged on to the SH schema, run the cleanup_split_10g.sql script to move the partition and update the indexes.

@c:\wkdir\cleanup_split_10g.sql

PROMPT bring them back in shape (into tablespace EXAMPLE)

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

PROMPT Let's move the last cowboy back to EXAMPLE ..
ALTER INDEX sales_time_bix 
  REBUILD PARTITION feb_02 TABLESPACE example;

PROMPT no index structures outside EXAMPLE anymore
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';

From a SQL*Plus session logged on to the SH schema, run the prep4_global_index.sql script to prepare for global index maintenance.

@c:\wkdir\prep4_global_index.sql

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

Rem control ...
Rem is empty

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 ;

From a SQL*Plus session logged on to the SH schema, run the create_global_index.sql script to create a concatenated unique index on the SALES table:

@c:\wkdir\create_global_index.sql

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 running the add_sales_pk.sql script:

@c:\wkdir\add_sales_pk.sql


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! Run the add_salestemp_pk.sql script to accomplish this:

@c:\wkdir\add_salestemp_pk


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;

From a SQL*Plus session logged on to the SH schema, execute the following query: You can run the use_global_index.sql script to create an explain plan and view the information:

@c:\wkdir\use_global_index.sql

Rem STATEMENT WHICH USES GLOBAL INDEX - Window 1 

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 that you are using the global index, process this statement over and over again. You can use the SQL*Plus functionality "r" to rerun the last statement, or you can run the run_select.sql file.

@c:\wkdir\run_select.sql

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

While you are running 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 guarantees READ CONSISTENCY in this situation as well and provides the most efficient partition table and index maintenance operations without restricting the online usage.

From a SQL*Plus session logged on to the SH schema, execute the following query, or run the script exchange_partition_w_gim.sql:

@c:\wkdir\exchange_partition_w_gim.sql

set echo on
Rem *****
Rem EXCHANGE IT
Rem - with global index maintenance
Rem demonstrate influence of index on other queries in 
Rem second window
Rem *****

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. Run the script show_sales_idx_status.sql:

@c:\wkdir\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);

View the information in the exchanged partition and the stand-alone table: Run the script count_mar_sales.sql:

@c:\wkdir\count_mar_sales.sql


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.

From a SQL*Plus session logged on to the SH schema, execute the following query. Run the script use_global_index.sql:

@c:\wkdir\use_global_index.sql

Rem STATEMENT WHICH USES GLOBAL INDEX - Window 1
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.

From a SQL*Plus session logged on to the SH schema, execute the following query. Run the script exchange_partition_wo_gim2.sql:

@c:\wkdir\exchange_partition_wo_gim2.sql

Rem EXCHANGE IT
Rem - without global index maintenance
Rem demonstrate influence of index on other queries in 
Rem second window
Rem *****

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

The global index is now marked unusable. Run the script show_sales_idx_status.sql to view this information.

@c:\wkdir\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 statements to cleanup the module-specific modifications. Run the script cleanup_mod1.sql:

@c:\wkdir\cleanup_mod1.sql

Rem   drop unique index sales_pk. you do not need it, and it
Rem   might cause some problems later on with DML operations
Rem   *****

ALTER TABLE sales DROP CONSTRAINT sales_pk;
DROP INDEX sales_pk;

Rem Cleanup - bring data back
Rem Necessary - that way you do not have to check for it at the end - top goal is no
Rem interference with the original SH schema ...
Rem - data for 1998
Rem - no data for 2001
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;
         

PROMPT original situation again
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_2001);
Rem SELECT COUNT(*) FROM sales PARTITION (sales_q1_1998);

PROMPT just to be safe ... -cleanup module
set serveroutput on
exec dw_handsOn.cleanup_modules

PROMPT control whether the correction script was applied properly ..
SELECT * FROM TABLE(dw_handsOn.verify_env)

Please ensure that the environment is properly reset. Having an environment that is not "clean" may affect the usage or run-time behavior in other tutorials.