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

learn more… | top users | synonyms (1)

2
votes
1answer
941 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
2
votes
3answers
578 views

Performance issues moving 1 million rows between tables

I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this: delete from table1 OUTPUT *.delete into table2 I am executing this job every hour, but it ...
2
votes
2answers
1k views

SQL Partioning on date, how does it work for future records?

I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
2
votes
2answers
2k 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
2k 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
5k 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 SCHEMA....
2
votes
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: ...
2
votes
2answers
351 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
263 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
737 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 ...
2
votes
2answers
475 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
1k views

How to partition a very large table with limited db space?

I have a very big table. I want to partition it, but I cannot. Database size: 1TB, Free space 200GB Table: Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data. ...
2
votes
1answer
214 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
1answer
1k views

Triggers not being run on a partitioned table

I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
2
votes
1answer
28 views

How do I add values to partition function and scheme?

We have a partition function defined as CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int) AS RANGE LEFT FOR VALUES (20101228, ... , 20161227) where each date is the first date of our ...
2
votes
1answer
49 views

Returning all rows from multiple partitions of a partitioned table

I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like ...
2
votes
1answer
56 views

Partition scheme filegroup metadata

When you create a partition scheme against a particular partition function, you specify a series of filegroups. I cannot find the metadata store where this filegroup list can be found & queried. ...
2
votes
1answer
2k views

moving a Partition of Partitioned Table to other File Group, SQL Server

I created partitioned table on DateTime column. for last 6 month. I placed 3 oldest month on a file group named 'FG_OlderData', and 3 newest month on a file group named 'FG_NewerData'. Partition ...
2
votes
3answers
64 views

SQL Server uses only PRIMARY partition by default

My database has 2 partitions files: PRIMARY, DATABASE_ARCHIVED. A lot of tables have a column "archived". When archived is 1 row is in the DATABASE_ARCHIVED partition. When archived is 0 row is in the ...
2
votes
1answer
349 views

Shrinking old partitions

I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have ...
2
votes
1answer
582 views

Does SQL Server have a reference partitioning equivalent?

Oracle 11g has introduced a new feature that is called Reference Partition. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table. http://www....
2
votes
2answers
2k views

Best option to back up mysql innodb partition

We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link http://www....
2
votes
1answer
11k views

After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail

I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
2answers
1k 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 ...
2
votes
1answer
65 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...
2
votes
1answer
84 views

Difference between clustered index and partitioning column

As I understand partitioning, the column that you partition on is how things are divided into partitions. But does the partitioning column have anything to do with order on the disk? (Aside from ...
2
votes
2answers
2k views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
2
votes
2answers
83 views

Benefits of table partitioning on same disk

Maybe is stupid question, but I must ask to be clear with that. I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks. What I need ...
2
votes
1answer
182 views

Why do we need partitions in PostgresSQL? (or any other RDBMS)

So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore. One thing that ...
2
votes
1answer
423 views

Script Table Definition in Powershell

I'm writing a powershell to create a duplicate table(in another schema) for the purposes of partition switching*. I'm having problems adding the columns and the clustered column store index. Here's ...
2
votes
2answers
125 views

Make sure partitioning is working

I created a partition for a table. There is a column "archived" (tinyint). 0 goes to PRIMARY data file, 1 to ARCHIVED data file. ALTER DATABASE MYDB ADD FILEGROUP [ARCHIVED]; ALTER DATABASE MYDB ADD ...
2
votes
1answer
194 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my [...
2
votes
1answer
132 views

How does subpartitioning actually work and what physical files are created?

I can't understand how the subpartitioning works in mysql. I am reading the following example: create table invoices ( customer_id int not null, product_id int not null, amount decimal (8,...
2
votes
1answer
29 views

Obtaining the next partition number

For a partitioned table, how do I determine via T-SQL what is the next partition number that will be used? For example, the switch-in today went into partition number 50. I want to build the new ...
2
votes
3answers
7k 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 ...
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 (...
2
votes
2answers
187 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. ...
2
votes
1answer
853 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 ...
2
votes
1answer
1k views

How can I find out which tables use reference partitioning from the Oracle data dictionary?

I'm writing a generic drop-all-objects script for our Oracle databases. It generates drop table TABLE_NAME cascade constraints purge lines for all tables, amongst other object types, by looping ...
2
votes
2answers
774 views

How to do Partition for MySQL 100GB table in zero downtime?

I have a Innodb engine table with 100GB,I have a planned to do partition the table.Can anyone tell me how to do it in zero downtime? In 100GB table the records are increasing for each and every ...
2
votes
1answer
559 views

Does partitioning a MySQL table also increase index creation performance?

This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time. Because partitions are bluntly put ...
2
votes
1answer
653 views

InnoDB, Hash Table, Reference Table and Fragmentation

I have a big list of domains and URLs that I store in a database, there are around 150M domains and 300M URLs, I'm using InnoDB to store each in the format: CREATE TABLE IF NOT EXISTS `domains_list` (...
2
votes
3answers
5k views

How do I remove partitioning?

There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code: alter table my_table remove ...
2
votes
1answer
8k views

Rename interval partitioning system generated partition names

I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition. PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY')) (PARTITION "p1_1" ...
2
votes
1answer
360 views

Unique Non Clustered Column in Partitioned table

We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a non-...
2
votes
2answers
684 views

Investigating/Troubleshooting I/O Spikes

We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server. The spikes seem to fall at an exact time after the hour, each hour. We have ...
2
votes
3answers
2k views

db2 range partitioning attach and detach issue

We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want. We had to retrieve ...
2
votes
1answer
1k views

PostgreSQL partitioning query performance inexplicably bad

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap: evlampts=# explain select max(timestamp) from only mcdata; ...
2
votes
1answer
333 views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...
2
votes
1answer
96 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...