Stack Overflow
Stack Overflow

Questions tagged [table-partitioning]

47 questions
0
votes
0 answers

Is using table partitioning for maintenance on tables that the service broker writes to a good or bad idea?

We have an activated stored procedure that writes errors to a table, which I recently learned is 300GB. To get rid of the 300GB I'm going to save out recent data, then truncate/reseed the table during a slow time. For future maintenance, though,…
mbourgon
  • 1,286
  • 2
  • 17
  • 35
0
votes
2 answers

Is it possible to create partitioned table with 'create table as' in PostgreSQL?

I am trying to create a partitioned table as follows: create table archive.table1 as table work1.table1 with no data partition by range (wk_date) and I am getting the following error: SQL Error [42601]: ERROR: syntax error at or near…
Thaz
  • 35
  • 9
0
votes
1 answer

Optimal Postgres partitioning based on date

I am looking at ways to improve deletion of data in my Postgres (partitioned) tables, not worsening the access performance. Using: Postgres 10.2 Ignoring some unrelated columns, I have the table transactions with these columns (omitting some…
Chillax
  • 4,418
  • 21
  • 56
  • 91
0
votes
1 answer

cannot combine declarative partitioning with inheritance in postgresql

We several tables which must inherit another table. The plan is to be able to perform queries between different tables using common attributes, but also perform queries on the parent table for analytical purposes. On top of this it might be…
Mihail Gershkovich
  • 412
  • 4
  • 9
0
votes
1 answer

ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION

I ran into the ORA-14097 while exchanging partition. Can anyone share me some light? I have the following source_tbl (non-partitioned) table and is intended to partition it using the column "VALID_PERIOD_END" CREATE TABLE source_tbl ( …
E. L.
  • 502
  • 3
  • 16
0
votes
1 answer

Is it a good practice to reuse partition functions and schemas on SQL Server Azure between different tables?

I have a partition function and a partition schema by date that I'm already using on a big table on my DDBB. As I have several big tables in my database that share this common pattern of having a date column, I wonder if it's OK to reuse this…
Oscar
  • 13,594
  • 8
  • 47
  • 75
0
votes
1 answer

How to move Indexes into another TableSpace

how to move indexes to another tablespace select owner, index_name, TABLE_NAME, a.TABLESPACE_NAME from all_indexes a where a.TABLE_NAME = 'REFUND'; ALTER INDEX CBS.PK_REFUND_ID REBUILD TABLESPACE T_IDX; ORA-14086: A partitioned index may not be…
Tomas
  • 47
  • 1
  • 5
0
votes
1 answer

Postgresql: Partitioning a table for values in SELECT

I want to use PostgreSQL's declarative table partitioning on a column with UUID values. I want it partitioned by values that are returned by a SELECT. CREATE TABLE foo ( id uuid NOT NULL, type_id uuid NOT NULL, -- other columns …
silverberry
  • 786
  • 5
  • 20
0
votes
0 answers

Increment by 1 in sequence numbering and dynamically partitioned tables

I am using dynamically created table partitions to store event information in a Postgresql 13 database. The master table from which the child tables inhert their structure contains an id field with an auto-incrementing sequence. The sequence, master…
Peter K.
  • 517
  • 3
  • 19
0
votes
0 answers

Oracle: Range-Range Interval Partition/Subpartition

I need to create a range-partitioned table: i.e. create table table1(item_id number(22), sys_entry_date timestamp default sysdate) partition by range(sys_entry_date) interval(NUMTOYMINTERVAL(1,'YEAR')) (partition p01 values less than…
Praveen Mishra
  • 101
  • 6
0
votes
0 answers

Odoo Table Partition

I'm working on Odoo and our database is grow and grow every days. I would like to do table partition for that and I have no experience on table partition process. First, I tried on new database. It was OK and then I worked on some functions and…
Sharifah
  • 361
  • 2
  • 17
  • 30
0
votes
1 answer

sql server partition table not performance in big tables?

I have table with 35 millions rows. When I select rows as shown as that was not working and It takes a long time and eventually gives a memory error (my ram is 32GB). I partitioned the table based on the monthly date and I have 64 table as shown…
Tomy
  • 225
  • 5
  • 18
0
votes
1 answer

SERIAL works with NULL, GENERATED ALWAYS AS IDENTITY not

Postgres 12: CREATE TABLE l_table ( id INT generated always as identity, w_id int NOT null references w_table(id), primary key (w_id, id) )PARTITION BY LIST (w_id); CREATE table l1 PARTITION OF l_table FOR VALUES IN (1); insert into l1…
Gabriel
  • 5,453
  • 14
  • 63
  • 92
0
votes
1 answer

Drop empty partition objects after truncate partition

I have a table partitioned by a date column, and each partition stores one month of data. Furthermore, each partition is associated with one filegroup, and each filegroup has exactly one db file (NDF). My setup is pretty simple: CREATE PARTITION…
Avi
  • 1,066
  • 1
  • 15
  • 37
0
votes
0 answers

Range vs List partitioning for YYYYMM year month field Oracle 12c

I have a table which holds monthly data differentiated by a column year month (YYYYMM, eg 201911). We have been planning to partition the table by month however we are unable to decide between Range Partitioning & List Partitioning for this type of…
pOrinG
  • 896
  • 3
  • 13
  • 27
1 2
3
4