Re: RE: Partitioning

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Wed, 28 Nov 2012 12:14:02 -0800 (PST)
Message-ID: <1354133642.13612.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>

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_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:14:02 CET