Splitting a database table into multiple segments for performance or manageability.
0
votes
1answer
26 views
Switching out of one scheme into another
I have a table named dbo.messages. We are expecting a lot of data in this table. To keep things to a reasonable level we are looking at a weekly partitioning on that table. What I would like to do ...
0
votes
0answers
51 views
What is the right way of doing aggregation to partitioned table? [on hold]
Suppose I have the following table transactions with columns
(day_id number, transaction_id number, amount number)
and partitioned by day_id.
In Oracle, Why
select day_id, sum(amount) sum ...
0
votes
1answer
40 views
Optimizing SQL query with two table joins and over 40 million rows in partitioned table
I have three tables, lets say their names are:
table1 (has total over 40 million rows, but is partitioned with Declarative partition https://www.postgresql.org/docs/10/static/ddl-partitioning.html ...
4
votes
1answer
67 views
How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?
I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
1
vote
1answer
27 views
Select data with the latest table partition that actually holds data
Version: SQL Server 2016
So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. ...
0
votes
0answers
17 views
Sharding at SQL Server level [duplicate]
I would like to get some advice about how to implement sharding at SQL Server Level (on-premise), basically, the architecture is something as shown below:
Stand Alone.
Writer: SSIS, Linked Servers
...
0
votes
1answer
40 views
SQL server - Re. Filegroup / partitioning
Assuming VLDB is split into multiple file groups, each having its own file and on a separate disk.
Now let's say there is a disk crash bringing down one of the file groups - then will the other parts ...
0
votes
1answer
23 views
Counting using window function with partition over an empty table
If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?
select count(*) over (partition by field_name)
from ...
0
votes
1answer
42 views
Select unique records from multiple records based on max date
I have table like below
+----+----------+-----+-------------+--------+
| ID | ADDR | IND | update_Date | Sq_NBR |
+----+----------+-----+-------------+--------+
| 1 | PHYSICAL | Y | 11-Oct ...
1
vote
0answers
43 views
Handling backups of a large table (>1 TB) in Postgres?
I have a 1TB table (X) that is a pain to backup.
The table X contains historical log data that is not often updated after creation. We usually only access a single row at a time, so performance is ...
0
votes
1answer
20 views
How do I add a partition to an existing table in mariadb / mysql?
I have the following table.
I want to add partitions too.
CREATE TABLE `app_log_Test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`dateCreated` datetime NOT NULL,
`host` varchar(512) COLLATE ...
1
vote
0answers
37 views
MySQL 5.7 issue with 16TB disk
(Repost from StackOverflow)
I have a MySQL 5.7 running on a Windows Server 2016 VM with two disks from a NAS: C:\ for system and D:\ for data (empty at start). I try to create a partitioned table:
...
2
votes
1answer
42 views
Question on best practice - keep empty partitions at both ends of your table?
I was reading ALTER PARTITION FUNCTION best practices which says -
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) ...
0
votes
1answer
13 views
Create Range Partition on existing large MySQL table
I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 ...
0
votes
1answer
28 views
Internal error code, kksgaItLvl_Setup [closed]
A simple query fails with the error. Has anyone faced this issue or has any solutions?
SELECT MAX(some_value)
FROM some_table
WHERE some_id = 62328188199
AND some_name = '...
1
vote
1answer
40 views
Log shipping from SQL Server 2014 Enterprise db to SQL Server 2017 Standard with partitioning
I wish to implement log shipping from my current primary which is a SQL Server 2014 Enterprise database to a SQL Server 2017 Standard edition database. I also wish to implement table partitioning on ...
0
votes
0answers
18 views
mongodb - monthly archival of data
Coming from a RDBMS background, this is what I have done in the past.
Create a table with partitions based on a date field. There are typically three tables (partitions) - current (M0), previous ...
0
votes
0answers
34 views
Is Postgres sharding available on Aurora (AWS)
I need to shard and/or partition my largeish Postgres db tables. The Postgres partitioning functionality seems crazy heavyweight (in terms of DDL). I see talk from <=2015 about pg_shard, but am ...
0
votes
3answers
50 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 ...
1
vote
1answer
26 views
MySQL Partitioning based on addition
I cannot find a good keyword to search for this kind of question, so here you go:
Suppose i have this table:
CREATE TABLE test (
tr_uuid BINARY(16) NOT NULL,
Customer VARCHAR(10) NOT NULL,
Data ...
0
votes
1answer
10 views
Non matching attribute 'INSTANT COLUMN(s)' between partition and table
I have a program that regularly exchanges a partition with a table.
MyTable is a partitioned table, and MyTableTemplate is an empty copy of this table, with the same structure but no partitioning.
...
1
vote
2answers
42 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 ...
0
votes
4answers
33 views
filter out the records with same id having different values in a column
Can anyone help me to solution for below issue.
create table [order1] (order_id int, CartTransaction_Id int, Status_Id int);
insert into [order1] values
(357488,87214,1),
(357489,87214,8),
(357490,...
4
votes
0answers
29 views
Variable column size overflow when merging partition
Supporting a product that out of the box does ETL to an OLTP DB every night for that day's activity, and every day is a new partition. Well, over x number of years this gets kind of messy. So in the ...
-4
votes
2answers
115 views
Can data size be less than index size in sql database?
Can the indexes on a SQL Server table consume more disk space than the table itself?
I have gone through several articles and studied this and a few expert recommended table partitioning, for ...
1
vote
1answer
39 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
40 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 ...
3
votes
3answers
908 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
50 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
66 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
325 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
641 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
95 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
79 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
25 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
37 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
12 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 ...
2
votes
1answer
97 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
149 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
118 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
137 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
69 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
24 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
57 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
66 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
144 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
76 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
724 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
43 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
71 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 ...
