Skip to Main Content
  • Questions
  • Splitting Partition & index creation

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Karthikeyan.

Asked: January 08, 2009 - 9:15 am UTC

Last updated: December 14, 2015 - 11:23 am UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

I have a table where the last parition was created by june and every data after june'08 was entering into the maxpartition column.
Now i need to split the paritions and there are local indexes created on this existing partitions.

The clarification i need is .

When i split the partition, i need to create the corresponding index partition also.

I will be adding the index partition for the partitioned column alone, Will oracle take care of adding the local indexes for the other columns .

Are these the only steps involved in splitting a partition table having local indexes ?

and Tom said...

when you split a partition, the indexes are split for you automagically - you need do nothing.


ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION p2006 VALUES LESS THAN (to_date('01-jan-2007','dd-mon-yyyy')) ,
 10    PARTITION p2007 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')),
 11    PARTITION others VALUES LESS THAN (maxvalue)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t
  2  select sysdate-mod(rownum,366*3+12), rownum, object_name
  3    from all_objects
  4  /

50255 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select min(dt), max(dt), count(*) from t partition(p2006) union all
  2  select min(dt), max(dt), count(*) from t partition(p2007) union all
  3  select min(dt), max(dt), count(*) from t partition(others);

MIN(DT)   MAX(DT)     COUNT(*)
--------- --------- ----------
26-DEC-05 31-DEC-06      16695
01-JAN-07 31-DEC-07      16425
01-JAN-08 08-JAN-09      17135

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) local;

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name, partition_name, status from user_ind_partitions where index_name = 'T_IDX';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P2006                          USABLE
T_IDX                          P2007                          USABLE
T_IDX                          OTHERS                         USABLE

ops$tkyte%ORA10GR2> alter table t split partition others at (to_date('01-jan-2009','dd-mon-yyyy'))
  2  into (partition p2008, partition others);

Table altered.

ops$tkyte%ORA10GR2> select index_name, partition_name, status from user_ind_partitions where index_name = 'T_IDX';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P2008                          UNUSABLE
T_IDX                          P2006                          USABLE
T_IDX                          P2007                          USABLE
T_IDX                          OTHERS                         UNUSABLE

ops$tkyte%ORA10GR2> select min(dt), max(dt), count(*) from t partition(p2006) union all
  2  select min(dt), max(dt), count(*) from t partition(p2007) union all
  3  select min(dt), max(dt), count(*) from t partition(p2008) union all
  4  select min(dt), max(dt), count(*) from t partition(others);

MIN(DT)   MAX(DT)     COUNT(*)
--------- --------- ----------
26-DEC-05 31-DEC-06      16695
01-JAN-07 31-DEC-07      16425
01-JAN-08 31-DEC-08      16768
01-JAN-09 08-JAN-09        367



note however the resulting local index partitions are "unusable", you would have to rebuild them OR if you want the indexes to be maintained, we can do that:
ops$tkyte%ORA10GR2> alter table t split partition others at (to_date('01-jan-2009','dd-mon-yyyy'))
  2  into (partition p2008, partition others)
<b>  3  update indexes;
</b>
Table altered.

ops$tkyte%ORA10GR2> select index_name, partition_name, status from user_ind_partitions where index_name = 'T_IDX';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P2008                          USABLE
T_IDX                          P2006                          USABLE
T_IDX                          P2007                          USABLE
T_IDX                          OTHERS                         USABLE



Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

When the number of index is more! how to split partition?

Karthikeyan, February 09, 2009 - 7:02 am UTC

In my case I used the same syntax , but the split partition is running for hours nearly it passed 40 hours and not completeing.

ALTER TABLE OMR.RAJEEVURE
SPLIT PARTITION RAJMAXDEFAULTD
AT (TO_DATE('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
INTO (PARTITION RAJAFT200908D TABLESPACE RAJE_DATA, PARTITION RAJMAXDEFAULTD TABLESPACE RAJE_DATA)
UPDATE INDEXES;

There are nearly 15 indexes of which 10 are local partitioned indexes.

there are 24 partitions ,each of the old partition is about 300mb (2800580 rows) in size and the max partition is 3gb(9528855 rows) in size, Here iam trying to split the max partition only.


SELECT Message,TRUNC(ELAPSED_SECONDS) || ':' || MOD(ELAPSED_SECONDS,60) ELAPSED,TRUNC(TIME_REMAINING) || ':' || MOD(TIME_REMAINING,60) REMAINING,ROUND(SOFAR/TOTALWORK*100, 2) PROGRESS_PCT FROM V$SESSION_LONGOPS WHERE SID in 2098;

MESSAGE ELAPSED REMAINING PROGRESS_PCT
---------------------------------------------------------------------- ---------- ---------- ------------
Table Scan: OMR.RAJEEV_CORP: 465097 out of 465097 Bl 798:18 0:0 100.00
ocks done

Sort Output: : 56855 out of 75080 Blocks done 29019:39 9302:2 75.73
Table Scan: OMR.RAJEEV_CORP: 551580 out of 551580 Bl 924:24 0:0 100.00
ocks done

Sort Output: : 43218 out of 43218 Blocks done 257:17 0:0 100.00
Sort Output: : 120275 out of 120275 Blocks done 684:24 0:0 100.00
Sort/Merge: : 84382 out of 84382 Blocks done 67:7 0:0 100.00
Table Scan: OMR.RAJEEV_CORP: 369788 out of 369788 Bl 664:4 0:0 100.00
ocks done

Sort/Merge: : 84420 out of 84420 Blocks done 67:7 0:0 100.00
Table Scan: GXPSTG.RU_EXPOSURES: 294140 out of 294140 Blocks done 1880:20 0:0 100.00
Sort Output: : 140878 out of 140878 Blocks done 849:9 0:0 100.00
Sort Output: : 101339 out of 101339 Blocks done 621:21 0:0 100.00
Table Scan: OMR.RAJEEV_CORP: 279565 out of 279565 Bl 498:18 0:0 100.00
ocks done

Table Scan: OMR.RAJEEV_CORP: 198339 out of 198339 Bl 349:49 0:0 100.00
ocks done

Sort/Merge: : 70308 out of 70308 Blocks done 62:2 0:0 100.00
Sort/Merge: : 75140 out of 75140 Blocks done 61:1 0:0 100.00
Table Scan: OMR.RAJEEV_CORP: 646051 out of 646051 Bl 1216:16 0:0 100.00
ocks done

Sort/Merge: : 22264 out of 22264 Blocks done 9:9 0:0 100.00
Sort Output: : 60923 out of 60923 Blocks done 345:45 0:0 100.00
Sort/Merge: : 60950 out of 60950 Blocks done 30:30 0:0 100.00
Sort/Merge: : 120336 out of 120336 Blocks done 89:29 0:0 100.00
Table Scan: OMR.RAJEEV_CORP: 102153 out of 102153 Bl 181:1 0:0 100.00
ocks done

Sort Output: : 22245 out of 22245 Blocks done 130:10 0:0 100.00
Sort/Merge: : 80630 out of 80630 Blocks done 49:49 0:0 100.00
Sort Output: : 84376 out of 84376 Blocks done 65580:0 0:0 100.00
Sort/Merge: : 43253 out of 43253 Blocks done 19:19 0:0 100.00
Sort/Merge: : 140925 out of 140925 Blocks done 120:0 0:0 100.00
Sort/Merge: : 101367 out of 101367 Blocks done 78:18 0:0 100.00
Sort/Merge: : 84390 out of 84390 Blocks done 55:55 0:0 100.00
Sort Output: : 80597 out of 80597 Blocks done 495:15 0:0 100.00

29 rows selected.

22:29:56 >

Question a:
What is that sort output here in the partition split doing, is this the index scan done for all the partitions. Most of the time the partition split is taking much time under this operation?

Question b:
Can i do the split first and then rebuild the indexes with parallel option?

Question c:
When i rebuild the index separately for each partition will the index become unusable?

thank you for your efforts.


Tom Kyte
February 09, 2009 - 7:22 pm UTC

a) it is the merge of the new data back into the index

b) sure, but your data will be offline during that operation. Right now, the data is available for query, if you do it your way, it won't be.

do you need it to be

c) does not compute. If you rebuild an index, why would the index become unusable after the rebuild?!?!

Reader, March 12, 2009 - 12:23 pm UTC

create table tst
(trade_dt_key number(8)
, symbol varchar2(10)
)
PARTITION BY RANGE (TRADE_DT_KEY)
(
PARTITION tst0901 VALUES LESS THAN (20090120),
PARTITION tst090120 VALUES LESS THAN (20090121),
PARTITION tst090121 VALUES LESS THAN (20090122),
PARTITION tst090123 VALUES LESS THAN (20090124)
);

I need to add a partition tst090121 values less than (20090123)

can you advice what I need to do?

Tom Kyte
March 12, 2009 - 1:14 pm UTC

ops$tkyte%ORA10GR2> select partition_name, high_value from user_tab_partitions where table_name = 'TST' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
TST0901                        20090120
TST090120                      20090121
TST090121                      20090122
TST090123                      20090124

ops$tkyte%ORA10GR2> alter table tst split partition tst090123 at
  2  (20090123) into (partition tst20090123, partition tst20090124);

Table altered.

ops$tkyte%ORA10GR2> select partition_name, high_value from user_tab_partitions where table_name = 'TST' order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
TST0901                        20090120
TST090120                      20090121
TST090121                      20090122
TST20090123                    20090123
TST20090124                    20090124



Reader, March 18, 2009 - 3:33 pm UTC

Hello Tom,
I need to add daily partitions from 20071201 to 20071231. DT_KEY is of type number.

Below is the create table script:
CREATE TABLE TST_NEW
(
DT_KEY NUMBER(8) NOT NULL,
RECID NUMBER ,
CREATED_DATE DATE DEFAULT sysdate
)
PARTITION BY RANGE (DT_KEY)
(
PARTITION P01 VALUES LESS THAN (20080101),
PARTITION TST_NEW080101 VALUES LESS THAN (20080102),
PARTITION TST_NEW080102 VALUES LESS THAN (20080103)
);

The below split partition statement works.
ALTER TABLE TST_NEW
SPLIT PARTITION P01 AT (20071231)
INTO (PARTITION TST_NEW071230,PARTITION TST_NEW071231);




If I try to split the partition into more than 2 I get ORA-14046 error. Can you please let me know if there is anyway to add all the 31 partitions for 2007-December in one statement?

ORA-14046: a partition may be split into exactly two new partitions

ALTER TABLE TST_NEW
SPLIT PARTITION P01 AT (20071231)
INTO (PARTITION TST_NEW071229,PARTITION TST_NEW071230,PARTITION TST_NEW071231);
Tom Kyte
March 19, 2009 - 10:14 am UTC

there is not, you split and split and split.

Not Possible to have index on partition

Vikram Shinde, May 22, 2009 - 5:34 am UTC

Hi Tom,

There is lot of diffrence between Partitioned-table having index and Plain-Table with Partitioned-index.

The query you have given above
"select index_name, partition_name, status, NUM_ROWS from user_ind_partitions
where index_name = 'T_INDX';"

will be able to see partitions only Plain-Table with Partitioned-index not partitioned-table with index

so its wrong

I will be thankful if you answer my doubt

Tom Kyte
May 23, 2009 - 10:25 pm UTC

I have no clue what you are trying to say.

There are differences between a non-partitioned table having an index and a partitioned table having one - this is true - sort of. I can create a partitioned index on a non-partitioned table. I can create a non-partitioned index on a partitioned table. There might be some technical differences (I don't think anyone ever said otherwise) but they are closer than you thing.

The query I gave above was, well, against partitioned data. And guess what - it saw data.

ops$tkyte%ORA10GR2> select index_name, partition_name, status from 
user_ind_partitions where index_name = 'T_IDX';

INDEX_NAME                     PARTITION_NAME                 STATUS
------------------------------ ------------------------------ --------
T_IDX                          P2006                          USABLE
T_IDX                          P2007                          USABLE
T_IDX                          OTHERS                         USABLE


so, obviously your statement:

... will be able to see partitions only Plain-Table with Partitioned-index not
partitioned-table with index
...

is false since

a) I had a partitioned table
b) we saw partitions



I will be thankful if you are more clear in exactly what you think "is wrong" - since all I did was demonstrate how it works and cut and pasted into a text field something you could ACTUALLY RUN AND SEE yourself.

try it, you'll see.

Vikram Shinde, August 25, 2009 - 7:29 am UTC

Thank you Tom.

issue with long datatype

satin satin, May 13, 2010 - 9:20 am UTC

I am getting the error .could you please help how can we fix the max value 

SQL> SELECT MAX(TO_NUMBER(high_value))  
  2        FROM ALL_TAB_PARTITIONS
  3        WHERE TABLE_OWNER = UPPER('CUST_FACT')
  4        and table_name =upper('FACT_CUST_REC')
  5        AND PARTITION_NAME <> 'MAXVALUE'
  6  /
SELECT MAX(TO_NUMBER(high_value))
                     *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG


SQL> 

I have split the partitions every month and would like to auotmatethis . Is there any way to do this 

Thanks

How to add subpartition in a Interval partition table subpartition by List

Rajeshwaran, April 16, 2014 - 6:05 pm UTC

Tom,

Can you help me how to add a subpartition on a Interval partitioned table subpartition by list ?

I don't need a default subpartition to hold all values, I need a separate subpartition for each possible values. And separate subpartition values will not be same across different partitions. (if its same, I can define them in subpartition template itself)

create table t(x number,y number, z number)
partition by range(x) interval(1)
subpartition by list(y)
subpartition template
(
 subpartition sp0 values(0)
)
(
 partition p0 values less than (1)
);


When I run the below insert statements, I am getting ora-14300

insert into t values(2,3,1);
insert into t values(3,2,1);
Tom Kyte
April 17, 2014 - 5:18 pm UTC

you'd have to specify the list partitioning scheme you want to use, that would be the only way to get your separate list partitions to happen. You cannot have it make a subpartition "just appear" for each value - it doesn't work that way.

How to add subpartition in a Interval partition table subpartition by List

Rajesh waran, April 17, 2014 - 5:51 pm UTC

Tom,

I did the below demo but all subpartitions ended up into DEFAULT partition.

How can I add subpartitions on composite (interval-list) partition table.

Say, if I know what values are going to be inserted into column x and y, How can I add subpartitions before the load happens? is that possible ? if yes, can you show me ?

rajesh@ORA11G> create table t(x number,y number, z number)
  2  partition by range(x) interval(1)
  3  subpartition by list(y)
  4  (
  5      partition p0 values less than (1)
  6     (
  7             subpartition sp1 values (1) )
  8  );

Table created.

rajesh@ORA11G> insert into t(x,y,z)
  2  select 1,rownum,1
  3  from all_users
  4  where rownum <= 5;

5 rows created.

rajesh@ORA11G> l
  1  select partition_name,
  2    SUBPARTITION_NAME,
  3    HIGH_VALUE
  4  from user_tab_subpartitions
  5* where table_name ='T'
rajesh@ORA11G> /

PARTITION_ SUBPARTITIO HIGH_VA
---------- ----------- -------
P0         SP1         1
SYS_P102   SYS_SUBP101 DEFAULT

2 rows selected.

rajesh@ORA11G>

Tom Kyte
April 17, 2014 - 6:14 pm UTC

well, you only specified the list partitioning scheme for P0, not for subsequently created partitions. You would do that with the template.


ops$tkyte%ORA11GR2> create table t(x number,y number, z number)
  2  partition by range(x) interval(1)
  3  subpartition by list(y)
  4  subpartition template
  5  (
  6          subpartition sp0 values ( 0 ),
  7          subpartition sp1 values ( 1 ),
  8          subpartition sp2 values ( 2 ),
  9          subpartition sp3 values ( 3 ),
 10          subpartition sp4 values ( 4 ),
 11          subpartition sp5 values ( 5 ),
 12          subpartition sp6 values ( 6 )
 13  )
 14  (
 15      partition p0 values less than (1)
 16  );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t(x,y,z)
  2  select 1,rownum,1
  3  from all_users
  4  where rownum <= 5;

5 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select partition_name,
  2         SUBPARTITION_NAME,
  3         HIGH_VALUE
  4    from user_tab_subpartitions
  5   where table_name ='T'
  6  /

PARTITION_NAME                 SUBPARTITION_NAME              HIGH_VALUE
------------------------------ ------------------------------ --------------------
P0                             P0_SP0                         0
P0                             P0_SP1                         1
P0                             P0_SP2                         2
P0                             P0_SP3                         3
P0                             P0_SP4                         4
P0                             P0_SP5                         5
P0                             P0_SP6                         6
SYS_P18113                     SYS_SUBP18106                  0
SYS_P18113                     SYS_SUBP18107                  1
SYS_P18113                     SYS_SUBP18108                  2
SYS_P18113                     SYS_SUBP18109                  3
SYS_P18113                     SYS_SUBP18110                  4
SYS_P18113                     SYS_SUBP18111                  5
SYS_P18113                     SYS_SUBP18112                  6

14 rows selected.



but again, you would have to know the values BEFORE hand. it doesn't make sense to use list partitioning with interval partitioning unless you KNOW what subpartitions you want statically created.


in other words - what is the use of dynamically creating a partition - without any of the list subpartitions you need. You would have to turn around and immediately create them (thus defeating the entire purpose of the interval partition! why dynamic create something - when you have to come back and manually create something else before you can use it!!!!?????)


In short, I don't see you as being a candidate for using interval partitioning here (think about it - you would have to add the partitions you want before the load anyway!!!).


ORA-14300 - on Interval partitioned tables.

Rajeshwaran Jeyabal, December 11, 2015 - 2:18 pm UTC

Team,

We have an application, that is interval partitioned on a numeric column, that insert a Key per batch job execution.

so far we are fine, but now (after 2 years) this batch job executes and inserts partitioned key column values as 1048576, and ended up with ORA-14300

rajesh@ORA11G> create table test_table(a number)
  2  partition by range (a) interval (1)
  3  (partition p1 values less than (2));

Table created.

rajesh@ORA11G>
rajesh@ORA11G> insert into test_table values(1);

1 row created.

rajesh@ORA11G>
rajesh@ORA11G> insert into test_table values(1048575);

1 row created.

rajesh@ORA11G> insert into test_table values(1048576);
insert into test_table values(1048576)
            *
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions


but really the table in database, have close to 800+ partitions only. Any help on to resolve this error.

for time being, we are planning to switch from interval to range and planning to go with this way.

rajesh@ORA11G> alter table test_table set interval();

Table altered.

rajesh@ORA11G> alter table test_table add partition p1048576 values less than ( 1048577 );

Table altered.

rajesh@ORA11G> insert into test_table values(1048576);

1 row created.

rajesh@ORA11G> select * from test_table partition(p1048576);

         A
----------
   1048576

1 row selected.

rajesh@ORA11G>


Please share your inputs.
Chris Saxon
December 14, 2015 - 11:23 am UTC

You specified an interval size of 1, yielding a theoretical partition ceiling of 1048576, hence the error.

Your workaround will suffice (but dont forget, we'll never put *new* partitions in there now that you've 'collapsed' them).

The obvious question is - why an interval size of 1 ? That seems odd.

ORA-14300 - on Interval partitioned tables.

Rajeshwaran Jeyabal, December 15, 2015 - 1:42 pm UTC

The obvious question is - why an interval size of 1 ? That seems odd.

We receive a set of files to be processed in application in 24/7. each time a file recieves we generate a file_key in master_table using sequence, and take that file_key to load the file contents into this table.

Each time this table will be accessed in the context of file_key. so thought of automating add partition concept with interval partition and now we hit this error.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.