Showing posts with label subpartitioning. Show all posts
Showing posts with label subpartitioning. Show all posts

Friday, May 21, 2021

Workaround for ORA-14634 during an attempt to split a partition containing subpartitions

Short background:
I had a LIST/RANGE partitioned tables that needed a new partition.

The table is partitioned BY LIST on column STREAM, and subpartitioned BY RANGE on column LASTUPDATED. 

Each subpartition is placed in a tablespace according to the year in the LASTUPDATED column. 
So for example, no matter which partition the subpartition belongs to, it will always go into tablespace DATA_2019 if the LASTUPDATED column has a value which falls within the boundaries of year 2019. 

This is called vertically striping of partitions and is often, but now always, set up using a subpartition template.


DDL for the table:
  CREATE TABLE CATEGORIES
(
  UID              VARCHAR2(255 BYTE),
  CREATED          TIMESTAMP(6)                 NOT NULL,
  LASTUPDATED      TIMESTAMP(6)                 NOT NULL,
  BODY             CLOB,
  STREAM           VARCHAR2(255 BYTE)
)
LOB (SBODY) STORE AS SECUREFILE BLOB (
  TABLESPACE  DATA1
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
)
COMPRESS FOR OLTP
TABLESPACE DATA1
PARTITION BY LIST (STREAM)
SUBPARTITION BY RANGE (LASTUPDATED)
(
 -- partition definition ..
 	-- subpartition definition..
);
  
I had the following SQL intended to split the partition OTHERS into a brand new partition called DIGITAL_MEDIA:
ALTER TABLE categories
 SPLIT PARTITION OTHERS VALUES
 ('audiobooks')
 INTO (
       PARTITION DIGITAL_MEDIA
       TABLESPACE DATA1
     (
     	 -- 2016
        SUBPARTITION SP_dm_older 
        VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1,
        subpartition SP_dm_201601 
        VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016,
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),
        VALUES LESS THAN (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016,
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),
        .
        .
        .
        subpartition SP_dm_202411 
        VALUES LESS THAN (TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE ATOMHOPPER_2024 ),
        subpartition SP_dm_202412 
        VALUES LESS THAN (TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2024
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA_2024 ),
        SUBPARTITION SP_DM_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1
        LOB (BLOB) STORE AS SECUREFILE  ( TABLESPACE DATA1)
        )
     ,
PARTITION OTHERS
)
ONLINE
UPDATE GLOBAL INDEXES;
There were a total of 110 subpartitioned listed in the above DDL. When trying to execute the script, I received:
ORA-14634: Subpartition descriptions cannot be specified during the SPLIT/MERGE of a partition of a Range-List partitioned table
Looking it up:
oerr ora 14634
// *Cause:  During a split or a merge of a partition of a range list
//          partitioned table you cannot specify any subpartitioning
//          information for the resulting partition (s)
// *Action: Remove all subpartitioning information from the DDL.
A rather clear message, which I followed, and thus changed my DDL to this:
ALTER TABLE categories
 SPLIT PARTITION OTHERS VALUES
 ('audiobooks')
 INTO (
       PARTITION DIGITAL_MEDIA
     , PARTITION OTHERS)
ONLINE
UPDATE GLOBAL INDEXES;
indeed a much simpler syntax.
 
It parsed without errors, and the result was a new partition, with system-generated names, placed in the correct tablespaces.

Let's verify the results:
SELECT table_name,partition_name,subpartition_name,tablespace_name
FROM DBA_TAB_SUBPARTITIONS 
WHERE TABLE_NAME='CATEGORIES' 
AND PARTITION_NAME='DIGITAL_MEDIA';
 
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME
CATEGORIES DIGITAL_MEDIA SYS_SUBP39427 DATA_2016
CATEGORIES DIGITAL_MEDIA SYS_SUBP39428 DATA_2016
CATEGORIES DIGITAL_MEDIA SYS_SUBP39429 DATA_2016
CATEGORIES DIGITAL_MEDIA SYS_SUBP39527 DATA_2024
CATEGORIES DIGITAL_MEDIA SYS_SUBP39528 DATA_2024
CATEGORIES DIGITAL_MEDIA SYS_SUBP39529 DATA_2024
The fact that we for this particular partition now must deal with system generated names is of no importance. The optimizer will still use partition pruning when appropriate.

Friday, August 2, 2019

How to list subpartitions and their sizes



First, find the number of partitions and subpartitions in a specific tablespace:
SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_OWNER='SCOTT'
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;

TABLE_NAME TABLESPACE_NAME num partitions num subpartitions
MYTABLE1 DATA1
75
450
MYTABLE2 DATA2
73
219
MYTABLE3 DATA1
74
222
MYTABLE4 DATA2
74
222
MYTABLE5 DATA1
81
243

For a specific table, all partitions and their subpartitions:
SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;

PARTITION_NAME SUBPARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_P177766 SYS_SUBP177763 TABLE SUBPARTITION DATA2 DISABLED  
26021384
25.06.2020 22:55:36
12
SYS_P177766 SYS_SUBP177764 TABLE SUBPARTITION DATA2 DISABLED  
21531914
26.06.2020 23:32:34
9,1
SYS_P177766 SYS_SUBP177765 TABLE SUBPARTITION DATA2 DISABLED  
0
02.03.2020 00:56:19
0
SYS_P177774 SYS_SUBP177771 TABLE SUBPARTITION DATA2 DISABLED  
285
16.07.2020 23:21:12
0
SYS_P177774 SYS_SUBP177772 TABLE SUBPARTITION DATA1 DISABLED  
259
16.07.2020 23:21:30
0

For specific subpartitions:
SELECT P.PARTITION_NAME "Subpartition name", S.PARTITION_NAME,S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.SUBPARTITION_NAME in ('SYS_SUBP177773','SYS_SUBP177763','SYS_SUBP177764','SYS_SUBP177765');

PARTITION_NAME Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_P177766 SYS_SUBP177763 TABLE SUBPARTITION DATA1 DISABLED  
26021384
25.06.2020 22:55:36
12
SYS_P177766 SYS_SUBP177764 TABLE SUBPARTITION DATA1 DISABLED  
21531914
26.06.2020 23:32:34
9,1
SYS_P177766 SYS_SUBP177765 TABLE SUBPARTITION DATA1 DISABLED  
0
02.03.2020 00:56:19
0
SYS_P177774 SYS_SUBP177773 TABLE SUBPARTITION DATA1 DISABLED  
0
02.03.2020 00:56:20
0

For a specific partition:
SELECT S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME='MYTABLE'
AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_SUBP14672 TABLE SUBPARTITION DATA1 DISABLED   127103150 29.06.2019 51,4
SYS_SUBP14673 TABLE SUBPARTITION DATA1 DISABLED   89059917 29.06.2019 34,1
SYS_SUBP14674 TABLE SUBPARTITION DATA1 DISABLED   0 29.06.2019 0

For a specific tablespace:
SELECT S.PARTITION_NAME "Subpartition name",S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.OWNER = 'SCOTT'
AND S.TABLESPACE_NAME='DATA1'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY S.BYTES DESC; 

Subpartition name SEGMENT_TYPE TABLESPACE_NAME COMPRESSION COMPRESS_FOR NUM_ROWS LAST_ANALYZED gb
SYS_SUBP27395 TABLE SUBPARTITION SCOTT DISABLED  
100835414
27.10.2018 19:06:53
44,6
SYS_SUBP29902 TABLE SUBPARTITION SCOTT DISABLED  
88951135
27.10.2018 21:23:24
41,6
SYS_SUBP29332 TABLE SUBPARTITION SCOTT DISABLED  
83142250
27.10.2018 23:40:00
38,3
SYS_SUBP29395 TABLE SUBPARTITION SCOTT DISABLED  
78610455
27.10.2018 19:00:05
37,1
SYS_SUBP28115 TABLE SUBPARTITION SCOTT DISABLED  
75810738
07.07.2018 15:54:52
35,7