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

learn more… | top users | synonyms (1)

2
votes
1answer
202 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
38 views

Returning all rows from multiple partitions of a partitioned table

I have a table that is partitioned into multiple filegroups and spread out over multiple tables b y month. My partition scheme is using a date column. What I would like to do is something like the ...
2
votes
1answer
42 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
945 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
57 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
262 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
2answers
804 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 ...
2
votes
1answer
543 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. ...
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 ...
2
votes
1answer
10k 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
51 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
993 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
70 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
141 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
352 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
173 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
118 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 ...
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
5k 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
1answer
279 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 ...
2
votes
2answers
180 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
779 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
2answers
13k 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 ...
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
705 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
507 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
571 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
1answer
7k 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
321 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 ...
2
votes
2answers
633 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
150 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
65 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 ...
2
votes
1answer
267 views

MariaDB 10 InnoDB Partition with Data Directory

mysql --version mysql Ver 15.1 Distrib 10.0.20-MariaDB, for Linux (x86_64) using readline 5.1 I have a MariaDB 10 installed, though I tried with the 5.5 release in the CentOS repos. I'm ...
2
votes
1answer
160 views

Does it makes sense to partition on a foreign key?

I have a MySQL table which looks a bit like this: ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed) Selects should go very fast, updates almost never happens, inserts ...
2
votes
1answer
203 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz ...
2
votes
1answer
240 views

Is there a difference between RANGE and LIST method for partitioning by consecutive integer values?

I have a table with a year column that has consecutive values -- 2000, 2001, and so on. It's a read-heavy MyISAM table, with 25+ million rows for every value of year and a comparable set inserted in ...
2
votes
1answer
117 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
1answer
1k views

Why is MySQL order by performance poor within a single partition of a partitioned table?

I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
2
votes
1answer
226 views

Availability Groups and Partition Swapping

Is it possible to use SQL 2012 Availability Groups over two machines and perform partition swapping on the primary machine ?
2
votes
1answer
554 views

Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard

I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise ...
2
votes
0answers
32 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
0answers
34 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
2
votes
0answers
41 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
2
votes
1answer
73 views

Deciding what columns to use for partitioning

I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up ...