Filed under: 12c,Indexing,Oracle,Partitioning,Upgrades — Jonathan Lewis @ 10:13 am BST Jun 9,2017
昨天，我在Ukoug Database SIG的演讲结束时，我用一张幻灯片总结了（大部分）我提出的观点：
在12.2中，您可以：在一次在线操作中，将一个简单的堆表转换为具有多列自动列表分区的分区表、使用部分索引、只读段、并过滤掉不需要的数据。
昨晚，我认为我应该演示上述观点。因此，我使用以下代码创建了一个演示用的堆表：
rem rem Script: 122_features.sql rem Author: Jonathan Lewis rem Dated: June 2017 rem rem Last tested rem 12.2.0.1 rem create table t1( date_start not null, date_end not null, id not null, client_id, resort_code, uk_flag, v1, padding, constraint t1_range_ck check ((date_end - date_start) in (7, 14, 21)) ) segment creation immediate nologging as with generator as ( select rownum id from dual connect by level <= 1e4 ) select trunc(sysdate,'yyyy') + 7 * mod(rownum, 8) date_start, trunc(sysdate,'yyyy') + 7 * (mod(rownum, 8) + trunc(dbms_random.value(1,4))) date_end, rownum id, trunc(dbms_random.value(1e5,2e5)) client_id, trunc(dbms_random.value(1e4,2e4)) resort_code, case when mod(rownum,275) = 0 then 1 end uk_flag, lpad(rownum,10,'0') v1, lpad('x',100,'x') padding from generator v1, generator v2 where rownum <= 1e5 -- > "GT" inserted to avoid WordPress formatting issue ; create index t1_client_idx on t1(client_id); create index t1_resort_idx on t1(resort_code); create index t1_ukflag_idx on t1(uk_flag); alter table t1 add constraint t1_pk primary key(id);复制
我已经创建了一张表，模拟一家旅行公司安排的假期正好持续一、二、三周，而且（为了方便起见）它们都在一周的同一天开始。因此，我为每一行生成一个开始日期和结束日期，确保开始日期是从基准日期起7天的倍数，而结束日期是7天、14天或21天之后。我还创建了几个索引和主键约束。而且，在表上有一个特殊的标志列，标明预订的英国的假日中的一小部分。
最后，当数据集变得太大时，我决定对表进行分区，我脑海中浮现的一个明显的分区思想，就是将具有相同开始日期和持续时间的假日都在同一个分区中，每个分区都有一个开始/持续时间。
我还决定计划将老的数据置为只读。一旦英国的假日成为历史，我就不感兴趣了，所以我要去掉一些。
保护主键的索引必将是全局索引，因为它不包括分区键；由于uk_flag上的索引包含少量数据，所以我也会保留全局索引，但我希望其他两个索引都是本地的-但对于较旧的数据，我并不关注CLIENT_ID列上是否保留索引。
并且，在我重构数据时，我并不希望停掉应用。
因此，我的SQL语句如下：
alter table t1 modify partition by list (date_start, date_end) automatic ( partition p11 values (to_date('01-Jan-2017'),to_date('08-Jan-2017')) indexing off read only, partition p12 values (to_date('01-Jan-2017'),to_date('15-Jan-2017')) indexing off read only, partition p13 values (to_date('01-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p21 values (to_date('08-Jan-2017'),to_date('15-Jan-2017')) indexing off read only, partition p22 values (to_date('08-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p23 values (to_date('08-Jan-2017'),to_date('29-Jan-2017')) indexing off read only, partition p31 values (to_date('15-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p32 values (to_date('15-Jan-2017'),to_date('29-Jan-2017')) indexing off read only, partition p33 values (to_date('15-Jan-2017'),to_date('05-Feb-2017')) indexing off read only ) including rows where uk_flag is null or (date_start > to_date('01-feb-2017','dd-mon-yyyy')) online update indexes ( t1_client_idx local indexing partial, t1_resort_idx local, t1_ukflag_idx indexing partial ) ;复制
关键点：
partition by list (date_start, date_end) — 多列列表分区
automatic — 如果对到达的数据还没有存在的分区，那么会自动新建一个。
indexing off — 我的部分分区（预定义中最早的那个）将面临部分索引。
read only — 我的部分分区（预定义中最早的那个）将置为只读。
including rows where — 部分行将不会出现在拷贝阶段 [1]
online — Oracle将在我复制阶段记录日志数据，并在复制完成后应用日志。
update indexes – 指定一些索引的细节。[2]
local — 部分索引将被建成本地分区索引。
indexing partial —部分重建的索引并不持有数据(指已声明“indexing off”的分区）。
我做了两个标注：
[1] – 复制是一致读取的，因此在复制时插入的数据仍然会出现在最终的表中，使它看起来好像including rows子句失效了。
[2] – 包含分区键的索引将自动创建为本地索引（但您可以在此处声明它们为全局索引，或者如果需要，可以将其声明为全局分区索引）。手册在这一点上有点混乱；它建议将带前缀的索引创建为本地索引，但随后将“prefixed”定义为“包含分区键”，而不是通常的“以分区键开头”。
大功告成 --除非做了：
所有的测试都是正确的
负载测试，以查看当大量新假日被预订和当前假日被修改时它的行为
清理一些不应该存在的“意外”分区
将一些自动生成的表分区更改为“索引关闭”（如果需要）
下面是几个查询（带结果）显示了这一语句的效果：
select count(*) from t1; /* COUNT(*) ---------- 99773 -- some rows (old UK) have disappeared from the original 100,000 */ select index_name, partitioned, status, leaf_blocks, num_rows , indexing, orphaned_entries from user_indexes where table_name = 'T1' order by partitioned, index_name ; /* INDEX_NAME PAR STATUS LEAF_BLOCKS NUM_ROWS INDEXIN ORP -------------------- --- -------- ----------- ---------- ------- --- T1_PK NO VALID 263 99773 FULL NO T1_UKFLAG_IDX NO VALID 1 136 PARTIAL NO T1_CLIENT_IDX YES N/A 149 62409 PARTIAL NO T1_RESORT_IDX YES N/A 239 99773 FULL NO -- Indexes: Local or global, full or partial. */ select segment_type, segment_name, count(*) from user_segments group by segment_type, segment_name order by segment_type desc, segment_name ; /* SEGMENT_TYPE SEGMENT_NAME COUNT(*) ------------------ ------------------------- ---------- TABLE PARTITION T1 24 INDEX PARTITION T1_CLIENT_IDX 15 INDEX PARTITION T1_RESORT_IDX 24 INDEX T1_PK 1 INDEX T1_UKFLAG_IDX 1 -- One local index has fewer segments than the other */ set linesize 180 set trimspool on column high_value format a85 break on index_name skip 1 set pagesize 200 select index_name, status, leaf_blocks, num_rows, partition_name, high_value from user_ind_partitions where index_name = 'T1_CLIENT_IDX' -- index_name like 'T1%' order by index_name, partition_position ; /* INDEX_NAME STATUS LEAF_BLOCKS NUM_ROWS PARTITION_NAME HIGH_VALUE -------------------- -------- ----------- ---------- ---------------------- ------------------------------------------------------------------------------------- T1_CLIENT_IDX UNUSABLE 0 0 P11 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P12 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P13 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P21 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P22 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P23 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P31 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P32 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P33 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-02-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) USABLE 10 4126 SYS_P1528 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4198 SYS_P1529 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4211 SYS_P1530 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4214 SYS_P1531 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4195 SYS_P1532 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4113 SYS_P1533 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 9 4027 SYS_P1534 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4217 SYS_P1535 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4167 SYS_P1536 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4230 SYS_P1537 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4113 SYS_P1538 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4069 SYS_P1539 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4215 SYS_P1540 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4138 SYS_P1541 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4176 SYS_P1542 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) */复制
我将索引分区的输出限制为启用了partial indexing的索引，以显示预定义的分区被标记为不可用，并且，正如您从段摘要中推导出的那样，那些不可用的索引分区没有分配任何段空间。
压力测试就留给感兴趣的读者吧。
2020年2月更新：
我刚刚想起了一个问题，我在写了这篇文章几个月后，发现了一个可能与此相关的问题。如果将数据加载到一个间隔分区表中，您可能会发现您需要一个非常大的PGA分配，而PGA中发生的任何事情都可能导致临时表空间上的大量I/O。这个问题在某种程度上与Oracle必须自动创建的分区数有关，但在加载范围分区表时不会出现。
我还没有测试当试图将表修改为间隔分区时，是否会出现同样的现象，但这应该是在生产系统上使用该方法之前，您应该做的一个测试，以便能够有必要的内存/空间/IO。
原文链接：https://jonathanlewis.wordpress.com/2017/06/09/12-2-partitions/
原文内容：
12.2 Partitions
Filed under: 12c,Indexing,Oracle,Partitioning,Upgrades — Jonathan Lewis @ 10:13 am BST Jun 9,2017
At the end of my presentation to the UKOUG Database SIG yesterday I summed up (most of) the points I’d made with a slide making the claim:
In 12.2 you can: convert a simple heap table to partitioned with multi-column automatic list partitions, partially indexed, with read only segments, filtering out unwanted data, online in one operation.
Last night I decided I ought to demonstrate the claim – so here’s a little code to create a simple heap table that I can demonstrate on:
rem rem Script: 122_features.sql rem Author: Jonathan Lewis rem Dated: June 2017 rem rem Last tested rem 12.2.0.1 rem create table t1( date_start not null, date_end not null, id not null, client_id, resort_code, uk_flag, v1, padding, constraint t1_range_ck check ((date_end - date_start) in (7, 14, 21)) ) segment creation immediate nologging as with generator as ( select rownum id from dual connect by level <= 1e4 ) select trunc(sysdate,'yyyy') + 7 * mod(rownum, 8) date_start, trunc(sysdate,'yyyy') + 7 * (mod(rownum, 8) + trunc(dbms_random.value(1,4))) date_end, rownum id, trunc(dbms_random.value(1e5,2e5)) client_id, trunc(dbms_random.value(1e4,2e4)) resort_code, case when mod(rownum,275) = 0 then 1 end uk_flag, lpad(rownum,10,'0') v1, lpad('x',100,'x') padding from generator v1, generator v2 where rownum <= 1e5 -- > "GT" inserted to avoid WordPress formatting issue ; create index t1_client_idx on t1(client_id); create index t1_resort_idx on t1(resort_code); create index t1_ukflag_idx on t1(uk_flag); alter table t1 add constraint t1_pk primary key(id);复制
I’ve got a table which models a travel company that arranges holidays that last exactly one, two, or three weeks and (for convenience) they all start on the same day of the week. So I generate a start and end date for each row, making sure the start date is a multiple of seven days from a base date while the end date is 7, 14, or 21 days later. I’ve got a few indexes on the data and a primary key constraint. There’s a special flag column on the table for holidays in the UK which happens to identify a small parcentage of the holidays booked.
Eventually, when the data set gets too big, I decide that I want to partition the table and the obvious partitioning idea that springs to mind is to partition it so that holidays with the same start date and duration are all in the same partition and each partition holds a single start/duration.
I’ve also decided that I’m going to make old data read-only, and I’m not interested in the UK holidays once they’ve gone into history so I’m going to get rid of some of them.
The index protecting the primary key will have to be global since it won’t contain the partition key; since the index on uk_flag covers a small amount of data I’m going to keep that global as well, but I want the other two indexes to be local – but for the older data I’m not really interested in keeping the index on client id.
And I don’t want to stop the application while I’m restructuring the data.
So here’s my one SQL statement:
alter table t1 modify partition by list (date_start, date_end) automatic ( partition p11 values (to_date('01-Jan-2017'),to_date('08-Jan-2017')) indexing off read only, partition p12 values (to_date('01-Jan-2017'),to_date('15-Jan-2017')) indexing off read only, partition p13 values (to_date('01-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p21 values (to_date('08-Jan-2017'),to_date('15-Jan-2017')) indexing off read only, partition p22 values (to_date('08-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p23 values (to_date('08-Jan-2017'),to_date('29-Jan-2017')) indexing off read only, partition p31 values (to_date('15-Jan-2017'),to_date('22-Jan-2017')) indexing off read only, partition p32 values (to_date('15-Jan-2017'),to_date('29-Jan-2017')) indexing off read only, partition p33 values (to_date('15-Jan-2017'),to_date('05-Feb-2017')) indexing off read only ) including rows where uk_flag is null or (date_start > to_date('01-feb-2017','dd-mon-yyyy')) online update indexes ( t1_client_idx local indexing partial, t1_resort_idx local, t1_ukflag_idx indexing partial ) ;复制
Key Points
partition by list (date_start, date_end) — partitioned by a multi-column list
automatic — if data arrives for which there is no existing partition a new one will be created
indexing off — some of my partitions (the pre-defined (oldest) ones) will be subject to partial indexing
read only — some of my partitions (the pre-defined (oldest) ones) will be made read only
including rows where — some of my rows will disappear during copying [1]
online — Oracle will be journalling the data while I copy and will apply the journal after the copying completes
update indexes – specify some details about indexes [2]
local — some of the rebuilt indexes will be local
indexing partial — some of the rebuilt indexes will not hold data (viz: for the partitions declared “indexing off”)
I’ve footnoted a couple of the entries:
[1] – the copy is done read-consistently so data inserted while the copy takes place will still appear in the final table, even if it looks as if it should have failed the including rows clause.
[2] – indexes which include the partition key will automatically be created as local indexes (but at this point you can declare them here as global, or globally partitioned if you want to). The manual is a little confusing at this point; it suggests that prefixed indexes will be created as local indexes but then defines “prefixed” to mean “contains the partition key” rather than the usual “starts with the partition key”.
Job done – except for
the exhaustive tests that it’s been done correctly
the load test to see how it behaves when lots of new holidays are being booked and current ones being modified
a little bit of clearing up of “surprise” partitions that shouldn’t be there
changing some of the automatically generated table partitions to be “indexing off” (if and when necessary)
Here are a few queries – with results – showing the effects this one statement had:
select count(*) from t1; /* COUNT(*) ---------- 99773 -- some rows (old UK) have disappeared from the original 100,000 */ select index_name, partitioned, status, leaf_blocks, num_rows , indexing, orphaned_entries from user_indexes where table_name = 'T1' order by partitioned, index_name ; /* INDEX_NAME PAR STATUS LEAF_BLOCKS NUM_ROWS INDEXIN ORP -------------------- --- -------- ----------- ---------- ------- --- T1_PK NO VALID 263 99773 FULL NO T1_UKFLAG_IDX NO VALID 1 136 PARTIAL NO T1_CLIENT_IDX YES N/A 149 62409 PARTIAL NO T1_RESORT_IDX YES N/A 239 99773 FULL NO -- Indexes: Local or global, full or partial. */ select segment_type, segment_name, count(*) from user_segments group by segment_type, segment_name order by segment_type desc, segment_name ; /* SEGMENT_TYPE SEGMENT_NAME COUNT(*) ------------------ ------------------------- ---------- TABLE PARTITION T1 24 INDEX PARTITION T1_CLIENT_IDX 15 INDEX PARTITION T1_RESORT_IDX 24 INDEX T1_PK 1 INDEX T1_UKFLAG_IDX 1 -- One local index has fewer segments than the other */ set linesize 180 set trimspool on column high_value format a85 break on index_name skip 1 set pagesize 200 select index_name, status, leaf_blocks, num_rows, partition_name, high_value from user_ind_partitions where index_name = 'T1_CLIENT_IDX' -- index_name like 'T1%' order by index_name, partition_position ; /* INDEX_NAME STATUS LEAF_BLOCKS NUM_ROWS PARTITION_NAME HIGH_VALUE -------------------- -------- ----------- ---------- ---------------------- ------------------------------------------------------------------------------------- T1_CLIENT_IDX UNUSABLE 0 0 P11 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P12 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P13 ( TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P21 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P22 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P23 ( TO_DATE(' 2017-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P31 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P32 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-01-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) UNUSABLE 0 0 P33 ( TO_DATE(' 2017-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') , TO_DATE(' 2017-02-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') ) USABLE 10 4126 SYS_P1528 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4198 SYS_P1529 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4211 SYS_P1530 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4214 SYS_P1531 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4195 SYS_P1532 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4113 SYS_P1533 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 9 4027 SYS_P1534 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4217 SYS_P1535 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4167 SYS_P1536 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4230 SYS_P1537 ( TO_DATE(' 2017-01-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4113 SYS_P1538 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4069 SYS_P1539 ( TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-03-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4215 SYS_P1540 ( TO_DATE(' 2017-01-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4138 SYS_P1541 ( TO_DATE(' 2017-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) USABLE 10 4176 SYS_P1542 ( TO_DATE(' 2017-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') , TO_DATE(' 2017-02-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian') ) */复制
I’ve limited the index partition output to the index with partial indexing enabled to show that its pre-defined partitions are marked as unusable and, as you can infer from the segment summary, those unusable index partition don’t have any segment space allocated to them.
Stress tests are left to the interested reader.
Update Feb 2020
I’ve just been reminded of a problem I discovered a few months after writing this note that might be relevant. If you load data into an interval partitioned table you may find that you need a very large PGA allocation for no obvious reason, and whatever’s going on in the PGA may result in a massive amount of I/O on the temporary tablespace. The problem relates in some way to the number of partitions that Oracle has to create automatically, and doesn’t appear when you’re loading a range-partitioned table.
I haven’t yet tested whether the same phenomenon appears when you try to modify a table to be interval partitioned, but it’s a test you should do before you commit to using the method on a production system so that you can allow for the necessary memory/space/IO.
