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

learn more… | top users | synonyms (1)

0
votes
0answers
25 views

Broken partition function/schemes

The dilemma SQL Server 2008 Enterprise 2 partition schemes using the same partition function partition function splits on date column, monthly basis maintenance script to split partition function ...
1
vote
0answers
38 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a ...
5
votes
1answer
238 views

Why should I “Avoid partitioning dimension tables” on SQL Server?

SQL Cat have a list of tips titled Top 10 Best Practices for Building a Large Scale Relational Data Warehouse. Under section 4 - Design dimension tables appropriately they state: Avoid ...
0
votes
1answer
43 views

Mysql hash partition

In my system's database we have a table which stores 3 fields: id int, name(varchar) and description (mediumtext). The table is by far the biggest in our database and the queries on it are exclusively ...
1
vote
0answers
36 views

Can I Implement a Sliding Window for a Table Spread Across Two Partition Schemes on the Same Partition Function?

I have a table with a PK based on a bigint identity and will have a clustered index on a datetime2 column. I've created a partition function based on calendar quarter and was planning on two partition ...
-1
votes
1answer
65 views

What is the best source for deep learning about table partitioning? [closed]

I know some of the basics of table partitioning, but I haven't used it for an operational database yet, as I went studying more about the topic, I found some information that is written for SQL server ...
0
votes
0answers
45 views

Insert strategy for partitioned table

Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ...
0
votes
0answers
42 views

Scheduled Partition one time by day

I am trying to schedule partitioning so that it runs only at 11:00pm. For example, I have the following table: Table 1 Name status John 0 Thomas 1 Dimitri 1 Yohann 0 At ...
0
votes
0answers
33 views

SQL Server: can reduce data file with partition table?

I had a database with one table called Saler stored on 'C:\'. Primary MDF file is very big (100G and about 200 million rows) and my disc is nearly full. Now I added a new file group 'Secondary' and a ...
0
votes
0answers
9 views

Designing Users table on DistributedDB

I am using SQL Server 2012 SP2 Express with PDB Wrapper 1.0.4 on 7x Partition nodes + Common server. Currently my users table is shared on common but we see high network latency. We left users table ...
0
votes
1answer
34 views

SQL Server - Show Content of a partition

I did somes partitions on my sql database with the wizard of microsoft Sql Server Management Studio. But i wanted to know if it's possible to do an request for show just lines contains by one ...
1
vote
1answer
88 views

Postgresql INSERT triggers “0 row affected” workaround

In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo. Is there any workarounds for this issue in 9.4?
0
votes
0answers
51 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
3
votes
1answer
75 views

How to implement table purge with partition on SQL Server 2012?

I've read this question: How to remove a table partition But it's not what I want. I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
1
vote
1answer
94 views

Bringing SQL Server filegroups online

We've experienced an sql injection attack which corrupted our main db. The main db had two partitioned tables into filegroups. The corrupt database entered into Suspect mode, and it's been ...
0
votes
0answers
34 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
0
votes
1answer
28 views

Partitioning a database

I have a database that's growing larger by the week (as expected). Key tables are becoming huge and slower to perform queries against. Whilst the indexes are sufficient, I've read the partitioning ...
0
votes
1answer
39 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
1
vote
1answer
74 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
1
vote
2answers
84 views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
1
vote
2answers
694 views

Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size. Now I am stuck using Diskpart to set ...
1
vote
1answer
79 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
3
votes
0answers
605 views

Slow inserts/updates into partitioned table with lot of partitions

I'm designing the partitioning for our new database, but we are facing performance issues in inserting or updating the partitioned table. For simplicity suppose we have one table T, which have an ...
18
votes
1answer
650 views

Seek and you shall Scan… on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a ...
2
votes
1answer
258 views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
2
votes
3answers
120 views

Performance of rare SELECT vs. frequent INSERT in timeseries data

I have a simple timeseries table movement_history ( data_id serial, item_id character varying (8), event_time timestamp without timezone, location_id character varying (7), ...
4
votes
1answer
231 views

Star join query optimization - Change partitioning, use columnstore?

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
1
vote
1answer
139 views

ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint ''

I'm using SQL Server 2008. I have two tables PARTITIONTESTTABLE and PARTITIONTESTTABLESUB. PARTITIONTESTTABLESUB has a foreign key to PARTITIONTESTTABLE. I created partition and both of these two ...
2
votes
1answer
49 views

Partition scheme filegroup metadata

When you create a partition scheme against a particular partition function, you specify a series of filegroups. I cannot find the metadata store where this filegroup list can be found & queried. ...
1
vote
2answers
85 views

Partition of a table - how to minimize the time offline? How to check if all the statistics were updated?

As you ca see on the picture, I have a table called "tblsapdispatch" that has 55,707,259 records. Last sunday 30-aug-2015 they run a big update on this table, and I can see that the statistics were ...
0
votes
1answer
67 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
18
votes
1answer
380 views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
0
votes
0answers
39 views

SPLIT PARTITION issue?

we had written program to ADD partition on every month. But, one of our customer forgotten to execute the program from Jan to July and now, he is facing performance issues (because corresponding table ...
1
vote
1answer
130 views

Diffrences between master and slave replication in mysql?

In a database named asterisk, there is a table named cdr which was partitioned on the time field. mysql> show create table cdr; Create Table: CREATE TABLE `cdr` ( `gid` bigint(20) NOT NULL ...
2
votes
1answer
85 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
4
votes
1answer
190 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
5
votes
1answer
207 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
4
votes
1answer
289 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON ...
2
votes
0answers
42 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
2
votes
1answer
84 views

Deciding what columns to use for partitioning

I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up ...
2
votes
1answer
761 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
3
votes
2answers
129 views

will table partitioning and compression help heavily used OLTP database?

We have a very big table which is over 300gb with over half billion rows in it. This table is used for audit purpose for all critical applications. A lot of inserts are happening per seconds. Due to ...
0
votes
1answer
474 views

Oracle 11g - drop maxvalue partition, keep data

I have some tables with data like this: CREATE TABLE "PARTTEST4" ("year" number(4,0) NOT NULL ENABLE) PARTITION BY RANGE ("year") (PARTITION "P_OLD" VALUES LESS THAN (2010), PARTITION ...
8
votes
3answers
149 views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
3
votes
2answers
274 views

Any way to speed up reorganize partition of a very large table in MySQL?

So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added. The partitions are based off a timestamp which are ...
0
votes
0answers
79 views

Problem Setting up MariaDB Spider HA

I have problem setting MariaDB with Spider for partitioning and HA.My environment consists of: Spider Proxy - 10.64.20.207 Backend1 - 10.64.20.208 Backend2 - 10.64.20.209 First I am creating user ...
5
votes
1answer
4k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
2
votes
1answer
344 views

MariaDB 10 InnoDB Partition with Data Directory

mysql --version mysql Ver 15.1 Distrib 10.0.20-MariaDB, for Linux (x86_64) using readline 5.1 I have a MariaDB 10 installed, though I tried with the 5.5 release in the CentOS repos. I'm ...
2
votes
1answer
1k views

moving a Partition of Partitioned Table to other File Group, SQL Server

I created partitioned table on DateTime column. for last 6 month. I placed 3 oldest month on a file group named 'FG_OlderData', and 3 newest month on a file group named 'FG_NewerData'. Partition ...