Skip to Main Content
  • Questions

  • add subpartitions to partitioned table using alter table

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Bhavesh.

Asked: December 09, 2014 - 4:39 pm UTC

Last updated: March 30, 2022 - 1:19 pm UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,
I have a list based partitioned table as shown below.


create table t( col1 number, col2 date, data_id varchar2(10), archived char(1))
partition by list (data_id)
(partition a_data_id values ('A'),
partition b_data_id values ('B')
);


Can I do the alter table to add subpartitions to this existing table?
The subpartitions should be based on archived column which has 'Y' and 'N' values.

Thanks

and Tom said...

in this case - all the data is going to have to move. I assume you already have Y/N values set. So, each single partition would have to be REBUILT (not split really) into two new partitions.

Rather than attempting to do this bit by bit, a recommendation would be to recreate the table (all of the rows have to move anyway - the N's to a new N partition, the Y's to a Y partition) - and since they all have to move, you might as well use one big DDL to do it all at once - in parallel, using direct path, skipping redo generation if you like (undo will be skipped regardless with direct path).


so - either use a create table as select, or pre-create the table and use insert /*+ append */ into it.

Or you can use dbms_redefinition if you want this operation to be "online".


Unless your data doesn't have the archived bit set - then it might be different as data would only move as it was updated in the future. If that is the case - you'd need three subpartitions - one for existing data and Y/N subpartitions for the future.

But since you said you only wanted two for Y/N, that implies everything will be rewritten - so the most expedient approach will be to direct path load a new table.

Rating

  (4 ratings)

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

Comments

Reply to the question pls !

Rares, July 18, 2018 - 10:31 am UTC

The guy asks how u can do it.
Tell him how and only then make your recommendations based on his specific scenario !
At this point the question remains unanswered : How can u subpartition using alter ?
I, for example, need to do this because I will create the new subpartition to hold my new data. So there will be no data moving involved. I already know about the [ALTER TABLE tablename MODIFY PARTITION partitionname ADD SUBPARTITION subpartionname values ('value')] command. However this does not work when the partition is not already subpartitionned !!!

Connor McDonald
July 20, 2018 - 10:29 am UTC

"How can u subpartition using alter ?"

You cannot.

Even if you could, the data still has to move - because it currently sits in a partition segment, now it has to be moved to new physical segments.


How to decide on number of subpartitions

Paul, March 15, 2022 - 4:41 am UTC

Greetings!

Currently, I have range partitioned table. We want to explore adding HASH subpartitions mainly for concurrent queries to improve the reads by other session waits and to eliminate Hot block contention.
My requirement is, client facing application queries should run faster with SLA in milli seconds.

General Questions around this -

1) What are the factors one should consider when converting to HASH subpartition table. ?

2) How to decide on the number of subpartitions for each partitions ? Any specific formula that has to be followed.

3) I have about 125 sessions /node running concurrent queries on 4-node RAC instances. So about 500 concurrent queries will be running. In this case, Any suggestion or thoughts how many number of subparitions would help to reduce hot block reads/read by other session waits.

Thanks in advance!
Connor McDonald
March 16, 2022 - 3:27 am UTC

1) Be aware that unless your queries include the hash partition key, then you're unlikely to see any significant query improvement. So you need to pick that hash column(s) wisely

But see (3) below

2) Always go for a power of 2.



3) Its pretty rare nowadays to see lots of problems with hot blocks on *query* because of the advent of ASSM. So I would be double checking your stats here. Hot block contention on indexes (ie ins/upd/del) is perhaps a bigger concern. Often "hot blocks" on query is more just cross-instance contention. In that circumstance, judicious use of services is sometimes an easier way forward than restructuring a table

Yearly partition-subpart/Monthly partition-subpart

Pete, March 28, 2022 - 4:03 am UTC

Hi Connor,

Database is on 19.11.

I was testing out converting yearly partition/64-subpart to monthly partition/64-subpart. Table row count is about 6-billion rows.

its range on DATE with Hash subpartition

This test was done to spread the data across more subpartitions to improve the concurrent query execution to reduce wait on read by other session and concurrency waits.

While comparing the test results,, with monthly partition/subpartitions,,, consistent gets got increased very high compared to yearly partition/subpartitions. Please see below.


1) I thought Oracle will reports less I/O as amount of data scan will be less with monthly partition/32-subpart compared to yearly subpartitions. But with monthly subparittions, it was much higher and performance is also bit degraded ?


2) FCT_MKT_PRC_PK is local partitioned unique key.
As per execution plan (provided below), Can you please clarify if Oracle is scanning all partitions (parition range all on the index). Query in question is using partition key/subpartition key in the query where clause.

Thanks!

Yearly partition/64 subpartitions:

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
.....
|  35 |      PARTITION RANGE ALL                    |                         |     2 |       | 10946   (1)| 00:00:01 |1048575|     1 |
|  36 |       PARTITION HASH ITERATOR               |                         |     2 |       | 10946   (1)| 00:00:01 |   KEY |   KEY |
|* 37 |        INDEX RANGE SCAN                     | FCT_MKT_PRC_PK          |     2 |       | 10946   (1)| 00:00:01 |       |       |
|* 38 |     TABLE ACCESS BY LOCAL INDEX ROWID       | FCT_MKT_PRC_O           |     1 |    90 | 10948   (1)| 00:00:01 |     1 |     1 |
|* 39 |    TABLE ACCESS STORAGE FULL                | DIM_SOURCE              |     1 |    40 |     8   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        653  consistent gets
          0  physical reads
          0  redo size
       1092  bytes sent via SQL*Net to client
       9201  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed


Monthly parition/32 subpartitions:

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
...
|  35 |      PARTITION RANGE ALL                    |                         |     2 |       | 43208   (1)| 00:00:02 |   675 |     1 |
|  36 |       PARTITION HASH ITERATOR               |                         |     2 |       | 43208   (1)| 00:00:02 |   KEY |   KEY |
|* 37 |        INDEX RANGE SCAN                     | FCT_MKT_PRC_PK4         |     2 |       | 43208   (1)| 00:00:02 |       |       |
|* 38 |     TABLE ACCESS BY LOCAL INDEX ROWID       | FCT_MKT_PRC_DBA3        |     1 |    96 | 43210   (1)| 00:00:02 |     1 |     1 |
|* 39 |    TABLE ACCESS STORAGE FULL                | DIM_SOURCE              |     1 |    40 |     8   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         10  recursive calls
          2  db block gets
       6913  consistent gets
          4  physical reads
          0  redo size
       1506  bytes sent via SQL*Net to client
       9210  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          7  rows processed

Chris Saxon
March 29, 2022 - 12:56 pm UTC

The database doesn't know which of the range partitions the row(s) you're reading are in. So it's scanning through all the partitions, then picking a subpartition within these.

Going for monthly partitions (instead of yearly) => more partitions to read => more work to do

You said in your previous comment you wanted to eliminate Hot block contention.

What is this on? The primary key index? If so, instead of adding subpartitions it may be better to:

- Range partition the table
- Global hash partition the index

oracle partition /subpartition continuation

Pete, March 30, 2022 - 7:23 am UTC

Hi Chris,

Continuation from previous post,

I am noticing lot of "read by other session" waits on table
subpartition when 50 to 100 session runs concurrently the same queries against the table. Following are the wait events seen while monitoring the sessions running the query.

read by other session
cell single block read request
library cache: mutex X
gc buffer busy acquire
latch: cache buffers chains
cursor: pin S wait on X
cursor: pin S

>>
You said in your previous comment you wanted to eliminate Hot block contention.
What is this on? The primary key index? If so, instead of adding subpartitions it may be better to:

- Range partition the table
- Global hash partition the index
>>

Read by other session waits actually showing up on the table subpartitions. I will try your suggestion as well. that is,

1. monthly range partition
2. global hash partition the primary key index


Thanks!

Chris Saxon
March 30, 2022 - 1:19 pm UTC

Let us know how it goes
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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.