Questions tagged [partitioning]

Splitting a database table into multiple segments for performance or manageability.

Filter by
Sorted by
Tagged with
0
votes
1answer
266 views

Postgresql - how to list indexes on a table that is partitioned?

This command works for non-partitioned tables: select * from pg_indexes But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition. ...
0
votes
0answers
13 views

Trigger on update of any columns to copy the update to another able

I am partitioning an existing table in production called app_users, to do this, I have created a partition table app_users_partition with it's partitions along with a trigger in app_users which copies ...
1
vote
0answers
30 views

SQL Server Partition. How would splitting range with Guid work?

I have a very large table in my database, and it has a GUID column called Group_Id. This Group_Id has a pretty even split of data, and it would be beneficial to partition the table by this column. ...
0
votes
0answers
37 views

Oracle - partition table by 3 columns

I have, in Oracle database, fact table based upon which reports are generated. Table contains sales data - large volume. Users generating reports are usually using grouping by (sale_day, customer, ...)...
1
vote
2answers
321 views

Mysql Range partition

I have the next table structure: CREATE TABLE `sat_msgPos` ( `idOwnApp` mediumint(11) NOT NULL DEFAULT '0', `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT, `idVeh` mediumint(11) NOT NULL ...
1
vote
2answers
47 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
-1
votes
1answer
109 views

Postgres 12 Scalability using table Partitioning and Foreign Data Wrappers

I have looked through the archives and I cant find any discussions around the following topic. I have a fairly in depth question that I would appreciate some guidance with. Current environment ...
0
votes
0answers
37 views

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 ...
0
votes
1answer
19 views

postgres partition data record assignment

It is possible in PostgreSQL 11 to display the partition table for each row in the table? Well, it's clear that the assignment of partitions is always known by the declarative definition but is there ...
6
votes
2answers
1k views

Round robin vs Demand Parallelism partitioning

This is related to having a common key driver on top of the query with a nested loops, and the parallelism for the rows coming out of driver being either demand type or round robin type. I would have ...
1
vote
1answer
74 views

Triggers on partitioned tables on Postgres 11.5

I asked a question about History table design for deletions in PG 11.5, and received a suggestion to partition the table. This is an excellent idea as the table may become huge, and the information ...
3
votes
1answer
107 views

SQL Server 2012 - How to Remove Partition on Publisher but not Subscriber?

Is it possible to setup partitioning on a SQL Server 2012 publisher table, so that older data can be removed without affecting the same table on a subscriber table? Table exists on Database A (SQL ...
21
votes
2answers
6k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
0
votes
1answer
510 views

Limits of table partitioning (in PostgreSQL)

I have the following use case (PostgreSQL, currently v9 but I'm planning to upgrade to the latest version). I have a data warehouse application with a central table that has a sizeable number of ...
6
votes
2answers
199 views

Change collation on partition column (SQL Server 2017)

I'm trying to change collation on column: ALTER TABLE [dbo].[yourdad] ALTER COLUMN [yourmam] varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL; and it gives message like this: Msg 5074, ...
-1
votes
1answer
126 views

Mysql Partitioning Query

i have created partitions on pricing table. below is the alter statement. ALTER TABLE `price_tbl` PARTITION BY HASH(man_code) PARTITIONS 87; one partition consists of 435510 records. total records ...
1
vote
3answers
377 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
0
votes
1answer
26 views

MySQL - InnoDB - Partitioning a table with uniqueness

Following a previous post I posted: date-based-partitioning This is my solution for partitioning a table with primary key. I wanted to add partitioning by months and delete old record. CREATE TABLE ...
2
votes
1answer
70 views

Increasing join performance on very large table

I have the following query in Postgres database SELECT (rel."external-institute" || '#' || rel."external-institute-code")::varchar(50) FROM public."td_documents" d JOIN ...
0
votes
0answers
49 views

Postgres - Partition pruning and query performance

I created a fact table with range partition on a date column and when a SQL statement is executed like this: Select * from my_table Where dt_col between to_date('01/01/2017','mm/dd/yyyy') ...
-1
votes
1answer
158 views

Large table partition

I have non-partitioned production table with 100 million records for last 2 years. The table has the clustered and non clustered indexes. I want to insert daily about 150K records from external ...
4
votes
2answers
7k views

how to generate the creation script for partition function and partition schema?

I have a script to generate the create table script. You can see it on my answer to this question. However, now I need to script my partition functions and partition schemes On this question here, ...
0
votes
1answer
29 views

Why does this partitioning helper view give an 'Input string was not in a correct format' error?

A database I'm investigating has defined in it a view admin.View_PartitionRanges, which is heavily based on the view PartitionRanges to be found in this MSDN blogpost from 2009. The view definition ...
5
votes
3answers
35k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
-1
votes
1answer
724 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following query:...
2
votes
2answers
768 views

SQL Server 2014 Transactional Replication Partition Switching

I am working with SQL Server 2014 Transactional Replication with tables that have partitions. The source tables are partition switched when they are loaded and so far I have been able to replicate ...
2
votes
2answers
144 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
0
votes
0answers
34 views

Rebuild clustered index online on specific partition

Can I rebuild clustered index ONLINE (MS SQL 2017 Developer Edition) for specific partition which is on partitioned table with different filegroups? Selected partition is on read_write filegroup, but ...
-1
votes
1answer
354 views

Fragmentation in InnoDB tables: dump all data together reduced size, but fragmentation got worse [closed]

I have a question about tables and fragmentation in MySQL My starting point is this question: How find and fix fragmented MySQL tables I have a table with 1 million of rows and 128MB of space. This ...
1
vote
0answers
38 views

MSSQL - Lock escalation on partitioned indexed view

I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level. On the top of that table I have indexed view which is ...
0
votes
0answers
39 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...
1
vote
0answers
41 views

Changing clustered key on a big table

I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way CREATE TABLE BigFactTable ( BigFactTableId bigint IDENTITY ...
1
vote
1answer
221 views

Postgres 11 declarative partitioning - using large number of partitions

We are running a system using Postgres 11 and we implemented partitioning with ID which is most convenient to us but which produces many partitions (currently over 1000 with expected total of 10000 ...
0
votes
0answers
46 views

How to rename parent table and child tables in partition tables postgres 9.6

I am trying to rename parent table in partitions. I created 45 child tables on monthly wise manner. I can alter the name of parent table but I don't know how to alter the 45 child table references. ...
-4
votes
1answer
145 views

Monthly to Daily Partitions in Oracle

I have a table with monthly partitions on it. I was asked to change those monthly partitions to daily partitions. For that, can I do even if the data exists ? What prerequisites do I need to take care....
-1
votes
1answer
364 views

Reduced copy of zabbix database

I've a relatively big postgresql database that tooks a lot of space in a server. The database stores a zabbix system used to monitor data that I've to safely keep, so history tables are quite big. My ...
-1
votes
1answer
137 views

Query performance question

Using Demo from here to reproduce my issue with changes in table structure as below and modifying demo partition function to datetime CREATE TABLE [dbo].[DemoPartitionedTable]( [DemoID] [int] ...
29
votes
1answer
41k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
0
votes
1answer
24 views

Postgres - how to investigate a dependency before DROPing a field

I want to drop a field from a partitioned table, POSTGRES claims there are dependencies and proposes using CASCADE. How can I check that nothing important will be deleted before risking DROP with ...
1
vote
1answer
56 views

How to find out if an index is aligned

I'm struggling to find any confirmation within sql server or sql management studio that my index is in fact aligned with my partition. I'm creating a primary key constraint on a partitioned table: ...
0
votes
1answer
78 views

Understanding partition elimination

I am trying to understand the below query being run from our developers on read only database which just runs forever. Table here is partitioned table on partition column trickey per day. Query is ...
3
votes
1answer
2k views

How to shrink space on table with a function-based index?

I have a partitioned table for which I'd like to remove unnecessary free space. Usually I would run commands like this: ALTER TABLE {table name} MODIFY SUBPARTITION {subpartion name} SHRINK SPACE; ...
2
votes
1answer
297 views

Partitioning big tables

I have a DB with multiple large tables. For example one single table has 738,829,922 rows. The primary key of the table is a datetime. For better performance and maintainability the table is ...
1
vote
1answer
121 views

Cannot choose Storage option on a table in SSMS for standard edition

I am looking into partitioning of tables for SQL Server on two different machines. One machine runs Microsoft SQL Server (RTM) 2016 Developer Edition, and the other is running Microsoft SQL Server ...
0
votes
1answer
99 views

Changing Column Data Type with FKs

I want to change my column data type from "Datetime" to "Datetime2(7)". If I try to do it with the GUI SQL Server 2014 asks to save all FKs table dependencies and then I get a lock timeout because ...
1
vote
1answer
149 views

syntax error at or near “current_timestamp” while creating partition table

I am trying to create a partition for the last 30 days. Yet when I run this code: create table Test_Older30day partition of Test for values from (current_timestamp + interval'-30 day') to (...
0
votes
1answer
19 views

DynamoDB: How to model data that's shared and queried by all users?

I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head ...
0
votes
0answers
88 views

Archiving a huge SQL Server table with change data capture and foreign key constraints on multiple columns

We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints. We decided on the archival since the ...
1
vote
3answers
52 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
0
votes
1answer
194 views

Deadlock on partitioned table

we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing. Our setup is like this: ...

1
2
3 4 5
16