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.):

@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 ‘31-DEC-01’.

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:

@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:

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

Now rerun the session stats again to see the differences. You will see the parallel session statistics have changed; it shows that the last query was parallelized, and it also show the degree of parallelism.

@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:

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

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

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

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 will need with SQL*Loader and a subsequent insertion. Note that you will 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.

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.

@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 will need TWO process steps, which means more work effort and slower performance for the complete process.

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

@create_stage.sql

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

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

Open a new terminal window. Execute the following commands:

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. For example, if your database alias is o10g1, start SQL*Loader with the statement:

sqlldr sh/sh@o10g1 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.

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.

You will now run 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 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

You can now drop the staging table information:

@drop_sales_dec01.sql>

DROP TABLE sales_dec01;

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

@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, but that 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:

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

Let's now see how much space the new compressed partition is allocating and compare it to the size of the uncompressed partition:

@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 will find in any data warehouse environment where the data was cleansed, consolidated, or even aggregated prior to its INSERT.

First, check again the number of rows in the current external table. (Note that you can run the select_et.sql script file.)

@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 30-DEC-2001. You will 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:

@alter_loc_attrib.sql

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

Then check the new data:

@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 30-MAR-2002.

Run the SQL to create the table:

@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, 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,
  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, execute the following statement:

@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 to DBMS_STATS the same internal property as 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.

@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, 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 *****
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, 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_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, execute the following query:

@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):

@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.

@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. Oracle will then suppress the validity checking of the table that is going to be exchanged; otherwise Oracle guarantees that all values of the partition key match within the partition boundaries.

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

@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.

@show_sales_idx_status.sql

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

@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, execute the following query:

@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, execute the following query:

@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, 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

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 instead. 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, execute the following queries:

@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.

@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, split1_10g.sql:

@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.

@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, split2_10g.sql:

@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.

@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, execute the following query:

@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, execute the following query:

@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, execute the following query:

@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.

Let's build a constraint leveraging this index. Execute the following statements:

@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!

@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:

@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.

@run_select.sql

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

While you’re 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. You will also recognize that the query result will change as soon as the partition exchange command succeeded. Oracle guarantees READ CONSISTENCY in this situation as well and provides 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:

@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:

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

Let’s see what’s in the exchanged partition and the stand-alone table:

@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; the stand-alone table is empty now.

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

@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:

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

You will see that the global index is now marked unusable.

@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 for module-specific modifications:

@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.