[20151004]表中最大分区数.txt
--oracle的表最大分区数能达到多少。依稀记得以前2^20-1 .
SCOTT@test01p> select power(2,20)-1 N10 from dual ;
N10
---------------------
1048575
--参考链接：
http://blog.itpub.net/267265/viewspace-1061109/
D:\tools\rlwrap>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> @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> 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> select * from partition_interval_tab where trade_date >= trunc(sysdate+700);
no rows selected
SCOTT@test01p> @dpc '' partition
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6jc9tqsupurcm, child number 0
-------------------------------------
select * from partition_interval_tab where trade_date >=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">=TRUNC(SYSDATE@!+700))
--从另外的角度认证最大分区数=1048575.