Here is an example: SQL> create table part_test( 2 dataset_id number, 3 status_name varchar2(20), 4 status varchar2(12), 5 stuff varchar2(100) 6 ) 7 partition by list(dataset_id) 8 (partition partition1 values (1,2,3,4,5), 9 partition partition2 values (6,7,8,9,10), 10 partition partition3 values (11,12,13,14,15)) 11 / Table created. SQL> SQL> begin 2 for i in 1..10000 loop 3 insert into part_test 4 values(mod(i,15)+1, 'USABLE','VALID','When in the course of human events'); 5 end loop; 6 7 commit; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> update part_test 2 set status_name = 'UNUSABLE', status = 'INVALID' 3 where mod(dataset_id, 2) = 0; 4667 rows updated. SQL> SQL> commit; Commit complete. SQL> SQL> select count(*) from part_test partition(partition1); COUNT(*) ---------- 3334 SQL> select count(*) from part_test partition(partition2); COUNT(*) ---------- 3335 SQL> select count(*) from part_test partition(partition3); COUNT(*) ---------- 3331 SQL> SQL> create table part_test2( 2 dataset_id number, 3 status_name varchar2(20), 4 status varchar2(12), 5 stuff varchar2(100) 6 ) 7 partition by list(dataset_id) 8 subpartition by list(status_name) 9 subpartition template 10 (subpartition good values ('USABLE','GOOD'), 11 subpartition bad values ('UNUSABLE','BAD')) 12 (partition partition1 values (1,2,3,4,5), 13 partition partition2 values (6,7,8,9,10), 14 partition partition3 values (11,12,13,14,15)) 15 / Table created. SQL> SQL> insert into part_test2 select * from part_test; 10000 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> SQL> select count(*) from part_test2 subpartition(partition1_good); COUNT(*) ---------- 2000 SQL> select count(*) from part_test2 subpartition(partition1_bad); COUNT(*) ---------- 1334 SQL> select count(*) from part_test2 subpartition(partition2_good); COUNT(*) ---------- 1334 SQL> select count(*) from part_test2 subpartition(partition2_bad); COUNT(*) ---------- 2001 SQL> select count(*) from part_test2 subpartition(partition3_good); COUNT(*) ---------- 1999 SQL> select count(*) from part_test2 subpartition(partition3_bad); COUNT(*) ---------- 1332 SQL> David Fitzjarrell ________________________________ From: Zabair Ahmed <roon987@xxxxxxxxxxx> To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>; "mwf@xxxxxxxx" <mwf@xxxxxxxx> Sent: Wednesday, November 28, 2012 12:37 PM Subject: Re: RE: Partitioning Thanks Mark First reaction is -- whhhatt. This seems to be beyond my SQL/partitioning knowledge. But I will continue with my analysis/research on the issue. Yes I do have a test system and I've already created the partitioned here and populated it with sample data. I am now in position to write and test the approach to be taken. The question I have for you is would the approach be any easier if I could hold 2 copies of the table in PROD. And if I had this luxury how would you go about doing it. Thanks for your input so far, much appreciated. Sent from Yahoo! Mail on Android -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l