-- rebuild_vs_drop.sql (Scot Martin)
-- 10.1.0.4 01-AUG-2005

-- This test compares dropping and recreating a locally partitioned index
-- with rebuilding the index.  Does both btree and bitmap.  Many factors
-- are ignored in this test, such as the nature of the data modifications
-- that would happen between the drop and recreate, or unusable and rebuild.

MYDBA@ORCL > 
MYDBA@ORCL > create table test(a date, x number, data char(5))
  2  partition by range(a)
  3  (
  4  	     partition p1 values less than (to_date('01-JAN-2006','DD-MON-YYYY')),
  5  	     partition p2 values less than (to_date('01-JAN-2007','DD-MON-YYYY')),
  6  	     partition p3 values less than (to_date('01-JAN-2008','DD-MON-YYYY'))
  7  );

Table created.

MYDBA@ORCL > 
MYDBA@ORCL > insert /*+ append */ into test
  2  select to_date('01-AUG-2005','DD-MON-YYYY') + rownum/3000,
  3  trunc(dbms_random.value(100000,100009)), 'x'
  4  from dual connect by level <= 1800000;

1800000 rows created.

MYDBA@ORCL > commit;

Commit complete.

MYDBA@ORCL > 
MYDBA@ORCL > select count(*) from test;

  COUNT(*)
----------
   1800000

1 row selected.

MYDBA@ORCL > select min(a) from test;

MIN(A)
--------------------
01-AUG-2005 00:00:29

1 row selected.

MYDBA@ORCL > select max(a) from test;

MAX(A)
--------------------
24-MAR-2007 00:00:00

1 row selected.

MYDBA@ORCL > select table_name, partition_name from user_tab_partitions order by 1,2;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST                           P1
TEST                           P2
TEST                           P3

3 rows selected.

MYDBA@ORCL > 
MYDBA@ORCL > set timing on;
MYDBA@ORCL > 
MYDBA@ORCL > create index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:45.96
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.19
MYDBA@ORCL > 
MYDBA@ORCL > create index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:45.87
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:08.79
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:19.02
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:05.64
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:09.56
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:17.44
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:05.04
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.45
MYDBA@ORCL > 
MYDBA@ORCL > create bitmap index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:04.80
MYDBA@ORCL > 
MYDBA@ORCL > drop index test_idx;

Index dropped.

Elapsed: 00:00:00.12
MYDBA@ORCL > 
MYDBA@ORCL > create bitmap index test_idx on test(x)
  2  local;

Index created.

Elapsed: 00:00:06.16
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:00.32
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:00.78
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:00.18
MYDBA@ORCL > 
MYDBA@ORCL > alter index test_idx rebuild partition p1;

Index altered.

Elapsed: 00:00:00.30
MYDBA@ORCL > alter index test_idx rebuild partition p2;

Index altered.

Elapsed: 00:00:00.62
MYDBA@ORCL > alter index test_idx rebuild partition p3;

Index altered.

Elapsed: 00:00:00.21
MYDBA@ORCL > 
MYDBA@ORCL > set timing off;
MYDBA@ORCL > 
MYDBA@ORCL > drop table test;

Table dropped.

MYDBA@ORCL > purge recyclebin;

Recyclebin purged.

MYDBA@ORCL > 
MYDBA@ORCL > set echo off;