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

learn more… | top users | synonyms (1)

2
votes
2answers
247 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] ...
4
votes
4answers
255 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
569 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 ...
0
votes
1answer
107 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
6
votes
1answer
7k 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 ...
1
vote
2answers
8k views

How to remove a table partition

I would like to remove a partition from a table in a SQL Server 2012 database. The table has a primary key but the partition column is not a primary key. I would also like to keep the data structure ...
2
votes
1answer
1k 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 ...
1
vote
1answer
464 views

PostgreSQL partition pruning?

In postgres partition is done by child tables. My problem are, when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than ...
1
vote
1answer
38 views

Are there any inherently non-partitionable datasets?

Are there any practical datasets that, by their nature, cannot be partitioned in any practical way? I am wondering about this because such datasets would be ill-suited to scaling out on commodity ...
-1
votes
1answer
1k views

Oracle Partition Table Space Issue

I have a table with list partitions on it. When i insert data i see that all the partitions have default initial size of 8 MB and I also noticed that when i truncate full table and check size it still ...
2
votes
1answer
33 views

remove a paritition table

I need to remove a partition table for an application upgrade. It is SQL Server 2008 R2. The partition column is on column col1 data type smallint, null. Col1 has length 10 and precision 5. As you ...
2
votes
1answer
118 views

Best Practice in storage allocation for compressed and partitioned tables

I have a fairly large database that basically consists of one table, this table is paritioned by month and all partitions are compressed with page compression. After the initial load of the data there ...
2
votes
0answers
77 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
0
votes
1answer
95 views

MySQL 5.1: How to rollback partitioned table

I have partitioned a table on the basis of one flag column have value either 0 or 1. Now, I want to roll back these tables. How can I do this?
3
votes
1answer
347 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 ...
0
votes
1answer
272 views

Oracle DB - Is it possible to distribute data of a table onto multiple servers?

BigTable[1]-like systems (HBase[2] for instance) allows a table to be distributed transparently accross multiple servers. This works as follows: Each server perform the read/writes operations on ...
0
votes
1answer
397 views

How to split the Wordpress database?

FYI - I originally posted this question under the Wordpress Development site here: http://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database I have a site that is used ...
0
votes
1answer
66 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
2
votes
3answers
6k views

Partition by year and sub-partition by month mysql

I have a database with a date column having datatype as date. I am using mysql 5.6 The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub ...
0
votes
1answer
2k views

How to get accurate row counts in partitions in a table

I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes. So how can I get exact row count for partitions in a 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 ...
0
votes
1answer
73 views

Does partitioning sometimes require denormalization?

Specific example of what I'm asking: Say that a SaaS app has an Order table with CustomerID and a child OrderItem table. We want to partition both tables and we want to use CustomerID as the ...
0
votes
1answer
82 views

How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

Consider a table with the following columns: phone BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64), state VARCHAR(64) We would like to partition the table on state, however that would require ...
1
vote
3answers
6k views

Is table partitioning improving performance? Is it worth it?

I have just been involved in a project on which I will have to develop a data migration process and a web interface that uses an already existing SQL Server DB. This DB was developed by another person ...
2
votes
1answer
3k views

Sharding Postgres Database

I have a Postgres database that has grown to the size where it is no longer feasible to store everything on a single database node. There is a Customer table in my schema where each row represents a ...
11
votes
1answer
217 views

How can I interpret the results of these DMVs to help me evaluate our partitioning strategy?

Version: SQL Server 2008 R2 Enterprise Edtn. (10.50.4000) In attempt to evaluate our partitioning strategy, I wrote this query to get the access methods against indexes on partitions (in the broadest ...
1
vote
2answers
133 views

Oracle tables partition related

Here is my doubt. Is it possible to apply a partition to one table depending in the partitions values of another one? Why I am asking? I have one table where information headers are persisted, and ...
1
vote
1answer
96 views

Partitioning a table state

So far I always used date ranges to partition my tables, however now I like to do it by state or a selected list of cities where all others that are not explicitly listed in an "other" partition, is ...
-1
votes
2answers
225 views

What are the possible risks involved in partitioning large database tables? [closed]

There has been much talk about the benefits of table partitioning in Relational DBMS. I would like to know: Are there any risks to table partitioning ? What measures are there to take to minimize ...
1
vote
1answer
293 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
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 ...
1
vote
1answer
22 views

Logically Horizontale-Partitioned Tables

I have two legacy tables, lets call them TableA and TableB. Both of them fall under the same schema design with identical columns, in other words you can union them directly as one table. The only ...
3
votes
2answers
889 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 ...
0
votes
1answer
276 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
0
votes
1answer
273 views

Does Oracle Partition by Reference optimizes join queries

Oracle 11g has a new feature in partitioning tables called partition by reference. I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
2
votes
2answers
185 views

Partition : understanding files and file groups

We are trying to partition a large history table. Being new to partitioning, I was trying to understand the concept of files and file groups. File groups can be set up in following three ways : 1. ...
0
votes
1answer
93 views

Proper drive partitioning in Linux for Oracle

I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions. Can ...
9
votes
1answer
585 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
1
vote
1answer
688 views

Problem deleting detached partition in DB2 LUW V9.7

Recently in prod environment we are having problems while dropping the detached partitions its giving this error """ DB21034E The command was processed as an SQL statement because it was not a ...
5
votes
2answers
1k views

Oracle partition by interval using persian calendar

Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say: select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian') from my_table; I created a table as: create ...
8
votes
1answer
2k views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
3
votes
1answer
635 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 ...
0
votes
2answers
4k views

Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
1
vote
2answers
772 views

Snapshotting Database Tables

I have a reporting database (in SQL Server) that takes nightly snapshots of the production data (Oracle) for users to query against for the next day, but now the requirement is getting to be that they ...
0
votes
1answer
143 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
2
votes
2answers
720 views

Creating a partition on my soft-delete indicator column

In SQL Server 2008 R2 I am exploring the use of partitioning tables. We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL I want to focus ...
3
votes
0answers
396 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 ...
2
votes
1answer
825 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
3
votes
2answers
15k 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 ...
4
votes
1answer
464 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. ...