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

learn more… | top users | synonyms (1)

4
votes
1answer
51 views

Truncate parent table but not children

I have a table in PostgreSQL 9.4 that I partitioned so that all inserts to the parent actually go into one of the child partitions. The problem is that I already have hundreds of millions of rows of ...
4
votes
1answer
1k views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
4
votes
1answer
344 views

Proper Database Partitioning

I had asked a previous question "Proper technique for storing users event data" and the correct answer in my opinion was to create a database partition. Now from what I have read on it there are ...
4
votes
1answer
139 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 ...
4
votes
1answer
376 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
4
votes
1answer
219 views

Table partitioning filegroup

Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
4
votes
1answer
146 views

SQL Server data partitioning

This is for SQL Server: I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the ...
4
votes
1answer
472 views

SQL Server Partitioning - Parent / Child tables

I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is ...
4
votes
1answer
67 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 ...
4
votes
2answers
325 views

Strategy to deal with multi billion row simulation results and partitioning

I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the ...
4
votes
2answers
2k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...
4
votes
1answer
3k 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
992 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
0answers
35 views

SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :) A few times I have ran into a weird scenario; when I run: SELECT * FROM ph.FileGroupDetail ...
4
votes
1answer
2k 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 ...
4
votes
2answers
3k 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
259 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
4k 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 ...
3
votes
1answer
905 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
1answer
827 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
2k 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
5k 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
1answer
275 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
460 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
141 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
2answers
711 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
589 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
4answers
568 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
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
1answer
152 views

How do I create a partition using a bit field in SQL Server 2014

I have a table where data is archived. I need to keep this data as it is rarely viewed. Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When ...
3
votes
1answer
217 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

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
1answer
142 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
2k 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
318 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
233 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
3answers
676 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
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
1answer
77 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
3answers
4k 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
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
638 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
2answers
6k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
3
votes
1answer
440 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
648 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
1answer
88 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
194 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
2answers
2k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...