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

learn more… | top users | synonyms

2
votes
1answer
2k 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 ...
1
vote
0answers
136 views

How to make Greenplum 4.2.3 only scan the intended partition?

When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...
2
votes
1answer
174 views

How do I know if my database partitioning is done well? [closed]

I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts ...
1
vote
3answers
4k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
3
votes
2answers
841 views

Partitioning on a single filegroup

I have some very large tables in my database, but a substantial chunk of this data is "old". Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
1
vote
1answer
2k views

conversion of high_value to date datatype

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what ...
2
votes
1answer
171 views

MySQL partitioned tables?

I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
0
votes
0answers
156 views

Renaming the system generated partitions during interval partitioning

I am trying to rename the system genrated partition names(generated during interval partitioning). I try to do this while my table is live(data read and write is happening). I need two scripts. I ...
0
votes
2answers
156 views

MySQL Cluster - partitions on disk

I have question about MySQL Cluster: Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks) ...
0
votes
0answers
115 views

XML errors while copying dependents during a redefinition to an interval partitioning scheme

I am partitioning an existing table. I used the following process: Checks DBMS_REDEFINITION.CAN_REDEF_TABLE(...); Successful Create duplicate table CREATE TABLE "USER"."MYTABLE_ONLINE" ( ...
3
votes
1answer
1k views

Setting up a Postgresql Partition with foreign key

I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed. I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them. ...
2
votes
1answer
3k 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" ...
0
votes
1answer
384 views

Postgres 9.1 query plan not using check_constraints on partitions after large data load

I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
1
vote
1answer
951 views

Indexing issues while partitioning with dbms_redefinition

I get an error while doing the redefinition. Following are the steps which I have taken so far: Step 1. Start redefinition DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT', ...
2
votes
1answer
109 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 ...
1
vote
2answers
850 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
130 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 ?
3
votes
3answers
833 views

Is it a good practice to apply Vertical Partition to a newly created model?

Suppose you are modeling the ER model for a new Object Oriented Web Application. There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
0
votes
1answer
183 views

Where is my old partition in mysql show create table?

I am keep adding daily base partition for a table as e.g. ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) ( PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')), PARTITION ...
0
votes
2answers
195 views

Best Way to Partition By City / State in MySQL 5.5

I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so: ALTER TABLE ...
2
votes
0answers
217 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
1
vote
0answers
120 views

MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)

I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
4
votes
1answer
298 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 ...
12
votes
1answer
2k 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 ...
2
votes
1answer
4k 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 ...
1
vote
2answers
1k views

Best way to organize compound indexes on partitioned table (date by range)?

I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
0
votes
0answers
50 views

How to check on my partitions?

I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
2
votes
1answer
197 views

Where are partitions defined in mysql

I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data. How do I go from new disks (lets call them ...
5
votes
1answer
508 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 ...
1
vote
1answer
164 views

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
3
votes
3answers
454 views

High insert rate on MySQL

I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
3
votes
1answer
3k 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
231 views

SELECT multiple sensor values in one query

Background I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
4
votes
1answer
1k 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 ...
8
votes
1answer
1k 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
698 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 ...
2
votes
1answer
384 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 ...
1
vote
1answer
83 views

Partitions and Indexes, in which cases should be used

I know some knowledges: Partitions used to achieve better performance (like described in this article) And indexes used to increase performance in selection operations. And as result my question: ...
4
votes
1answer
552 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 ...
0
votes
1answer
1k views

Partitioning not working need primary key?

We are trying some partitioning as below. ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) ( PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), PARTITION p2 VALUES LESS THAN ...
1
vote
0answers
52 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
4
votes
1answer
202 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 ...
0
votes
1answer
350 views

Partitioning in SQL Server 2008

I want to partition some of our tables in a SQL Server 2008 database. The partition key will be date based. I have several questions: Is there a rule of thumb regarding the recommended number of ...
1
vote
2answers
567 views

How to check if there is partitions in use in fact tables?

I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?
2
votes
1answer
291 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
2
votes
2answers
357 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 ...
4
votes
2answers
735 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. ...
5
votes
1answer
1k 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 ...
1
vote
0answers
59 views

MySQL Partioning: 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 ...
1
vote
1answer
1k views

SSIS: “The buffer manager cannot write 8 bytes to file”

We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...