Re: RE: Partitioning
- From: David Fitzjarrell <oratune@xxxxxxxxx>
- To: Zabair Ahmed <roon987@xxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2012 08:45:54 -0800 (PST)
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@xxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>; "oratune@xxxxxxxxx"
<oratune@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
