6 Replies Latest reply: Jun 5, 2013 3:40 AM by 1012855 RSS

    ORA-14314: resulting List partition(s) must contain atleast 1 value

    vpolasa
      Hi,

      Using: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production, Windows 7 Platform.

      I'm trying to understand Exchange Partition and Split (List) partitioning. Below is the code I'm trying to work on:
        CREATE TABLE big_table (
        id            NUMBER(10),
        created_date  DATE,
        lookup_id     NUMBER(10),
        data          VARCHAR2(50)
      );
      
      declare
        l_lookup_id big_table.lookup_id%type; 
        l_create_date date; 
      begin
        for i in 1 .. 1000000 loop
          if mod(i,3) = 0 then
             l_create_date := to_date('19-mar-2011','dd-mon-yyyy'); 
             l_lookup_id := 2; 
          elsif mod(i,2) = 0 then
             l_create_date := to_date('19-mar-2012','dd-mon-yyyy'); 
             l_lookup_id := 1; 
          else
             l_create_date := to_date('19-mar-2013','dd-mon-yyyy'); 
             l_lookup_id := 3; 
          end if; 
          
          insert into big_table(id, created_date, lookup_id, data)
             values (i, l_create_date, l_lookup_id, 'This is some data for '||i); 
        end loop; 
        commit; 
      end; 
      /
      
      alter table big_table add (
      constraint big_table_pk primary key (id));
      
      exec dbms_stats.gather_table_stats(user, 'BIG_TABLE', cascade => true);
      
      create table big_table2 (
      id number(10),
      created_date date, 
      lookup_id number(10),
      data varchar2(50)
      )
      partition by list (created_date)
      (partition p20991231 values (TO_DATE(' 2099-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
      
      alter table big_table2 add (
       constraint big_table_pk2 primary key(id));
       
      alter table big_table2 exchange partition p20991231
      with table big_table
      without validation
      update global indexes;
      
      drop table big_table;
      
      rename big_table2 to big_table;
      
      alter table big_table rename constraint big_table_pk2 to big_table_pk;
      
      alter index big_table_pk2 rename to big_table_pk;
      
      exec dbms_stats.gather_table_stats(USER, 'BIG_TABLE', cascade => TRUE);
       
      I'm trying to split the data by moving created_date=19-mar-2013 to new partition p20130319. I tried to run the below query but failed with error. Where am I doing it wrong?

      Thanks.
      alter table big_table
      split partition p20991231 values (to_date('19-mar-2013','dd-mon-yyyy'))
      into (partition p20130319
           ,partition p20991231
           ); 
      
      Error report:
      SQL Error: ORA-14314: resulting List partition(s) must contain atleast 1 value
      14314. 00000 -  "resulting List partition(s) must contain atleast 1 value"
      *Cause:    After a SPLIT/DROP VALUE of a list partition, each resulting
                 partition(as applicable) must contain at least 1 value
      *Action:   Ensure that each of the resulting partitions contains atleast
                 1 value
        • 1. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
          Solomon Yakobson
          It doesn't work the way you thought it does. When you use split partition you MUST specify split date that is within partition bounds. It doesn't matter that you used NOVALIDATE and partition contains values outside the list.

          SY.
          • 2. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
            rp0428
            >
            I'm trying to understand Exchange Partition and Split (List) partitioning
            . . .
            I'm trying to split the data by moving created_date=19-mar-2013 to new partition p20130319.
            >
            How do you expect that to work? the value '19-mar-2013' is NOT in the current partition list of values. You created a list that only has ONE value
            (partition p20991231 values (TO_DATE(' 2099-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
            How do you expect Oracle to split ONE value? That doesn't even make any sense.

            Can you split this list of alphabetic characters into two lists for us: 'E'?

            You can't get two lists out of ONE item.

            Now try this. Split this list into two lists: 'A,B,C,D,E'. Do the split at character 'C' and you get the two lists: 'A,B,C' and 'D,E'.

            Now that makes sense.
            • 3. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
              vpolasa
              I stand corrected.

              Below are the steps I have gone through to understand:

              1. How to partition a table with data in it.
              2. Exchange partition.
              3. Split partition (List).
              4. Split data to more than 2 partitions.

              Please correct me if I'm missing anything.
              CREATE TABLE big_table
                (
                  id           NUMBER(10), 
                  created_date DATE, 
                  lookup_id    NUMBER(10), 
                  data         VARCHAR2(50)
                );
                
              DECLARE
                l_lookup_id big_table.lookup_id%type;
                l_create_date DATE;
              BEGIN
                FOR i IN 1 .. 1000000
                LOOP
                  IF mod(i,3)= 0 THEN
                    l_create_date := to_date('19-mar-2011','dd-mon-yyyy');
                    l_lookup_id   := 2;
                  elsif mod(i,2)   = 0 THEN
                    l_create_date := to_date('19-mar-2012','dd-mon-yyyy');
                    l_lookup_id   := 1;
                  ELSE
                    l_create_date := to_date('19-mar-2013','dd-mon-yyyy');
                    l_lookup_id   := 3;
                  END IF;
                  INSERT INTO big_table(id, created_date, lookup_id, data)
                    VALUES(i, l_create_date, l_lookup_id, 'This is some data for '||i);
                END LOOP;
                COMMIT;
              END;
              /
              
              ALTER TABLE big_table ADD
              (CONSTRAINT big_table_pk PRIMARY KEY (id));
              
              EXEC dbms_stats.gather_table_stats(USER, 'BIG_TABLE', CASCADE => true);
              
              CREATE TABLE big_table2
                ( id           NUMBER(10), 
                  created_date DATE, 
                  lookup_id    NUMBER(10), 
                  data         VARCHAR2(50)
                )
                partition BY list(created_date)
                (partition p0319 VALUES
                  (TO_DATE(' 2013-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ,TO_DATE(' 2012-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ,TO_DATE(' 2011-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
                 );
                 
              ALTER TABLE big_table2 ADD
              (CONSTRAINT big_table_pk2 PRIMARY KEY(id));
              
              ALTER TABLE big_table2 exchange partition p0319
              WITH TABLE big_table without validation
              UPDATE global indexes;
              
              DROP TABLE big_table;
              
              RENAME big_table2 TO big_table;
              
              ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
              
              ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
              
              EXEC dbms_stats.gather_table_stats(USER, 'BIG_TABLE', CASCADE => TRUE);
              
              SELECT p.partition_name, p.num_rows
              FROM user_tab_partitions p
              WHERE p.table_name = 'BIG_TABLE';
              
              ALTER TABLE big_table split partition p0319 VALUES
              (TO_DATE(' 2013-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
              INTO (partition p20130319, partition p0319);
              
              ALTER INDEX big_table_pk rebuild;
              
              EXEC dbms_stats.gather_table_stats(USER, 'BIG_TABLE', CASCADE => TRUE);
              
              SELECT p.partition_name, p.num_rows
              FROM user_tab_partitions p
              WHERE table_name = 'BIG_TABLE';
              
              SELECT DISTINCT created_date FROM big_table partition(p20130319);
              SELECT DISTINCT created_date FROM big_table partition(p0319);
              
              ALTER TABLE big_table split partition p0319 VALUES
              (TO_DATE(' 2012-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
              INTO (partition p20120319, partition p20110319);
              
              ALTER INDEX big_table_pk rebuild;
              
              EXEC dbms_stats.gather_table_stats(USER, 'BIG_TABLE', CASCADE => TRUE);
              
              SELECT p.partition_name, p.num_rows
              FROM user_tab_partitions p
              WHERE table_name = 'BIG_TABLE';
              
              SELECT DISTINCT created_date FROM big_table partition(p20130319);
              SELECT DISTINCT created_date FROM big_table partition(p20120319);
              SELECT DISTINCT created_date FROM big_table partition(p20110319);
              • 4. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
                rp0428
                >
                I stand corrected.
                >
                Apparently not since you just posted a bunch of code just like the first bunch.

                Did you miss this part of my reply?
                >
                You can't get two lists out of ONE item.
                >
                You created a LIST partitioned table. You only have ONE item in the list

                You can't get two lists out of ONE item.

                Try the experiment I suggested and post the results:
                >
                Can you split this list of alphabetic characters into two lists for us: 'E'?
                >
                NO YOU CAN'T - You are trying to tell Oracle to split the list that contains a single 'E' into two pieces. You can't divide a list of ONE item into two pieces.

                Now try this:
                >
                Split this list into two lists: 'A,B,C,D,E'. Do the split at character 'C'
                >
                Now you can do the split. You get two lists: 'A,B,C' and 'D,E'.

                If you are using a date you should be using a RANGE partition.

                See the VLDB and Partitioning Guide for an example of splitting a partition of a LIST partitioned table.
                http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin002.htm#insertedID16
                >
                Splitting a Partition of a List-Partitioned Table
                You split a list partition by using the ALTER TABLE ... SPLIT PARTITION statement. The SPLIT PARTITION clause enables you to specify a list of literal values that define a partition into which rows with corresponding partitioning key values are inserted. The remaining rows of the original partition are inserted into a second partition whose value list contains the remaining values from the original partition.

                You can optionally specify new attributes for the two partitions that result from the split.

                The following statement splits the partition region_east into two partitions:

                ALTER TABLE sales_by_region
                SPLIT PARTITION region_east VALUES ('CT', 'MA', 'MD')
                INTO
                ( PARTITION region_east_1
                TABLESPACE tbs2,
                PARTITION region_east_2
                STORAGE (INITIAL 8M))
                PARALLEL 5;
                The literal value list for the original region_east partition was specified as:

                PARTITION region_east VALUES ('MA','NY','CT','NH','ME','MD','VA','PA','NJ')
                The two new partitions are:

                region_east_1 with a literal value list of ('CT','MA','MD')

                region_east_2 inheriting the remaining literal value list of ('NY','NH','ME','VA','PA','NJ')

                The individual partitions have new physical attributes specified at the partition level. The operation is executed with parallelism of degree 5.

                You can split a default list partition just like you split any other list partition. This is also the only means of adding a partition to a list-partitioned table that contains a default partition. When you split the default partition, you create a new partition defined by the values that you specify, and a second partition that remains the default partition.

                The following example splits the default partition of sales_by_region, thereby creating a new partition:

                ALTER TABLE sales_by_region
                SPLIT PARTITION region_unknown VALUES ('MT', 'WY', 'ID')
                INTO
                ( PARTITION region_wildwest,
                PARTITION region_unknown);
                • 5. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
                  vpolasa
                  What part of my message was not clear to you?
                  Apparently not since you just posted a bunch of code just like the first bunch.
                  Do you like to just view the code posted by someone or do you spare some time for the effort they have shown in producing their code? I may be doing mistakes, but I believe we learn from our mistakes. I suggest you not to be so arrogant in your words if you think the whole world of Oracle is inside you.
                  Did you miss this part of my reply?
                  If you try my bunch of code, you might realize I didn't miss your part of reply. And this code wasn't given to you out of courtesy. There are many developers who might be looking for some examples to understand a concept. The code I posted may not be accurate, but I hope it'll help a few.
                  If you are using a date you should be using a RANGE partition.
                  You might be mistaken. Using a Range partition for date field may be recommended but not mandatory. In my work place, we get files every day and we partition the data by list on the date field. This has been in practice since few years, and we never saw any performance related issues.
                  Now you can do the split. You get two lists: 'A,B,C' and 'D,E'.
                  If you execute my bunch of code, you'd understand what I'm trying to achieve.
                  Being in a hurry to post your arrogant messages might have led you to miss what I was trying to do.
                  • 6. Re: ORA-14314: resulting List partition(s) must contain atleast 1 value
                    1012855
                    range partition means dividing with some range..
                    say for example salary..
                    people getting different salaries can be divided based on the salary range..
                    and same as with the date..
                    emloyee joined on so and so date..
                    like wise employees joined on each month can be divided using the range..

                    and coming to list partition this groups the data of same category..
                    say from same city or state something like that..

                    for range partitions:
                    ---------------------------

                    if range by salary
                    the following divides into two partitions p1,p2 in which p1 contains employees of sal<2000 and p2 of sal>=2000

                    SQL>alter table example1 split partition sal_range at (2000) into (partition p1,partition p2);

                    for list partitions:
                    -----------------------

                    if list by state
                    and a partition named part contains 8 rows of which 2 of AP,2 of TN,2 of KL,2 of KA
                    you can split a partition into *2 new partitions only* (more than 2 is an error)
                    now divide this

                    SQL>alter table states split partition part values('AP','TN','KL') into (partition rest_of_part,partition Karnataka );

                    the result is partition rest_of_part contains AP,TN,KL and partition Karnataka contains KA( the one which had left assigns to second partition )
                    like wise

                    SQL>alter table states split partition rest_of_part values('AP','TN') into (partition ap_tn,partition Kerala );

                    SQL>alter table states split partition ap_tn values('AP') into (partition Andhra,partition Tamilnadu );

                    finally the result is

                    partition Andhra contains AP
                    partition Tamilnadu contains TN
                    partition Kerala contains KL ad
                    partition Karnataka contains KA

                    Thanks and regards
                    $@&Y

                    Edited by: 1009852 on Jun 5, 2013 1:12 AM