range-hash partition drop partition local index still exists?

From: Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx>
To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 8 Jun 2010 15:46:31 +0700
dear lists,

10.2.0.4 - win32
I create range-hash table, and then drop a partition.
but local index partition still exists...

create table shipped
(prod_id number,
cust_id number,
datetime timestamp with local time zone,
quantity number,
amount number(10,2))
pctfree 5
partition by range (datetime)
SUBPARTITION BY HASH(cust_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE users,
SUBPARTITION sp2 TABLESPACE users,
SUBPARTITION sp3 TABLESPACE users,
SUBPARTITION sp4 TABLESPACE users)
( partition SHP_Q1_2002 values less than (timestamp '2002-04-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_Q2_2002 values less than (timestamp '2002-07-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_Q3_2002 values less than (timestamp '2002-10-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_Q4_2002 values less than (timestamp '2003-01-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_Q1_2003 values less than (timestamp '2003-04-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_Q2_2003 values less than (timestamp '2003-07-01 00:00:00.00
Asia/Jakarta') tablespace users,
  partition SHP_MAX values less than (MAXVALUE) tablespace users
);

create index shipped_idx1 on shipped(prod_id) local tablespace users;
create index shipped_idx2 on shipped(cust_id) local tablespace users;
create index shipped_idx3 on shipped(amount) global partition by
hash(amount) partitions 8 tablespace users;


declare
yyyy varchar2(4);
mm varchar2(2);
dd varchar2(2);
begin

for i in 1..100 loop
yyyy:=round(dbms_random.value(2002,2003),0);
mm:='0'||round(dbms_random.value(1,9),0);
dd:=round(dbms_random.value(1,28),0);
insert into shipped
values(i,round(dbms_random.value(i,9999999999999999),0),to_date(yyyy||mm||dd,'YYYYMMDD'),dd,yyyy);
end loop;
commit;
end;
/

 select index_name,partition_name,subpartition_count,high_value,status
 from dba_ind_partitions
 where index_name in ('SHIPPED_IDX1','SHIPPED_IDX2','SHIPPED_IDX3')
 order by 1,2;

 select index_name,partition_name,subpartition_name,high_value,status
 from dba_ind_subpartitions
 where index_name in ('SHIPPED_IDX1','SHIPPED_IDX2','SHIPPED_IDX3')
 order by 1,2;

select count(1) from shipped partition(SHP_Q1_2002);

alter table shipped drop partition SHP_Q1_2002 update global indexes;

 select index_name,partition_name,subpartition_count,high_value,status
 from dba_ind_partitions
 where index_name in ('SHIPPED_IDX1','SHIPPED_IDX2','SHIPPED_IDX3')
 order by 1,2;

SHIPPED_IDX1                   SHP_Q1_2002                      0 TIMESTAMP'
2002-04-01 00:00:00.000000000Asia/Jakarta WIT'                        USABLE
SHIPPED_IDX2                   SHP_Q1_2002                      0 TIMESTAMP'
2002-04-01 00:00:00.000000000Asia/Jakarta WIT'                        USABLE

is it expected behaviour?
or am I missing something?


-- 
thanks and regards
ujang jaenudin
jakarta - indonesia
range-hash partition drop partition local index still exists?

Other related posts:
