Re: RE: Partitioning

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

Certainly it is:
 

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> update part_test
  2  set status_name = 'NONUSABLE', status = 'UGLY'
  3  where mod(dataset_id, 4) = 0;
 

2000 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           subpartition ugly values ('NONUSABLE','UGLY'))
 13  (partition partition1 values (1,2,3,4,5),
 14  partition partition2 values (6,7,8,9,10),
 15  partition partition3 values (11,12,13,14,15))
 16  /
 

Table created.
 

SQL>
SQL> insert into part_test2 select * from part_test;
 

10000 rows created.
 

SQL>
SQL> commit;
 

Commit complete.
 

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(*)

---

       667
 

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

---

       667
 

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

---

      1334
 

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

---

      1334
 

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

---

       667
 

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

---

      1999
 

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

---

       666
 

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

---

       666
 

SQL>
 

And for the dbms_redefinition 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> update part_test
  2  set status_name = 'NONUSABLE', status = 'UGLY'
  3  where mod(dataset_id, 4) = 0;
 

2000 rows updated.
 

SQL>
SQL> commit;
 

Commit complete.
 

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           subpartition ugly values ('NONUSABLE','UGLY'))
 13  (partition partition1 values (1,2,3,4,5),
 14  partition partition2 values (6,7,8,9,10),
 15  partition partition3 values (11,12,13,14,15))
 16  /
 

Table created.
 

SQL>
SQL> exec dbms_redefinition.start_redef_table('bing','part_test','part_test2', null, dbms_redefinition.cons_use_rowid);
 

PL/SQL procedure successfully completed.
 

SQL>
SQL> declare
  2
  3  num_errs pls_integer;
  4
  5  begin
  6            dbms_redefinition.copy_table_dependents('bing', 'part_test','part_test2',
  7            dbms_redefinition.cons_orig_params, true, true, true, true, num_errs);
  8  end;
  9  /
 

PL/SQL procedure successfully completed.
 

SQL>
SQL> select object_name, base_table_name, ddl_txt from
  2           dba_redefinition_errors;
 

no rows selected
 

SQL>
SQL> exec dbms_redefinition.sync_interim_table('bing','part_test','part_test2')
 

PL/SQL procedure successfully completed.
 

SQL>
SQL> exec dbms_redefinition.finish_redef_table('bing','part_test','part_test2')
 

PL/SQL procedure successfully completed.
 

SQL>
SQL> select count(*) from part_test subpartition(partition1_good);
 
  COUNT(*)

---

      2000
 

SQL> select count(*) from part_test subpartition(partition1_bad);
 
  COUNT(*)

---

       667
 

SQL> select count(*) from part_test subpartition(partition1_ugly);
 
  COUNT(*)

---

       667
 

SQL> select count(*) from part_test subpartition(partition2_good);
 
  COUNT(*)

---

      1334
 

SQL> select count(*) from part_test subpartition(partition2_bad);
 
  COUNT(*)

---

      1334
 

SQL> select count(*) from part_test subpartition(partition2_ugly);
 
  COUNT(*)

---

       667
 

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

---

      1999
 

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

---

       666
 

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

---

       666
 

SQL>
SQL> drop table part_test2 purge;
 

Table dropped.
 

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 4:02 AM Subject: Re: RE: Partitioning

Thank you David for that comprehensive example. One question is it possible to modify the subpartition template to say add a third value in the future eg UGLY. Thanks

Sent from Yahoo! Mail on Android

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 29 2012 - 16:48:08 CET