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
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-lReceived on Thu Nov 29 2012 - 17:45:54 CET