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

learn more… | top users | synonyms (1)

3
votes
2answers
511 views

do partitions make sense when dates are not involved?

We are having some discussions about partitioning some application tables in SQL Server 2008 based on their quickly growing size. One is an event table that is typically used for INSERTS (80% of the ...
3
votes
2answers
142 views

Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?

I know this may sound ridiculous but: Is it possible to separate the Data Definition (i.e. Structures of my Tables, Views and other things) and Rows-Data on a File-Level in SQL Server. I think it will ...
3
votes
1answer
742 views

Postgres: Put 1 database in separate HD partition?

I have 2 HD partitions - one with SSD and another with regular hard drives. I have multiple databases (as in logical database, not physical) with 1 data directory. I want the data for 1 of the ...
3
votes
2answers
878 views

T-SQL: How to switch all partitions from one table to another?

Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?
3
votes
1answer
1k views

Partitioning large mysql table

I have a large (40GB) table I would like to partition. The rows are essentially write once read many. Currently I am using mysql 5.5 innodb with file_per_table. It's tough to optimize this table since ...
3
votes
1answer
3k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
3
votes
2answers
66 views

Dealing with partitioned tables in Postgresql

Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided ...
3
votes
1answer
52 views

Returning all rows from multiple partitions of a partitioned table

I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like ...
3
votes
2answers
148 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 ...
3
votes
2answers
232 views

SQL Server 2012 Table Partitioning - Have I done it correctly?

I have a table that stores accounting transactions for what we call "jobs". The table looks like this: ( REGION char(3) JobNumber char(17) SeqNum decimal(5,0) ..... ..... ..... AcctYear decimal(4,0) ...
3
votes
1answer
386 views

Drop partition scheme with orphan indexes

I have a partition scheme in my SQL Server database that I am trying to drop. This scheme was used by 2 indexes, which I deleted. I can't delete the scheme, since it appears to still be used by the ...
3
votes
2answers
967 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
3
votes
1answer
11k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
3
votes
1answer
4k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW "...
3
votes
1answer
2k views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
3
votes
2answers
4k views

mysqldump vs LOAD DATA INFILE

Why would you ever use mysqldump to repopulate a table over load data infile? Here's the back story to my question. We had a table that was incorrectly partitioned. It was supposed to be ...
3
votes
2answers
68 views

How to sort the data fetched from horizontal partitioned tables

I have a telco billing software system. In it there are daily logs of users' calls. The logs are horizontally partitioned by date (month). Each partition is stored in a separate database and may be ...
3
votes
1answer
103 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, ...
3
votes
1answer
376 views

(How) does table inheritance interfere with locking in postgres?

I have a table inheritance setup that might be simplified like this: CREATE TABLE p ( id BIGSERIAL PRIMARY KEY, type_id BIGINT, approved BOOLEAN ); CREATE INDEX ON p(approved); CREATE ...
3
votes
3answers
6k views

How to partition a table by timestamp, where data from every i'th day goes to partition i?

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that. The basic idea that I want to capture is a ...
3
votes
1answer
443 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
3
votes
1answer
2k views

How to structure Master/Detail tables for performance without denormalizing?

Some Background Info We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails. There are roughly 80k Transactions a day which translates to ...
3
votes
1answer
589 views

Oracle: Interval partitioning and dependant tables

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
3
votes
1answer
74 views

Drop 15 days records in monthly partition

I have created a monthly partition on my table like below: PARTITION BY RANGE (PARTITION_KEY) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION MayPartition VALUES LESS THAN (TO_DATE('...
3
votes
1answer
43 views

High availability on a subset of the data

On my site, I have a set of big databases (+6TB total) that is running on a SQL Server 2012 instance. One of these is the main DB and the other ones are archived data that are queried sometimes (...
3
votes
1answer
620 views

How to maintain a global index on a large table undergoing regular partition purges?

Problem I've got giant table haystack weighing in at nearly 3 TB, partitioned by month (date) and subpartitioned by client. I need to create a global index that spans all clients to facilitate ...
3
votes
1answer
549 views

What's the best way to structure logging application data in MySQL

I may be overthinking this but: I have an API; I want to log calls to the API for stat/debugging purposes. Ideally the speed of reads should be quick but is not essential (as will primarily be read ...
3
votes
2answers
17k views

Adding subpartitions to existing partitioned table

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table? I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will ...
3
votes
2answers
4k views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
3
votes
3answers
782 views

High insert rate on MySQL

I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
3
votes
1answer
931 views

Why am I getting Table scan on a partitioned table query?

I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table. When I run the following: SELECT * FROM dbo.INVENTORY_BREAK WHERE IR_ID ...
3
votes
4answers
864 views

Is it possible to virtually vertical partition and oracle database table?

Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate ...
3
votes
2answers
165 views

SQL Error: ORA-14300: While partitioning and subpartitioning

I have tried this earlier and it works: I have T1 which has no DATE or STATE which needs to be partitioned on DATE and sub-partitioned on STATE Hence for the ID present in T1, fetch the DATE and ...
3
votes
2answers
393 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 ...
3
votes
1answer
208 views

Why can an Oracle SET UNUSED COLUMN cause internal UPDATE SET X=X and huge log generation?

On Oracle 11.2.0.4.1, we tried to run a simple ALTER TABLE PARTITIONED_LARGE SET UNUSED COLUMN XXXXX; on a large ref-partitioned child table to decommission a column. The table has hundreds of ...
3
votes
2answers
317 views

mysql - is it possible to create a partition expression based on two keys / indexes?

if i have a table with, lets say 500 million rows, and among others there are two main individual indexes on that table. the table looks like: CREATE TABLE `t1` ( id_1 bigint unsigned not null, ...
3
votes
1answer
80 views

What inefficiency does sharding & partitioning attempt to address? [closed]

I have never implemented sharding or partitioning, but from what I understand, multiple servers are used to distribute large amounts of data. Is this because of a limited hardware budget or to ...
3
votes
1answer
306 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called client_id....
3
votes
1answer
294 views

Where are partitions defined in mysql

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data. How do I go from new disks (lets call them ...
3
votes
1answer
278 views

Adding new key(s) to enable partitioning, INSERT performance?

I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 different devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
3
votes
1answer
18 views

Sybase ASE 15.5 : Create a custom function for partitionning

I want to create a partitioned table to store messages. If the message belongs to "an even week number" => go to the partEven partition ! If the message belongs to "an odd week number" => go to the ...
3
votes
0answers
90 views

Best design to reference multiple tables from single column?

Proposed schema First and foremost, here is an example of my proposed schema to reference throughout my post: Clothes ---------- ClothesID (PK) INT NOT NULL Name VARCHAR(50) NOT NULL Color VARCHAR(...
3
votes
0answers
820 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 ...
3
votes
1answer
37 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 ...
3
votes
0answers
369 views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
3
votes
0answers
412 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
3
votes
1answer
183 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
3
votes
0answers
560 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
3
votes
2answers
3k views

ALTER TABLE SWITCH statement failed

ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables... Does anyone knows how to fix that on already existing table full of data? Well, I've ...
3
votes
1answer
650 views

SQL Server data files in Windows Azure: filegroups and partitioning still applicable?

I just created a database using the new functionality "SQL Server 2014 data files on Windows Azure" and this works quite well. However, I have a table with a billion records and in our local ...