Showing posts with label Partitioning. Show all posts
Showing posts with label Partitioning. Show all posts
Wednesday, April 2, 2025

How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table

›
Create the table: CREATE TABLE mytable ( id bigserial, key text COLLATE pg_catalog."default" NOT NULL, outdata jsonb...
Wednesday, February 23, 2022

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 r...
Friday, October 15, 2021

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. ...
Monday, September 20, 2021

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 t...

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 NUL...
1 comment:
Thursday, June 17, 2021

How to list partitions and their LOB segment sizes

›
SELECT ts.table_owner "owner", ts.table_name "table name", s.SEGMENT_NAME "segment name", ...
Thursday, May 27, 2021

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 fo...
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, a...
Friday, March 26, 2021

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 ...
Tuesday, March 9, 2021

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 onli...
Wednesday, July 29, 2020

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...
Wednesday, March 25, 2020

How to list index subpartitions and their sizes

›
SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ...
Tuesday, December 10, 2019

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...
Thursday, November 14, 2019

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 obje...
Tuesday, November 12, 2019

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 ...
Friday, November 8, 2019

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 ...
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...
Monday, May 6, 2019

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 state...

How to create a LIST partitioned table in PostgreSQL

›
Create the table: CREATE TABLE DOCUMENTS( DOC_ID INTEGER NOT NULL, LEGAL_ENTITY INTEGER NU...