6 Replies Latest reply: Feb 11, 2013 5:47 PM by Solomon Yakobson RSS

    Maximum number of partitions allowed per table.

    Solomon Yakobson
      Interesting findings with interval partitioning:
      SQL> SELECT  *
        2    FROM  v$version
        3  /
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE    11.2.0.3.0      Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      SQL> DROP TABLE tbl PURGE
        2  /
      
      Table dropped.
      
      SQL> CREATE TABLE tbl(
        2                   id number(6),
        3                   dt date
        4                  )
        5    PARTITION BY RANGE(dt)
        6      INTERVAL (INTERVAL '1' DAY)
        7      (
        8       PARTITION p1 VALUES LESS THAN (date '-857-12-31')
        9      )
       10  /
      
      Table created.
      
      SQL> select  partition_name,
        2          high_value
        3    from  user_tab_partitions
        4    where table_name = 'TBL'
        5  /
      
      PARTITION_NAME HIGH_VALUE
      -------------- ------------------------------------------------------------
      P1             TO_DATE('-0857-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
                     LS_CALENDAR=GREGORIAN')
      
      
      SQL> INSERT
        2    INTO tbl
        3    VALUES(
        4           1,
        5           sysdate
        6          )
        7  /
      
      1 row created.
      
      SQL> DROP TABLE tbl PURGE
        2  /
      
      Table dropped.
      
      SQL> CREATE TABLE tbl(
        2                   id number(6),
        3                   dt date
        4                  )
        5    PARTITION BY RANGE(dt)
        6      INTERVAL (INTERVAL '1' DAY)
        7      (
        8       PARTITION p1 VALUES LESS THAN (date '-858-01-01')
        9      )
       10  /
      
      Table created.
      
      SQL> select  partition_name,
        2          high_value
        3    from  user_tab_partitions
        4    where table_name = 'TBL'
        5  /
      
      PARTITION_NAME HIGH_VALUE
      -------------- ------------------------------------------------------------
      P1             TO_DATE('-0858-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
                     LS_CALENDAR=GREGORIAN')
      
      
      SQL> INSERT
        2    INTO tbl
        3    VALUES(
        4           1,
        5           sysdate
        6          )
        7  /
        INTO tbl
             *
      ERROR at line 2:
      ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions
      
      
      SQL> 
      From Logical Database Limits:

      Maximum number of partitions allowed per table or index: 1024K - 1

      I always thought limit implies to number of actual, not potential partitions, however it looks like I was wrong, although it makes not much sense to limit potential and not actual partitions:
      SQL> select  trunc(sysdate) - date '-858-01-01',
        2          1024 * 1024 - 1
        3    from  dual
        4  /
      
      TRUNC(SYSDATE)-DATE'-858-01-01' 1024*1024-1
      ------------------------------- -----------
                              1048661     1048575
      
      SQL> select  to_char(DATE'-858-01-01' + 1048575,'MM/DD/YYYY')
        2    from  dual
        3  /
      
      TO_CHAR(DA
      ----------
      11/17/2012
      
      SQL> 
      So tomorrow "magic" date should increase by one day. I'll test it. But more interesting if tomorrow I will be able to insert a row that forms a new partition into table TBL.

      SY.
        • 1. Re: Maximum number of partitions allowed per table.
          rp0428
          >
          I always thought limit implies to number of actual, not potential partitions, however it looks like I was wrong, although it makes not much sense to limit potential and not actual partitions
          >
          That 'sense' thing could probably be argued either way. The other argument is that Oracle has to be able to automatically create any partition required and it can only create 1024k - 1. So if you create yours with sysdate how could it create all of the others?

          Practically speaking though you are right. There is nothing inherently wrong with having a partition that includes data for more than one day if that partition is a 'hole' in the middle of the 1024k range because all of the other partitions had already been created. I mean no great loss.
          • 2. Re: Maximum number of partitions allowed per table.
            mtefft
            One of the cool things about interval partitions is that they can be sparse. And that you don't have to bake the universe of values into the partitioning definition.

            But this revlelation shrnks the universe substantially.
            • 3. Re: Maximum number of partitions allowed per table.
              Solomon Yakobson
              rp0428 wrote:
              The other argument is that Oracle has to be able to automatically create any partition required and it can only create 1024k - 1. So if you create yours with sysdate how could it create all of the others?
              Not sure I follow. What is the purpose of counting potential partitions? Partition part# iin sys.tabpart$ is not assigned based on potential partition position. If I issue a DDL to create new partition regardless of interval/non-interval partitioning Oracle has to check how many partitions table has so far or po and raise same/similar exception if partition I am asking to create is over the limit. And, in any case, knowing we can create all potential partitions at table create time doesn't mean I will not try to insert data outside the range. So there is absolutely no guarantee Oracle can automatically create any partition requested. Again, I don't understand why creating non-interval partitioned table with a single initial partition has partition count of 1:
              SQL> DROP TABLE tbl1 PURGE
                2  /
              
              Table dropped.
              
              SQL> CREATE TABLE tbl1(
                2                    id number(6),
                3                    dt date
                4                   )
                5    PARTITION BY RANGE(dt)
                6      (
                7       PARTITION p1 VALUES LESS THAN (date '-857-12-31')
                8      )
                9  /
              
              Table created.
              
              SQL> SELECT  partition_count
                2    FROM  user_part_tables
                3    WHERE table_name = 'TBL1'
                4  /
              
              PARTITION_COUNT
              ---------------
                            1
              
              SQL>
              And interval partitioned table with same single initial partition has partition count of 1048575:
              SQL> CREATE TABLE tbl1(
                2                    id number(6),
                3                    dt date
                4                   )
                5    PARTITION BY RANGE(dt)
                6      INTERVAL (INTERVAL '1' DAY)
                7      (
                8       PARTITION p1 VALUES LESS THAN (date '-857-12-31')
                9      )
               10  /
              
              Table created.
              
              SQL> SELECT  partition_count
                2    FROM  user_part_tables
                3    WHERE table_name = 'TBL1'
                4  /
              
              PARTITION_COUNT
              ---------------
                      1048575
              
              SQL> 
              Would be interesting to find out what forces Oracle to go into potential partition mode for interval partitioning.

              SY.
              • 4. Re: Maximum number of partitions allowed per table.
                rp0428
                >
                Would be interesting to find out what forces Oracle to go into potential partition mode for interval partitioning.
                >
                Probably only Oracle knows the answer to that.

                My working hypothesis would be that is is due to the code-path difference between the explicit creation of partitions and the implicit creation that occurs with INTERVAL partitioning.

                When partitions are created explicitly the even is strictly a DDL operation and that event can be trapped with a system trigger.

                But with INTERVAL partitioning the creation of new partitions is intrinsically part of an INSERTor UPDATE DML operation. Those implicit partition creations CANNOT be trapped with a system trigger.

                It is also not clear if implicit partition creations are DML or not. They can't be rolled back. As far as I can tell the creation of the partition is done in its own transaction.

                Jonathan Lewis provides an example of being unable to trap implicit partition creation here:
                http://jonathanlewis.wordpress.com/2012/09/08/ddl-triggers/
                >
                If I issue a DDL to create new partition regardless of interval/non-interval partitioning Oracle has to check how many partitions table has so far or po and raise same/similar exception if partition I am asking to create is over the limit.
                >
                Yes - but the explicit creation of a new partition is strictly a DDL operation while the implicit creation, as stated above, is part of a DML operation.

                So my first thought is that Oracle may be trying to avoid the IS-A-NEW-PARTITION-ALLOWED check for every implicit creation of a new partition. That check could conceivably be required for EVERY RECORD that is being inserted.

                By doing the check one time up-front Oracle would know that it NEVER needs to perform the partition-count check at run-time. So maybe it is an optimization.
                • 5. Re: Maximum number of partitions allowed per table.
                  Hoek
                  FWIIW, on MOS/Metalink the following bug can be found:
                  Bug 13604209 : ORA-14300 TRYING TO INSERT INTO INTERVAL PARTITION TABLE
                  (workaround: none (sofar))
                  • 6. Re: Maximum number of partitions allowed per table.
                    Solomon Yakobson
                    Hoek wrote:
                    Bug 13604209 : ORA-14300 TRYING TO INSERT INTO INTERVAL PARTITION TABLE
                    Thanks.

                    SY.