When explaining a query that is accessing a partitioned table, what does the Pstart=KEY or Pstop=KEY indicate?

The Pstart=KEY or Pstop=KEY indicate that the exact partition cannot be determined at compile time, but will most likely be found during run time.

Some earlier step in the plan is producing one or more values for the partition key, so that pruning can take place.

Example: I have a composite partitioned table, with a locally partitioned index:

create table published_documents(
  UNIQUE_ID                   VARCHAR2(160 BYTE) NOT NULL,
  REGYEAR                     NUMBER(18),
  DOCUMENT_TYPE               VARCHAR2(100 CHAR),
  DOCUMENT_NAME               VARCHAR2(1000 CHAR),
  TOPIC                       VARCHAR2(30 CHAR),
  VALID                       CHAR(1 BYTE),
  VERSION                     NUMBER(18),
  DATA_XML                    CLOB,
  FORMAT                      VARCHAR2(1000 CHAR),
  PERIOD                      VARCHAR2(1000 CHAR)
)
PARTITION BY LIST (DOCUMENT_TYPE)
SUBPARTITION BY LIST (PERIOD)
...
);

create index pub_docs_idx1 on published_documents
(regyear, document_type, period)
  local;

Send the following query to the database:

select  document_type, count(*)
from myuser.published_documents
partition(LEGAL)
group by document_type;

The output is as expected:

DOKUMENTTYPE	COUNT(*)
Affidavit	7845
Amending Agreement	29909
Contract	6647

And result in the following execution plan:

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                     |     4 |   128 |   195M  (1)| 02:07:06 |       |       |
|   1 |  PARTITION LIST SINGLE|                     |     4 |   128 |   195M  (1)| 02:07:06 |   KEY |   KEY | 
|   2 |   HASH GROUP BY       |                     |     4 |   128 |   195M  (1)| 02:07:06 |       |       |
|   3 |    PARTITION LIST ALL |                      |  2198M|    65G|   195M  (1)| 02:07:03|     1 |   114 |
|   4 |     TABLE ACCESS FULL | PUBLISHED_DOCUMENTS |   2198M|    65G|   195M  (1)| 02:07:03|   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------

When we specifiy a named partition, we can see how the optimzer is limiting its search only to the partition mentioned in the predicate, but it does not yet know how many subpartitions to scan. Since there is no mention of a date range to match the PERIOD column in the predicate, all 114 subpartitions must be scanned.

Note that the text "TABLE ACCESS FULL" in step 4 can be somewhat confusing: we are only talking about a full table access of the partition called "LEGAL", not the the entire table.

In my experience, specifying the partition name directly is rather unusual, and mostely done by DBAs.
Let's try it with a predicate that is more likely to be sent to the oracle server by a user or a batch program:

select dokumenttype, period, count(*)
from myuser.published_documents
where periode = '2018-01'
group by dokumenttype, period;

The output is as expected:

DOKUMENTTYPE	PERIODE	COUNT(*)
Affidavit	2018-01	7845
Amending Agreement	2018-01	29909
Contract	2018-01	6647
Payroll	2018-01	7824
HA_related	2018-01	36608
Banking	2018-01	14167
IT	2018-01	4094

The rows in the output above belongs to many different partitions, but they are all from the period 2018-01.

The explain plan for this query would be:

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                           |    50 |  1950 |  6589K  (1)| 00:04:18 |       |       |
|   1 |  PARTITION LIST ALL     |                           |    50 |  1950 |  6589K  (1)| 00:04:18 |     1 |    11 |
|   2 |   HASH GROUP BY         |                           |    50 |  1950 |  6589K  (1)| 00:04:18 |       |       |
|   3 |    PARTITION LIST SINGLE|                           |  8122K|   302M|  6589K  (1)| 00:04:18 |       |       |
|*  4 |     INDEX SKIP SCAN     |        PUB_DOCS_IDX1      |  8122K|   302M|  6589K  (1)| 00:04:18 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("PERIOD"='2018-01')
       filter("PERIOD"='2018-01')

Here, too, we see that the optimizer first selects all 11 partitions, but then use the partitioned index PUB_DOCS_IDX1 to find the rows that would match the string '2018-01'. The optimizer does not yet know how many index subpartitions to scan; this will be determined during run-time.

Thanks to

Jim Brull

Justin Cave

How to list the number of rows per partition in PostgreSQL

I found an old post from 2013 on stackoverflow.com which solved a problem I had finding the number of rows per partition in PostgreSQL.

I couldn't find a simple way to list the estimated number of rows per partition and subpartition in PostgreSQL so I created a view as suggested by the member MatheusOl:

CREATE OR REPLACE VIEW row_counts AS
    SELECT relname, reltuples::numeric
    FROM pg_class pg, information_schema.tables i
    WHERE pg.relname = i.table_name
        AND relkind='r'
        AND table_schema NOT IN ('pg_catalog', 'information_schema');

Logon to your database using psql and create the view. Then, query it and the result would be something similar to querying dba_tab_partitions and dba_tab_subpartitions in Oracle:

            relname              |  reltuples
---------------------------------+-------------
 event_sp_2019_ukf               |       20799
 event_sp_2019_vasplpr           |           0
 event_sp_2019_ukp               |         120
 event_sp_2019_ltp               |           0
 event_sp_2019_smp               |          95

The view will not distinguished between tables, partitiones or subpartitions - they're all tables in terms of object types.

PostgreSQL: how to use the to_date, to_char and cast conversion functions

Building on the example in my previous post, this is how you can use the to_date and to_char functions for predicates used against both the original, non partitioned table, and the partitioned version of the same.

Query the non-partitioned table, you could first convert to character, and use the desired format. After that you can convert to date:

select *
from myuser.mytable
where to_date(to_char(created_date,'YYYY.MM.DD'),'YYYY.MM.DD') between '2020.01.01' and  '2020.01.31';

To query the partitioned table, you need an an explicit cast to the same data type used by the partitioned key column:

select *
from myuser.mytable
where  cast(created_date as date) between '2020.01.01' and  '2020.01.31';

I realized this difference when the number of rows returned was not what I expected. After I changed my SQL statement to use CAST, the number of rows was exactly what I expected.

PostgreSQL: how to create a partitioned table using a conversion function

Consider the following table:

CREATE TABLE myuser.mytable (
    id integer NOT NULL,
    created_date timestamp without time zone NOT NULL,
    event_json text NOT NULL,
    format_version text NOT NULL,
    rep_id text NOT NULL,
    appversion text NOT NULL
);

Let's say you want to partition this table by RANGE, and you would like to use the column "created_date" which is of data type "TIMESTAMP WITHOUT TIME ZONE".
You could use a conversion function like CAST to convert the column to a partitioned one, like this:

  CREATE TABLE myuser.mytable (
    id integer NOT NULL,
    created_date timestamp without time zone NOT NULL,
    event_json text NOT NULL,
    format_version text NOT NULL,
    rep_id text NOT NULL,
    appversion text NOT NULL
)
partition by range(cast(created_date as date));

Obviously you proceed with adding your partitions the way you normally would in PostgreSQL, for example:

CREATE TABLE myuser.mytable_p_202001
 PARTITION OF myuser.mytable
 FOR VALUES FROM ('2020.01.01') TO ('2020.01.31');

CREATE TABLE myuser.mytable_p_202002
 PARTITION OF myuser.mytable
 FOR VALUES FROM ('2020.02.01') TO ('2020.02.29');

etc

CREATE TABLE myuser.mytable_p_default
 PARTITION OF myuser.mytable
 DEFAULT;

Beware: If you try to create a primary key constraint on the partition key column in this case, you will receive

DETAIL:  PRIMARY KEY constraints cannot be used when partition keys include expressions.

You are of course free to add a normal search index on the column:

CREATE INDEX myidx1 ON myuser.mytable USING btree ( cast (created_date as date) );

Read more about the CAST function in the documentation
Another good source is postgressqltutorial.com

How to list partitions and their LOB segment sizes

SELECT  ts.table_owner "owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob part name",
        lp.column_name "lob name",
        lp.compression "lob compression",
        lp.partition_name "table part name",
        ts.num_rows "num rows",
        lp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_partitions lp
ON (s.partition_name = lp.lob_partition_name)  
   JOIN DBA_TAB_PARTITIONS ts
        ON (TS.PARTITION_NAME = lp.PARTITION_NAME) 
WHERE lp.table_name='MYTABLE'
AND   ts.table_name='MYTABLE'
-- To limit the output to a specific tablespace, uncomment line below
AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'MYTABLE' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lp.COMPRESSION,/*lp.subpartition_name*/lp.partition_name, lp.column_name,ts.num_rows,lp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY 12 DESC;

Output:

owner	table name	segment name	segment type	lob type	lob part name	lob name	lob compression	table part name	num rows	tablespace	size GB
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P3273	MYLOB1	NO	SYS_P3270	864051	DATA1	20
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P4684	MYLOB1	NO	SYS_P4681	593520	DATA1	17
JIM	MYTABLE	SYS_LOB0000079505C00019$$	LOB PARTITION	SECUREFILE	SYS_LOB_P5692	MYLOB1	NO	SYS_P5689	2021989	DATA1	16

For queries against a subpartitioned table, see this post.

Is default partition supported for partitioned tables in PostgreSQL?

Yes it is!

From version 11, it is possible to create a default partition for partitioned tables in PostgreSQL.

See the documentation for further details.

In version 12, several improvements to partitioning was introduced. See this blog post for details.

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.

What is the difference between dynamic and static partition pruning

From the documentation:

Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning.

* Static pruning occurs at compile-time, with the information about the partitions accessed being known beforehand.
* Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand.

A sample scenario for static pruning is a SQL statement containing a WHERE condition with a constant literal on the partition key column:

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = 1001;

There are some exceptions to this rule, listed here, for example if the partition pruning occurs using the results of a sub-query.

An exampel of Oracle using dynamic pruning is when operators or functions are used in the predicat (WHERE-condition). Also, statements that use bind variables against partition columns result in dynamic pruning:

var b1 number;
exec :B1 := 1001;

SELECT s.salesID AS ID,
       s.SalesDate AS SoldDate,
       s.pointofsalesID AS PoSID
FROM sales s
WHERE s.pointofsalesID = :B1;

How to add a different looking index to your interim table during online redefinition

The procedure REGISTER_DEPENDENT_OBJECT in the DBMS_REDEFINITION package lets you add a dependent object to your interim table during online redefinition, and then "plug it into" your redefintion process.

In my case, I was redefining an incorrectly partitioned table. The interim table was also partitioned, but this time correctly. During the phase where I call the COPY_TABLE_DEPENDENTS procedure to transfer the existing dependant objects (triggers, indexes, constraints etc) from the original table to the interim table, it failed with the message

ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2761

This was because I was using the directive dbms_redefinition.cons_orig_params in my code:

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'SALES',
orig_table=>'DAILYSALES',
int_table=>'DAILYSALES_INTERIM',
copy_indexes=>dbms_redefinition.cons_orig_params,
copy_triggers=>TRUE,
copy_constraints=>TRUE,
copy_privileges=>TRUE,
ignore_errors=>FALSE,
num_errors => l_num_errors,
copy_statistics=>TRUE,
copy_mvlog=>TRUE);
END;
/

As such, the redefinition process attempted to transfer the partitioned index from the original table which had a different partitioning scheme, to the new table who was partitioned differently and had fewer partitions.

As a workaround, I recreated the index directly on the interim table:

CREATE INDEX SALES.SEARCHIX1_INTERIM ON SALES.DAILYSALES_INTERIM
(COL1, COL2 DESC)
TABLESPACE DATA2
LOCAL
PARALLEL ( DEGREE 5 INSTANCES 1 );

Then, "plug it into" the redefinition procedure:

exec DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('SALES','DAILYSALES','DAILYSALES_INTERIM',2,'SALES','SEARCHIX1','SEARCHIX1_INTERIM');

Finally, change the directive in your COPY_TABLE_DEPENDENTS procedure from

copy_indexes=>dbms_redefinition.cons_orig_params

to

copy_indexes=>0

And run the COPY_TABLE_DEPENDENTS procedure again.
When done, finish the redefinition by calling the FINISH_REDEF_TABLE procedure, and you'll see that the interim index on the interim tables has been nicely transfered during the switch.

The Oracle 18c documentation can be found here
Another good source is HOW TO USE DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT (Doc ID 1304838.1 from Oracle Support)

How to list index partitions and their sizes

To list index partitions for index SALES_IDX1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXPART.INDEX_NAME='SALES_IDX1'
AND    S.SEGMENT_TYPE='INDEX PARTITION'
-- To only list partitions in tablespace DATA1, uncomment the following line:
-- AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	SYS_P177667	DATA1	8
SH	SALES_IDX1	SYS_P177518	DATA1	5
SH	SALES_IDX1	SYS_P44844	DATA1	3
SH	SALES_IDX1	SYS_P44663	DATA1	2
SH	SALES_IDX1	SYS_P177677	DATA1	2

To list all index partitions for tablespace DATA1:

SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_PARTITIONS IDXPART JOIN DBA_SEGMENTS S
ON (IDXPART.PARTITION_NAME = S.PARTITION_NAME )
AND    S.SEGMENT_TYPE='INDEX PARTITION'
AND    IDXPART.TABLESPACE_NAME='DATA1'
GROUP BY IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.PARTITION_NAME,IDXPART.TABLESPACE_NAME
ORDER BY 5 DESC;

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	GB
SH	SALES_IDX1	SYS_P177667	8
SH	SALES_IDX1	SYS_P177518	5
SH	SALES_IDX1	SYS_P44844	3
SH	SALES_IDX1	SYS_P177677	2
SH	SALES_IDX1	SYS_P44663	2
SH	SALES_IDX2	SYS_P179608	1
SH	SALES_IDX2	SYS_P178334	1
SH	SALEX_IDX2	SYS_P178459	1
SH	SALES_IDX3	SYS_P28534	0
SH	SALES_IDX3	SYS_P50905	0

To list the names of all partitioned indexes and their total sizes, in a particular tablespace:

SELECT DISTINCT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.TABLESPACE_NAME, 
    (SELECT ROUND(SUM(S.BYTES)/1024/1024/1024) 
     FROM DBA_SEGMENTS S
     WHERE S.SEGMENT_NAME = IDXPART.INDEX_NAME
     ) GB
FROM   DBA_IND_PARTITIONS IDXPART 
WHERE  IDXPART.TABLESPACE_NAME='DATA1'
ORDER BY 4 DESC;

INDEX_OWNER	INDEX_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX1	DATA1	567
SH	SALES_IDX2	DATA1	511
SH	SALES_IDX3	DATA1	331

To check which tablespaces the different partitions in an index reside in:

SELECT TABLESPACE_NAME,COUNT(*)
FROM DBA_IND_PARTITIONS
WHERE INDEX_NAME='SALES_IDX4'
GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME	COUNT(*)
DATA1	13
DATA2	832

How to list index subpartitions and their sizes

SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024/1024) "GB"
FROM   DBA_IND_SUBPARTITIONS IDXSUBPART JOIN DBA_SEGMENTS S
ON (IDXSUBPART.SUBPARTITION_NAME = S.PARTITION_NAME )
WHERE  IDXSUBPART.INDEX_NAME='SALES'
AND    IDXSUBPART.TABLESPACE_NAME='SH'
AND    S.SEGMENT_TYPE='INDEX SUBPARTITION'
GROUP BY IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME;

Output:

INDEX_OWNER	INDEX_NAME	PARTITION_NAME	SUBPARTITION_NAME	TABLESPACE_NAME	GB
SH	SALES_IDX_SK1	SYS_P18153	SYS_SUBP14709	DATA1	1
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14831	DATA1	1
SH	SALES_IDX_SK1	SYS_P18158	SYS_SUBP14862	DATA2	25
SH	SALES_IDX_SK1	SYS_P18157	SYS_SUBP14832	DATA1	0
SH	SALES_IDX_SK1	SYS_P18142	SYS_SUBP12409	DATA2	32
SH	SALES_IDX_SK1	SYS_P18139	SYS_SUBP12414	DATA1	1

What is the Asynchronous Global Index Maintenance introduced in Oracle 12.1?

Asynchronous Global Index Maintenance, also called Deferred Global Index Maintenance, means that Oracle will maintain global indexes during the nightly maintenance window, when you perform operations on a partitioned tables that use a global index. Oracle has introduced this in an effort to make online operations less disruptive.

Examples of such operations are

* DROP PARTITION
* TRUNCATE PARTITION
* MOVE PARTITION (although this operation is not listed in the Oracle 12.1 documentation)

The execution of the index maintenance is done through the PL/SQL program PMO_DEFERRED_GIDX_MAINT, which purpose is to clear orphan data from global indexes generated during partition maintenance operations.

The program can be verified through DBA_SCHEDULER_PROGRAMS:

SELECT OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED
FROM DBA_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	PROGRAM_NAME	PROGRAM_TYPE	PROGRAM_ACTION	ENABLED
SYS	PMO_DEFERRED_GIDX_MAINT	PLSQL_BLOCK	dbms_part.cleanup_gidx_internal( noop_okay_in => 1);	TRUE

It is executed trough the scheduler every night:

select schedule_name,repeat_interval
from DBA_SCHEDULER_SCHEDULES
where schedule_name='PMO_DEFERRED_GIDX_MAINT_SCHED';

SCHEDULE_NAME	REPEAT_INTERVAL
PMO_DEFERRED_GIDX_MAINT_SCHED	FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0

It is executed through the job PMO_DEFERRED_GIDX_MAINT_JOB:

SELECT owner,job_name,program_name,schedule_name,enabled,state,run_count,to_char(last_start_date,'dd.mm.yyyy hh24:mi') "start",stop_on_window_close
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	JOB_NAME	PROGRAM_NAME	SCHEDULE_NAME	ENABLED	STATE	RUN_COUNT	start	STOP_ON_WINDOW_CLOSE
SYS	PMO_DEFERRED_GIDX_MAINT_JOB	PMO_DEFERRED_GIDX_MAINT	PMO_DEFERRED_GIDX_MAINT_SCHED	TRUE	RUNNING	1328	10.12.2019 02:00	FALSE

If the job is not finished within a day, is it possible to run PMO_DEFERRED_GIDX_MAINT with parallelism, as specified in the Oracle Support Note "Is It Possible To Execute The Job PMO_DEFERRED_GIDX_MAINT_JOB With Parallel Degree? (Doc ID 2126370.1)".


An example from my own experience is that the following command

ALTER TABLE SALES
MOVE PARTITION P_2019_02 ONLINE
TABLESPACE DATA2
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Triggered the statement:

ALTER INDEX "SH"."SALES_IDX1" COALESCE CLEANUP;

I have found that I sometime need to stop an job that's running through the maintenance window, and into office hours.
In that case, I've used the procedure dbms_scheduler.stop_job, like this:

BEGIN
  DBMS_SCHEDULER.STOP_JOB('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'TRUE');
END;
/

If you need to find out which indexes that are due for coalescing, use this query:

SELECT owner,index_name,orphaned_entries
FROM   dba_indexes
where owner ='SCOTT'
and orphaned_entries='YES'
ORDER BY 1;

How to move XML LOB segments in a partitioned table to a new tablespace

The examples used in this article is based on Oracle 18c.

When moving a partition with XMLTYPE columns to a new tablespace, the LOB objects that was created by the XMLTYPE objects will follow along with the partition when you move it. Only the LOB partitions based on data types CLOB (and I assume also BLOB) will have to be moved explicitly.

The table looks as follows. Note the column types of CLOB and XMLTYPE:

CREATE TABLE CS_DOCUMENTS
(
  ENTRY_ID                 VARCHAR2(100 CHAR),
  BATCH_ID                 NUMBER(28),
  DOC_ID                   VARCHAR2(100 CHAR),
  DOC_TYPE                 VARCHAR2(100 CHAR),
  DOC_NAME                 VARCHAR2(4000 CHAR),
  STATUS                   VARCHAR2(4000 CHAR),
  PUBLISHED                TIMESTAMP(6),
  CREATED                  TIMESTAMP(6),
  RAW_DATA                 CLOB,
  DOKCOUNT                 INTEGER,
  REVISION                 INTEGER,
  XML_P_HEADER             SYS.XMLTYPE,
  XML_P_HEADER_FORMAT      VARCHAR2(4000 CHAR),
  XML_P_DATA               SYS.XMLTYPE,
  XML_P_DATA_FORMAT        VARCHAR2(4000 CHAR),
  XML_P_EXTENSION          SYS.XMLTYPE,
  XML_P_EXTENSION_FORMAT   VARCHAR2(4000 CHAR)
)
-- CLOB
LOB (RAW_DATA) STORE AS SECUREFILE (
  TABLESPACE  DATA1
)
-- XMLTYPE
XMLTYPE XML_P_HEADER STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_DATA STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_EXTENSION STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
)
TABLESPACE MOTTAK_DATA
PARTITION BY RANGE (BATCH_ID)
INTERVAL(1000)
(  
  PARTITION P_INIT VALUES LESS THAN (1001)
    NOCOMPRESS 
    TABLESPACE DATA1
    LOB (RAW_DATA) STORE AS SECUREFILE (
      TABLESPACE  DATA1
    )
 )
;

The tablespace DATA1 is filling up, and there is a need to move some partitions to another tablespace, DATA2.
In this example, I am moving the latest added partitions first, and working my way backwards.

Let's look at most recently added partition:

SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,ROUND(SUM(S.BYTES)/1024/1024/1024,2) "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MAX(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME != 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:

TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME	GB
CS_DOCUMENTS	RAW_DATA	SYS_LOB0044210973C00012$$	SYS_P6533446	SYS_LOB_P6533447	62	DATA1	31,24
CS_DOCUMENTS	SYS_NC00017$	SYS_LOB0044210973C00017$$	SYS_P6533446	SYS_LOB_P6533449	62	DATA1	0,01
CS_DOCUMENTS	SYS_NC00020$	SYS_LOB0044210973C00020$$	SYS_P6533446	SYS_LOB_P6533451	62	DATA1	0,01
CS_DOCUMENTS	SYS_NC00023$	SYS_LOB0044210973C00023$$	SYS_P6533446	SYS_LOB_P6533453	62	DATA1	0,01

The LOB partition SYS_LOB_P6533447 is occupying 31GB of space.

Move the partition to another tablespace. Notice how I specify tablespace for the table partition and the LOB object, but none of the XMLType objects:

Take the opportunity to compress the LOB objects at the same time. Use the ONLINE clause to allow DML against the table during the move-operation:

set timing on
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>'move_objects');
exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'move_objects', action_name=>NULL);
exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'move_lobs');
ALTER TABLE CS_DOCUMENTS
 MOVE PARTITION SYS_P6533446
    ROW STORE COMPRESS ADVANCED
    TABLESPACE DATA2
    LOB (ENTRY_RAW) STORE AS SECUREFILE (
      TABLESPACE  DATA2
      COMPRESS    MEDIUM
      )
 ONLINE;

The situation after the move:

SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,SUM(S.BYTES)/1024/1024/1024 "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MIN(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME = 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:

TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME	GB
CS_DOCUMENTS	RAW_DATA	SYS_LOB0044210973C00012$$	SYS_P6533446	SYS_LOB_P6533447	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00017$	SYS_LOB0044210973C00017$$	SYS_P6533446	SYS_LOB_P6533449	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00020$	SYS_LOB0044210973C00020$$	SYS_P6533446	SYS_LOB_P6533451	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00023$	SYS_LOB0044210973C00023$$	SYS_P6533446	SYS_LOB_P6533453	62	DATA2	0,0078125

ALTER TABLE .... MOVE PARTITION statments can be generated on a partition-by-partition basis with:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' ROW STORE COMPRESS ADVANCED TABLESPACE DATA2 lob (' || COLUMN_NAME || ') store as SECUREFILE (tablespace DATA2 COMPRESS MEDIUM) online update indexes;' 
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'CS'
and table_name='CS_DOCUMENT'
AND PARTITION_POSITION = (SELECT MAX(PARTITION_POSITION) FROM DBA_LOB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENT'  AND TABLESPACE_NAME != 'DATA2')
AND COLUMN_NAME = (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='CS_DOCUMENTS' AND DATA_TYPE LIKE ('%LOB%'));

How to convert a non-partitioned table into a partitioned table in Oracle version 12.2 and onwards

With Oracle 12.2 and higher versions, it is really simple to convert a non-partitioned table to a partitioned table: you can now use the "ALTER TABLE .... MODIFY" syntax.

Below I am showing how I used this feature for one of my tables.

First, find some basic info about the table as it is right now:

SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:

TABLE_NAME	PARTITIONED	NUM_ROWS	COMPRESSION
SEGMENT_SIZE_SAMPLES	NO	948401	DISABLED

Alter the table. I am taking the opportunity to compress the table at the same time as I am getting it partitioned. I am also using the ONLINE keyword, allowing DML against the table during the operation:

ALTER TABLE SEGMENT_SIZE_SAMPLES MODIFY 
PARTITION BY RANGE (SAMPLE_DATE)
INTERVAL
(
   NUMTOYMINTERVAL(1,'MONTH')
)
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('2014-01','YYYY-MM') )
)
ROW STORE COMPRESS ADVANCED
ONLINE;

Analyze the table:

exec DBMS_STATS.GATHER_TABLE_STATS (OwnName => 'DBDRIFT',TabName => 'SEGMENT_SIZE_SAMPLES');

Check the table properties again:

SELECT TABLE_NAME, PARTITIONED,NUM_ROWS,COMPRESSION
FROM  DBA_TABLES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

Output:

TABLE_NAME	PARTITIONED	NUM_ROWS	COMPRESSION
SEGMENT_SIZE_SAMPLES	YES	1000719

How about compression?

Check the DBA_TAB_PARTITIONS (I am only showing the first 5 rows for brevity):

SELECT TABLE_NAME, PARTITION_NAME,NUM_ROWS,COMPRESSION, COMPRESS_FOR
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES'
FETCH FIRST 5 ROWS ONLY;

Output:

TABLE_NAME	PARTITION_NAME	NUM_ROWS	COMPRESSION	COMPRESS_FOR
SEGMENT_SIZE_SAMPLES	P_INIT	0	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17074	19320	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17075	24955	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17076	24150	ENABLED	ADVANCED
SEGMENT_SIZE_SAMPLES	SYS_P17077	24934	ENABLED	ADVANCED

How about existing indexes?

By default, existing non-unique indexes are also partitioned during the alteration of the table.

There are two indexes on my table, one unique index supporting the primary key constraint, and one non-unique index.

Before the table was altered, both were unpartitioned:

SELECT INDEX_NAME,UNIQUENESS, PARTITIONED 
FROM DBA_INDEXES
WHERE TABLE_NAME='SEGMENT_SIZE_SAMPLES';

INDEX_NAME	UNIQUENESS	PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1	UNIQUE	NO
SEGMENT_SIZE_SAMPLES_IDX2	NONUNIQUE	NO

After the table was partitioned, the non-unique index was automatically partitioned, too:

INDEX_NAME	UNIQUENESS	PARTITIONED
SEGMENT_SIZE_SAMPLES_IDX1	UNIQUE	NO
SEGMENT_SIZE_SAMPLES_IDX2	NONUNIQUE	YES

Update 18.03.2020:

Another example using LIST partitioning:

alter table MUSIC.ALBUM modify
partition by list (GENRE)
(
    partition P_ROCK values (( 'ROCK')),
    partition P_POP values (( 'POP')),
    partition P_CLASSICAL values (( 'CLASSICAL')),
    partition P_MISC values (default)
)
online;

Update 12.03.2021:
An example using subpartitions:

-- First, give the LOB the desired attributes, if you wish to change any of them:
alter TABLE STOCKS MOVE
LOB (DOC)
STORE AS SECUREFILE(
  TABLESPACE  DATA1
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  COMPRESS    MEDIUM
);

-- Alter the table
alter TABLE STOCKS MODIFY
PARTITION BY LIST (stockname)
SUBPARTITION BY RANGE (LASTUPDATED)
(   -- First partition is called a_name
    PARTITION a_name VALUES('a-abc')
    (
    -- older values
    SUBPARTITION SP_a_name_1_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1 ),
    -- 2016
    subpartition SP_a_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

...contine with  partitions b_name, c_name etc

-- Final partition is called z_name
 PARTITION z_name VALUES(default)
  (
    -- 2016
    SUBPARTITION SP_sp_z_name_older VALUES LESS THAN ( TO_DATE('2016-01', 'YYYY-MM')) TABLESPACE DATA1,
    subpartition SP_sp_z_name_201601 VALUES LESS THAN (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2016
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2016 ),

...continue with one subpartition per month per year...

 LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    subpartition SP_z_name_202212 VALUES LESS THAN (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')) TABLESPACE DATA_2022
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA_2022 ),
    SUBPARTITION SP_z_name_DEFAULT VALUES LESS THAN (MAXVALUE) TABLESPACE DATA1
    LOB (DOC) STORE AS SECUREFILE  ( TABLESPACE DATA1)
    )
)
ONLINE
UPDATE INDEXES(
  IDX_1 LOCAL,
  IDX_2 LOCAL
);
  

Note that UPDATE INDEXES converts IDX_1 and IDX_2 and convert them to LOCAL indexes, on the fly.
Source: Oracle Documentation
I also recommend to look at Oracle-base.com for further reading.

How to list subpartitions and their LOB segment sizes

The query below will list the lob segment names, the lob subpartition names, the table subpartition names and sizing information for any subpartitioned tables using LOB columns:

SELECT  ts.table_owner "table owner",
        ts.table_name "table name",
        s.SEGMENT_NAME "segment name",
        s.SEGMENT_TYPE "segment type",
        s.SEGMENT_SUBTYPE "lob type",
        s.PARTITION_NAME "lob subpart name",
        lsp.column_name "lob name",
        lsp.compression "lob compression",
        lsp.subpartition_name "table subpartition name",
        ts.num_rows "num rows",
        lsp.tablespace_name "tablespace",
        ROUND(sum(s.bytes)/1024/1024/1024) "size GB"
FROM dba_segments s JOIN dba_lob_subpartitions lsp
ON (s.partition_name = lsp.lob_subpartition_name)  
   JOIN DBA_TAB_SUBPARTITIONS ts
        ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME) 
WHERE lsp.table_name='DOCUMENTS'
AND   ts.table_name='DOCUMENTS'
-- To limit the output to a specific tablespace, uncomment line below
-- AND   s.tablespace_name='DATA1'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lsp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name 
                        FROM dba_lob_partitions lpt 
                        WHERE lpt.table_name IN ( 'DOCUMENTS' ) )

GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.tablespace_name
/*
To limit output to lob subpartitions with a specific size, 
uncomment the restriction above and change the operator to suit your needs ( <, >, =) 
or use BETWEEN x AND y
*/
-- HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY lsp.subpartition_name DESC;

Example output:

TABLE_NAME	segment name	segment type	lob type	lob subpart name	lob compression	table subpartition name	num rows	tablespace	size GB
MYTABLE	MYTABLE_LOB7	LOB SUBPARTITION	SECUREFILE	KLM_DOCS_2018_01	MEDIUM	KLM_SP_2018_01	164497	DOCS2018	235
MYTABLE	MYTABLE_LOB7	LOB SUBPARTITION	SECUREFILE	VISA_DOCS_2018_01	MEDIUM	VISA_SP_2018_01	72405	DOCS2018	76
MYTABLE	MYTABLE_LOB7	LOB SUBPARTITION	SECUREFILE	KLM_DOCS_2017_10	MEDIUM	KLM_SP_2017_10	16256	DOCS2017	19
MYTABLE	MYTABLE_SMALL_LOB7	LOB SUBPARTITION	SECUREFILE	KLM_DOCS_SMALL_2018_01	MEDIUM	KLM_SP_2018_01	164497	DOCS2018	18
MYTABLE	MYTABLE_SMALL_LOB7	LOB SUBPARTITION	SECUREFILE	VISA_DOCS_SMALL_2018_01	MEDIUM	VISA_SP_2018_01	72405	DOCS2018	8

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

How to explain a query in PostgreSQL

If you are using partitioned tables, make sure you have enabled partition pruning:

SET enable_partition_pruning to on;

The explain statement is simple enough:

explain select * from documents where dokumenttype='SUBPOENA';

                            QUERY PLAN
---------------------------------------------------------------------
 Append  (cost=0.00..1.02 rows=1 width=774)
   ->  Seq Scan on P_SUBPOENA  (cost=0.00..1.01 rows=1 width=774)
         Filter: ((documenttype)::text = 'SUBPOENA'::text)
(3 rows)

Since this is the LIST-partitioned table outlined in this post, I know the optimizer picked the correct partition for my predicate.

For the LIST-range subpartitioned table outlined in this post, I get the following query plan:

explain select * from orders where country_code='se' and order_total between 4000 and 4999;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..28.21 rows=1 width=50)
   ->  Seq Scan on large_orders_se  (cost=0.00..28.20 rows=1 width=50)
         Filter: ((order_total >= '4000'::numeric) AND (order_total <= '4999'::numeric) AND ((country_code)::text = 'se'::text))
(3 rows)

Regarding parallelism, the documentation tells you to watch for the terms Gather or Gather Merge Node in the query plan:
An example:

"Subquery Scan on andre  (cost=1000.00..10024950.78 rows=294885 width=84)"
"  ->  Gather  (cost=1000.00..10013892.60 rows=294885 width=64)"
"        Workers Planned: 2"
"        ->  ProjectSet  (cost=0.00..9983404.10 rows=29488500 width=64)"
"              ->  ProjectSet  (cost=0.00..9830063.90 rows=12286900 width=128)"

How to create a LIST partitioned table in PostgreSQL

Create the table:

CREATE TABLE DOCUMENTS(
  DOC_ID                      INTEGER  NOT NULL,
  LEGAL_ENTITY                INTEGER       NULL,
  CREATED_DT                  DATE      NOT NULL,
  REGION                      VARCHAR(30)   NULL,
  DOCUMENTTYPE                VARCHAR(100)  NULL,
  DOCUMENTNAME                VARCHAR(1000) NULL
)
PARTITION BY LIST (DOCUMENTTYPE);

Create a couple of partitions, including a default partition:

CREATE TABLE P_SUBPOENAS PARTITION OF  DOCUMENTS FOR VALUES IN  ('SUBPOENA');
CREATE TABLE P_AFFIDAVITS PARTITION OF DOCUMENTS FOR VALUES IN  ('AFFIDAVIT');
CREATE TABLE P_MEMORANDOMS PARTITION OF DOCUMENTS FOR VALUES IN ('MEMORANDOM');
CREATE TABLE P_DEFAULT PARTITION OF DOCUMENTS DEFAULT;

To add a primary key to a partitioned table, read this post

If your LIST-partitioned table would benefit from sub-partitioning, read this post

What exactly is meant by "global statistics" on a partitioned table?

From the whitepaper "Understanding Optimizer Statistics":

"When dealing with partitioned tables the Optimizer relies on both the statistics for the entire table
(global statistics) as well as the statistics for the individual partitions (partition statistics) to select a
good execution plan for a SQL statement. If the query needs to access only a single partition, the
Optimizer uses only the statistics of the accessed partition. If the query access more than one partition,
it uses a combination of global and partition statistics."

How to describe and analyze a partitioned table in postgreSQL

In this example, I am describing a table called "albums" in a database called "music". The table is partitioned:

psql -h myserver.mydomain.com -U music musicdb
Password for user music:
psql (11.2)
Type "help" for help.

Describe the table:
musicdb=> \d album

Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default
--------------+-----------------------+-----------+----------+------------------------------
 album_id     | integer               |           | not null | generated always as identity
 album_name   | character varying(40) |           |          |
 genre        | character varying(20) |           |          |
 label        | character varying(40) |           |          |
 release_year | date                  |           |          |
Partition key: RANGE (date_part('year'::text, release_year))
Number of partitions: 3 (Use \d+ to list them.)

Describe the table's partitions:
musicdb=> \d+ album

Table "music.album"
    Column    |         Type          | Collation | Nullable |           Default            | Storage  | Stats target | Description
--------------+-----------------------+-----------+----------+------------------------------+----------+--------------+-------------
 album_id     | integer               |           | not null | generated always as identity | plain    |              |
 album_name   | character varying(40) |           |          |                              | extended |              |
 genre        | character varying(20) |           |          |                              | extended |              |
 label        | character varying(40) |           |          |                              | extended |              |
 release_year | date                  |           |          |                              | plain    |              |
Partition key: RANGE (date_part('year'::text, release_year))
Partitions: albums_1974 FOR VALUES FROM ('1974') TO ('1975'),
            albums_1979 FOR VALUES FROM ('1979') TO ('1980'),
            albums_default DEFAULT

Analyze the table:
musicdb=> analyze verbose album;

INFO:  analyzing "music.album" inheritance tree
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_1974"
INFO:  "albums_1974": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "music.albums_1979"
INFO:  "albums_1979": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "music.albums_default"
INFO:  "albums_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

To check the statistics:

select attname,inherited,null_frac,n_distinct,most_common_vals,most_common_freqs,histogram_bounds 
from pg_stats 
where tablename='album';

attname	inherited	null_frac	n_distinct	most_common_vals	most_common_freqs	histogram_bounds
album_id	true	0.0	-1.0			{1,2,3}
album_name	true	0.0	-0.6666667	{KISS}	{0.666666687}
genre	true	0.0	-0.33333334	{"Hard Rock"}	{1}
label	true	0.6666667	-0.3333333
release_year	true	0.0	-0.6666667	{1974-01-18}	{0.666666687}