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

learn more… | top users | synonyms

0
votes
1answer
278 views

MySql - Global index

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table: CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, ...
0
votes
2answers
190 views

Inputs on handling large volumes of data for specific tables in SQL Server standard

I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB. I have few critical transaction tables like this: 1. Payments 2. Cheques etc., and each of ...
1
vote
3answers
1k views

SQL Server 2008 Partitioning Evaluation

I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning. So we have Subcriber and SubscriberPartitioned tables. Tables structure is: ...
3
votes
2answers
1k 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 ...
0
votes
1answer
1k views

oracle global index vs local index on a partitioned table

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
2
votes
2answers
858 views

Best practices for creating partition schemes and functions

I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning. My main question is: Should I use the same Partition Scheme and Function for ...
6
votes
1answer
495 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 ...
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 ...
1
vote
4answers
530 views

Top 2 rows per partition from an absolute value date difference

I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images. Location + Camera + image capture_date is the ...
0
votes
1answer
186 views

Multiple Partition Keys?

This is a follow-on from a previous question. 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 ...
7
votes
4answers
2k 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 ...
2
votes
2answers
275 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 ...
4
votes
1answer
756 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 ...
3
votes
2answers
552 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?
14
votes
4answers
3k 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 ...
3
votes
2answers
2k 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 ...
14
votes
2answers
3k 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 ...
1
vote
2answers
261 views

Table partitioning

Recently I have been looking to optimize my database and its size is 7GB. I have an option to partition big table. But partitioning would just divide the big table into small ones while remaining on ...
6
votes
3answers
828 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.
0
votes
1answer
1k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
3
votes
2answers
138 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 ...
1
vote
1answer
126 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
4
votes
2answers
653 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 ...
11
votes
3answers
2k 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 ...
2
votes
2answers
862 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.
4
votes
1answer
292 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
2answers
996 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 ...
5
votes
1answer
1k 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 ...
8
votes
2answers
1k 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 ...
4
votes
1answer
1k 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 ...
8
votes
2answers
3k 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 ...