Re: RE: Partitioning

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 29 Nov 2012 08:45:54 -0800 (PST)
Message-ID: <1354207554.92702.YahooMailNeo_at_web121601.mail.ne1.yahoo.com>



What you want to do is still possible:
 

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> alter table part_test2 modify partition partition1 add subpartition partition1_ugly values ('NONUSABLE','UGLY');
 

Table altered.
 

SQL> alter table part_test2 modify partition partition2 add subpartition partition2_ugly values ('NONUSABLE','UGLY');
 

Table altered.
 

SQL> alter table part_test2 modify partition partition3 add subpartition partition3_ugly values ('NONUSABLE','UGLY');
 

Table altered.
 

SQL>
SQL> select count(*) from part_test2 partition(partition1);
 
  COUNT(*)


      3334
 

SQL> select count(*) from part_test2 partition(partition1);
 
  COUNT(*)


      3334
 

SQL> select count(*) from part_test2 partition(partition2);
 
  COUNT(*)


      3335
 

SQL> select count(*) from part_test2 partition(partition2);
 
  COUNT(*)


      3335
 

SQL> select count(*) from part_test2 partition(partition3);
 
  COUNT(*)


      3331
 

SQL> select count(*) from part_test2 partition(partition3);
 
  COUNT(*)


      3331
 

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(partition1_ugly);
 
  COUNT(*)


         0
 

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(partition2_ugly);
 
  COUNT(*)


         0
 

SQL> select count(*) from part_test2 subpartition(partition3_good);
 
  COUNT(*)


      1999
 

SQL> select count(*) from part_test2 subpartition(partition3_bad);
 
  COUNT(*)


      1332
 

SQL> select count(*) from part_test2 subpartition(partition3_ugly);
 
  COUNT(*)


         0
 

SQL> David Fitzjarrell

From: Zabair Ahmed <roon987_at_yahoo.co.uk> To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>; "oratune_at_yahoo.com" <oratune_at_yahoo.com> Sent: Thursday, November 29, 2012 9:20 AM Subject: Re: RE: Partitioning

I don't think I explained myself very clearly David. What I hoped for was say that we have created the new partitioned table (PART TEST2) with the subpartition template values of USABLE and UNUSABLE for status-name. Everything is working fine but now the requirement has changed and status-name will have an additional value of say NONUSABLE. How could this be handled.
Sent from Yahoo! Mail on Android

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 29 2012 - 17:45:54 CET

Original text of this message