[20151004]表中最大分区数.txt

[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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1813448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1813448/