Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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

0
votes
0answers
17 views

Drop Table Problem: No catalog entry found for partition ID in database Server 2016 SP1 Enterprise

I am getting an error while dropping a table or its partitioned column with the following command: DROP TABLE [dbo].[TABLE_NAME] The error message is as follows: Msg 608, Level 16, State 1, Line ...
0
votes
1answer
16 views

How to deal with unbounded growth in cassandra shards

Let's say we have a website like youtube where we have a comment session per e video. Users want to be able to comment and to see the comments made by other users by comment creation time. This is ...
0
votes
0answers
26 views

Partitioning Table: SQL Server

I created 12 partitions from July 2017 through July 2018 and didn't leave an empty partition at the end. Hence, am having a hard time adding new partitions using the split function. I even tried ...
2
votes
3answers
845 views

Can I speed up a large MySQL/MariaDB join through partitioning or merging?

I'm trying to run the following join query as part of a more complex one on MariaDB 10.1.26. select distinct project_commits.project_id, date_format(created_at, '%x%v1') as week_commit ...
2
votes
3answers
40 views

Size of Partitions in Partitioned Tables

According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies ...
1
vote
1answer
33 views

What are the downsides of using PARTITION for multi-tenant access?

We have an RDS Postgres database which keeps individual store data in a common schema with a field called store_id to distinguish between stores. Let's say we have 1MB per store and 10k stores so we'...
0
votes
0answers
24 views

MySQL: ROW_NUMBER() OVER PARTITION Question

I am looking to essentially recreate the ROW_NUMBER() OVER PARTITION function that I have used in SQL Server Management Studio previously to do the following in MySQL. MySQL doesn't seem to want to ...
4
votes
2answers
576 views

Move data from a table to another without losing data

I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is: Created a partition function and a partition scheme. Created ...
1
vote
1answer
44 views

How to move a Daily Partition to a Monthly Partitioned Table

I would like to switch out a partition from a daily partitioned table and merge it into monthly partitioned table. I am unsure as to how to go about it. Here's what I have so far. I have two tables,...
0
votes
1answer
33 views

Any workarounds for lack of primary key on partitioned tables?

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not ...
0
votes
0answers
17 views

Partitioning in PostgreSQL 10

Currently we are using pg_partman for partitioning our tables in PostgreSQL 9.4. We are planning on upgrading to PostgreSQL 10. I read that PostgreSQL 10 has better support for partitioning. While I ...
0
votes
0answers
34 views

Create partitioned with move partitioned data from original table

I have big size of database almost 500GB and i just wanted to retain 1 years of data from some large tables and move others data to a different location, So I am trying this by creating partitions on ...
0
votes
0answers
11 views

Partitioning of multiple tables in parallel or at same time

I'm working on Microsoft SQL Server and in my project's production environment, we have to perform partitioning of multiple tables as part of housekeeping activity. Right now it is performed in a ...
1
vote
1answer
59 views

Mongo CP, Cassandra AP?

I have read lot of articles on net but still confused why Mongo CP, Cassandra AP, RDBMS CA ? Will explain my understanding and query along with that . Mongo Consider a scenario where I have ...
0
votes
1answer
57 views

Rename column in a partitioned table

I want to rename a column in a partitioned table. Will oracle do this for all partitions? Is there anything special for this change?
2
votes
1answer
78 views

Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described. Partitioning our billion+ row table by week would be beneficial, more ...
4
votes
2answers
128 views

Partition elimination on order by

I have a partitioned table and problems with partition elimination. Setup My table if quite simple and looks like this: CREATE TABLE [data].[OperationData]( [DataLoggerID] [bigint] NOT NULL, ...
4
votes
3answers
55 views

switching from normal list partitioning to reference partitioning in oracle 11g

I have a main parent table and multiple child tables which have normal partitioning in it, wherein the partition column is repeated in all child tables. I want to switch to reference partition(oracle ...
0
votes
0answers
21 views

Slicing a partition by current year, previous year, and two years ago

I have a cube that has partitions of this year, last year, and the year before last. Currently the partitions filter dates using a SQL query, but I'd like to replace that with slices. Ideally, I'd ...
0
votes
3answers
44 views

Question on MySQL Partitioning

Using version 5.5 (on which I'm stuck), I have a table I want to partition, here is the DDL: CREATE TABLE `emp` ( `id` int(11) DEFAULT NULL, `date1` bigint(20) DEFAULT NULL, `gen_year` int(11) ...
0
votes
1answer
60 views

sql server partitioned view wrong execution plan on update a temporary table

i need your help because we have a performance issues. I would like to update a temporary table that contains a join to the partitioned view. Schema: create schema SCHEMA1 go create schema SCHEMA2 ...
1
vote
1answer
121 views

Splitting Existing Partitions in SQL 2014

I have a SQL Server table that contains over 20 billion rows, with 13 billion rows in the last partition. The table is currently partitioned 6 ways by a "Time" field: 2011 2012 2013 2014 2015 2016/...
0
votes
1answer
58 views

Database design / database tuning for market basket analysis on ownership of products

My goal I want to record data for a market basket analysis (https://en.wikipedia.org/wiki/Affinity_analysis) on computer software and query this this data frequently. Data description One user may ...
1
vote
2answers
264 views

Delete data from a specific partition in MS SQL 2012

I would like to delete data from a specific partition using the partition ID. I got queries to truncate data from specific partition for SQL 2016 but did not find any query for lower versions. I ...
0
votes
1answer
42 views

Find continuous records with equal values characters [closed]

I need your kind support to resolve this issue. I have a table contains similar to the below data : |AccountNo|remarks | |Q32571 |Actual Read| |Q32571 |Locked | |Q32571 |Actual Read| |...
2
votes
1answer
56 views

Dealing with out-of-range values on a PARTITION BY RANGE

I have a table for storing events which is partitioned on a timestamptz column by month using PARTITION BY RANGE. There are 5 partitions at the moment, containing a span of a month each, starting ...
0
votes
1answer
48 views

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

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 ...
0
votes
0answers
16 views

Function that Mysql uses for key partitioning

I want to be able to figure out which partition a row is added to when using a mysql table that has n number of partitions based on a key (like a field VARCHAR(15)) I tried password(field)%n and md5(...
0
votes
1answer
49 views

How to find the time taken to execute a script

As a part of partitioning table,1M records were inserted into a table using insert statement and I forgot to capture the time taken to insert. Is there any way to find out the time taken to execute ...
1
vote
2answers
52 views

Will table partitions help me?

I have 10 tables. dbo.Table2008 dbo.Table2009 ... dbo.Table2018 Each table holds approximately 500,000,000 records which are 20 columns wide (if that matters). And each table has a clustered ...
2
votes
1answer
59 views

Table Partitioning and updates to partition subset

I am partitioning a large table (2 Billion records ) on an integer say AssetID that has 70,000 unique values, due to partitioning limitation of 15,000 I will create a partition on say 10,000 values ...
0
votes
1answer
14 views

MySQL Queries to get only runtime/duration, not fetched results

We are running few benchmark queries against our test RDS instance for checking efficiency of partitioning, before adapting it to production, where there is no partitions presently. We have few ...
0
votes
1answer
24 views

data fetch with multiple filters in a paginated way

We have a very huge table with around 10 partitions in our system. The table data has multiple references in other tables. The database administrator has advised us to avoid performing any join query ...
0
votes
1answer
33 views

Vertical Partition MySQL table

I am reviewing a case, where I have 200+ columns mostly varchar(100). The columns are from several external data sources like CRM / Demographics data etc. I cannot hold them in one MySQL table with ...
1
vote
1answer
64 views

Table Partition Switch Problem SQL Server

I have Tables Schema: CREATE TABLE [dbo].[t1]( [id] [bigint] IDENTITY(1,1) NOT NULL, [id_file] [bigint] NOT NULL, [id_campo] [bigint] NOT NULL, [valor] [nvarchar](255) NULL, [id_doc] [bigint] NULL, ...
1
vote
1answer
16 views

How to determine that the maximum size of the database file is exceeded, and switch the data insertion to the second server?

First I`m sorry for my bad English. My problem is I have one virtual server with PHP backend and one server with one single-table MySQL database. I don't want the file size of the database to exceed ...
4
votes
0answers
61 views

Moving a very large (40 TB) DB from a 2008 server to a 2016 server [duplicate]

The Situation: Our 2008 MSSQL server is old and rapidly running out of drive space. We have a new 2016 server ready, we just need to move a 40 TB database from the old server to the new one. The DB, ...
3
votes
1answer
78 views

Composite Primary Key on partitioned tables, and Foreign Keys

We have a database where several tables are partitioned. The primary key (PK for brevity) for these tables is of the form transaction_date, id Where transaction_date is the partitioning key, and id ...
2
votes
1answer
69 views

Unable to Query Very Large Table

I have a table defined as: CREATE TABLE [dbo].[DataTable]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [Data] [varbinary](max) NULL, [Preview] [varbinary](max) NULL, CONSTRAINT [PKDataTable] ...
2
votes
1answer
79 views

Partitioning increases CPU usage in mariadb

I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific ...
2
votes
1answer
49 views

Creating additional unique keys on a partitioned table

I am utilizing partition schemes in order to logically isolate data based on a TenantId column. TenantId is the partition column and clustering index across all tables that require tenant isolation. I ...
0
votes
1answer
123 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
vote
1answer
148 views

What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on. We are using partitioning for this and ...
3
votes
1answer
58 views

How to eliminate tables in a partitioned view

I can't get my query that joins a regular table to a partitioned view to eliminate tables that don't meet the predicates on the partitioning column. In particular, I'm interested in the case where I ...
1
vote
2answers
207 views

SQL Partition Function without actual values [duplicate]

I would like to partition a column by fiscalyear. How Do I create a partition function without harding coding values? This is what I currently have below. create table dbo.CustomerAccount ( ...
1
vote
1answer
48 views

Refactoring a very large database table and having join over millions of records

I have a fairly big postgresql jobs table of (more than 60 attributes) and one critical query contains joins with this table of million records. and I cannot reduce its size(paritioning by range which ...
1
vote
1answer
32 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 ...
0
votes
2answers
29 views

Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction. As per the calculations, ...
0
votes
0answers
95 views

Partitioning in SQL Server: Best choice and partition keys

In my application, I want to partition two tables, "files" and "value_text", because in the next days I have to insert 300 million rows (files) and 1.200 million rows (value_text) in these tables. ...
0
votes
3answers
87 views

mysql re-partition table

i have the following table. and i wan't to repartition it, adding a few more partitions to shard the overall size. however i don't find any information on how to add partitions in this situation. i ...