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

learn more… | top users | synonyms (1)

4
votes
1answer
2k views

Partitioning a table with LOB columns in Oracle 11g

we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that ...
4
votes
1answer
869 views

How to best store the Google Web Ngram data?

This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data. I'm looking to store the Google NGram Web data, which is slightly ...
4
votes
2answers
2k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
4
votes
1answer
226 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
3
votes
1answer
784 views

Why do we have to use SWITCH in table partitioning?

I'm a beginner in SQL. I'd like to know what is the importance of SWITCH in table partitioning? I've created one table partitioned month-wise, with a total of 12 partitions. The ranges are: ...
3
votes
4answers
190 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
3
votes
1answer
552 views

Will table partitioning speed up my queries?

My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their ...
3
votes
3answers
1k views

Is it a good practice to apply Vertical Partition to a newly created model?

Suppose you are modeling the ER model for a new Object Oriented Web Application. There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
3
votes
1answer
246 views

SELECT multiple sensor values in one query

Background I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
3
votes
2answers
442 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
139 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
3k views

How to create a partitioned table based on date?

I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
3
votes
2answers
616 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
718 views

ALTER TABLE … SWITCHing from regular table to partitioned table fails

The code below does the following: Creates a database play_partition in C:\TEMP Creates two identical partitioned tables play_table and archive_play_table Switches play_table partition 1 to ...
3
votes
1answer
253 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
89 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
1answer
2k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
3
votes
1answer
2k 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
3answers
1k views

Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 ...
3
votes
1answer
221 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
1k 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
225 views

Optimizing queries against a set of tables that contain partitioned data

I have a third party program that creates and populates a series of tables to hold some historical data: data_33 -- contains data for 12/01/11 - 12/05/11 data_34 -- contains data for 12/05/11 - ...
3
votes
1answer
1k 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
3answers
567 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
1answer
133 views

Rank Partition?

I have a table which holds customer information with an Account state (working or Deactivated) for every single day. I'm trying to reduce this information by showing start and end dates for the ...
3
votes
2answers
3k 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
2k 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
518 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
335 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
495 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
2answers
97 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
74 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
1k views

How to take partitions or filegroups out of service

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows ...
3
votes
1answer
211 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 ...
3
votes
1answer
395 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
3
votes
1answer
259 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
0answers
226 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
1k views

partitioning by datetime vs date vs int performance

I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server. We know we want daily partitions. The data has a natural DATETIME field in it, so my gut ...
3
votes
2answers
2k 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
113 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 ...
2
votes
1answer
919 views

Join tables in two databases make query slow? is better to partition db?

I have a table1 in db1 and table2 in db2 on SQL Server 2008 R2. If I make a select query joining both tables, it's really slow to get the results. A simple query like SELECT * FROM db1.dbo.table1 ...
2
votes
2answers
2k views

Why the query scan all the partitions?

I have the following partitioned table create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month) The partition function is CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
2
votes
2answers
1k views

Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column?

I'm not thinking of using it for the partition function. I just want to know, if it is supported or not. Don't tell me why it is not a good idea to use such columns on partitioned tables.
2
votes
1answer
342 views

Is DBCC SHOWFILESTATS an accurate way to see unused space?

We have a few tables each with near 100 million rows of data. A co-worker has these tables setup with partitions based on a date field. Our disk space is starting to run low and knowing that ...
2
votes
1answer
3k views

When is data moved during an Oracle partition split?

I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do: alter table ...
2
votes
2answers
295 views

MySQL Partitioning

I have a MyISAM table that holds a few million Messages. I am thinking about converting it to an partition InnoDB table. Just wondering about the best partition strategy. I would like to do this ...
2
votes
2answers
124 views

Update on view over partitioned tables updating all indexes

We have a table, which is partitioned through a date field into separate years. There is a view over all of these tables (Call) Schema is as follows: CREATE TABLE [dbo].[Call_2015]( [calID] ...
2
votes
2answers
261 views

Partitioning a multi-terabyte database [closed]

We have a database which is nearly 3TB in size, out of which 8 tables together are 1.5TB which we want to partition based on year. But as we will be partitioning over a terabyte of data, this will be ...
2
votes
1answer
162 views

Can not add list partitions

Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it. ...
2
votes
2answers
378 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 ...