Re: RE: Partitioning

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 28 Nov 2012 12:56:01 -0800 (PST)
Message-ID: <1354136161.27909.YahooMailNeo_at_web121606.mail.ne1.yahoo.com>

You can also use dbms_redefinition but I'm not sure it's faster:
 

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> 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> exec dbms_redefinition.start_redef_table('bing','part_test','part_test2', null, dbms_redefinition.cons_u
 

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

---

      1334
 

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

---

      1334
 

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

---

      2001
 

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

---

      1999
 

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

---

      1332
 

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>; "mwf_at_rsiz.com" <mwf_at_rsiz.com> 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

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

Received on Wed Nov 28 2012 - 21:56:01 CET