[20151004]表中最大分區數.txt

[20151004]表中最大分區數.txt

--oracle的表最大分區數能達到多少。依稀記得以前2^20-1 .

SCOTT@test01p> select power(2,20)-1 N10 from dual ;

                  N10

---------------------

              1048575

--參考連結：

<a href="http://blog.itpub.net/267265/viewspace-1061109/">http://blog.itpub.net/267265/viewspace-1061109/</a>

D:\tools\rlwrap&gt;oerr ora 14300

14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"

// *Cause:  The row inserted had a partitioning key that maps to a partition number greater than 1048575

// *Action  Ensure that the partitioning key falls within 1048575 partitions or subpartitions.

--從這個錯誤提示也可以旁證最大分區數1048575。如何驗證呢？

--如果真要建立這樣的表，估計資料字典的空間消耗會很大，不知道要多少時間。

1.建立如下測試環境：

SCOTT@test01p&gt; @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

------------------------------ -------------- -------------------------------------------------------------------------------- ----------

IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

CREATE TABLE partition_interval_tab (

  n1 NUMBER

,trade_date DATE

,n2 number

)

PARTITION BY RANGE (trade_date)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

PARTITION p_1 values LESS THAN (TO_DATE(' 2013-11-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

,PARTITION p_2 values LESS THAN (TO_DATE(' 2013-12-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

);

insert into partition_interval_tab values (1, trunc(sysdate), 100);

insert into partition_interval_tab values (2, trunc(sysdate + 20), 200);

commit;

SCOTT@test01p&gt; select partition_name,compression,compress_for from user_tab_partitions where table_name='PARTITION_INTERVAL_TAB';

PARTITION_NAME       COMPRESS COMPRESS_FOR

-------------------- -------- ------------------------------

P_1                  DISABLED

P_2                  DISABLED

SYS_P7753            DISABLED

SYS_P7754            DISABLED

--可以發現建立了4個分區。

2.測試：

SCOTT@test01p&gt; select * from partition_interval_tab where trade_date &gt;= trunc(sysdate+700);

no rows selected

SCOTT@test01p&gt; @dpc ''  partition

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  6jc9tqsupurcm, child number 0

select * from partition_interval_tab where trade_date &gt;=trunc(sysdate+700)

Plan hash value: 834375401

--------------------------------------------------------------------------------------------------------------------

| Id  | Operation                | Name                   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop |

|   0 | SELECT STATEMENT         |                        |        |       |    27 (100)|          |       |       |

|   1 |  PARTITION RANGE ITERATOR|                        |      1 |    35 |    27   (0)| 00:00:01 |   KEY |1048575|

|*  2 |   TABLE ACCESS FULL      | PARTITION_INTERVAL_TAB |      1 |    35 |    27   (0)| 00:00:01 |   KEY |1048575|

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1

   2 - SEL$1 / PARTITION_INTERVAL_TAB@SEL$1

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("TRADE_DATE"&gt;=TRUNC(SYSDATE@!+700))

--從另外的角度認證最大分區數=1048575.