Splitting a database table into multiple segments for performance or manageability.
14
votes
4answers
2k views
SQL large table design
I have a general question about SQL Server 2008 table(s) design. We currently have a table that is over 600GB and grows at about 3GB a day. This table has the appropriate indecies but is becoming a ...
11
votes
2answers
2k views
How Does Table Partitioning Help?
I am having difficulty to grab the idea of pros and cons of table partitioning. I am about to start work on a project which would have 8 tables and one of them will be the main data table which will ...
11
votes
3answers
847 views
Does the partition key also have to be part of the primary key?
I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
10
votes
2answers
1k views
Can I move rows between partitions by updating the partition key?
I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.
The question:
Can you move rows of data within a partitioned table from ...
9
votes
2answers
1k views
SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
8
votes
1answer
279 views
What is the modern way to partition PostgreSQL across machines, when the data is “naturally partitionable”
After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature.
Today I see data stores with quite different eyes than before. Things like Riak ...
8
votes
1answer
262 views
Disk file effects of delete and vacuum
I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
7
votes
2answers
2k views
SQL Server 2008 - Partitioning and Clustered Indexes
So let me preface by saying I do not have total control over my db design, so a lot of the aspects of the current system cannot be changed for the purposes of this scenario.
Comments about how we ...
7
votes
4answers
1k views
SQL Server Partitioning - what to use for partition key?
I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued ...
7
votes
2answers
881 views
How to best store Google ngrams in a database?
I downloaded google onegrams some days ago and it’s already a huge amount of data. I inserted the first of 10 packages into mysql and now I have a 47 million record database.
I am wondering how one ...
7
votes
3answers
765 views
Table partitioning for archiving data
Scenario:
two databases: DB_A and DB_Archive with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A and moved
to DB_Archive mainly to leave thing ...
7
votes
1answer
266 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
6
votes
3answers
688 views
non-clustered unique indexes on sql server partitions
SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.
I'm wondering why.
6
votes
2answers
159 views
Is it worth partitioning a Table by single value in Oracle?
Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
5
votes
2answers
221 views
Get a record's current partition
In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
5
votes
1answer
397 views
PostgreSQL partition hierarchy design for centralized logging
I am looking into setting up logging from all of my servers into a centralized postgresql database. It makes sense to be able to drop logs by date or by host, so I'd like to set it up with ...
5
votes
2answers
122 views
Mechanics of partitioning
Imagine the following hypothetical database structure. Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).
I want to partition my SQL ...
5
votes
2answers
651 views
Making the Oracle optimizer use a virtual column to find out about a partition
I have a large table that has three columns like this:
"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
GENERATED ALWAYS AS
(
(extract(YEAR FROM START_DATE) * ...
5
votes
2answers
270 views
Recreate Indexes on 1 billion record table
I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
5
votes
1answer
416 views
Database archive solutions
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
5
votes
2answers
361 views
Large accounting table, best way to partitioning by two dates
I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
5
votes
1answer
832 views
DROP PARTITION strategy using LIST-REF composite partitioning
We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
5
votes
2answers
250 views
Partition Function vs. Clustered Index
We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
4
votes
1answer
244 views
Why isn't partition-level lock escalation the default?
In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.
However, this isn't automatically ...
4
votes
2answers
6k views
How do I get a list of all the partitioned tables in my database?
How do I get a list of all the partitioned tables in my database?
Which system tables/DMVs should I be looking at?
4
votes
2answers
443 views
Creating partitions on a production database
I have a production MySQL 5.1 database that is running well but I want to improve query performances. I have never used Partitions and just going through the manuals.
I have two tables that involve a ...
4
votes
1answer
980 views
How do I horizontally partition an oracle database table, and should I?
We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment.
Create ...
4
votes
2answers
245 views
Huge database logging of event type rows and ways to optimize it
We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years.
Usual use of these events is selecting some of them from the ...
4
votes
1answer
193 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
393 views
Best way to increase no of partition in SQL Server Table
I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.
Can anyone ...
4
votes
1answer
253 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
80 views
Expanding a partitioned table
I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
4
votes
1answer
324 views
Partitioning a table vs moving entries to another table
I don't understand how partitioning helps me more or less than using a separate table to store older records.
I need to maintain a list of stock entries along with historical records about the stock.
...
4
votes
1answer
124 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
260 views
Full-text index in partitioned table
I have a table with just over a million records with a full-text index.
This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact ...
4
votes
1answer
552 views
MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
4
votes
1answer
954 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
525 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
109 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
132 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
383 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
2answers
379 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
130 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
448 views
Will creating partitions reduce locking and how do we implement this in sql-server?
A client wants our application to process more data faster so arranged a meeting with their dba to discuss options.
This application generates quite a lot of data that is used for reporting. Before ...
3
votes
1answer
1k 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
190 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
292 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
109 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
1answer
61 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 ...
3
votes
2answers
273 views
Large Fact table and partitioning key dilemma
I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes.
The table ...
