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

learn more… | top users | synonyms (1)

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: ...
1
vote
1answer
271 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 ...
1
vote
1answer
797 views

Partitioning vs Indexes

I'm very new to databases, so I apologize if my question is very basic... Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in ...
1
vote
1answer
2k 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 ...
1
vote
1answer
655 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 ...
1
vote
1answer
38 views

Best practices for selecting partition boundary ranges to maintain empty partitions at both end?

I have partitioned the table based on a date datatype partitioning column. The data for the column starts at "2014-01-01" and ends at "2015-01-01". I know that, the partitions should have empty ...
1
vote
1answer
96 views

table partition in order to solve frequent deadlock issues - how would I know if it will work?

I use sql server 2005 enterprise edition and have a subscriber DB - part of a transactional replication - that is used for Business Intelligence purposes - they run reports out of it, and SSIS ...
1
vote
1answer
175 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
2k views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
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 ...
1
vote
2answers
1k 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?
1
vote
4answers
741 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 ...
1
vote
1answer
55 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 ...
1
vote
2answers
165 views

Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)

Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries. Our database version is oracle 11g ...
1
vote
1answer
46 views

Getting rid of partitioning in Postgresql 9

it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer. What's the ...
1
vote
2answers
85 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
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 ...
1
vote
1answer
613 views

How to partition an Oracle table with 65 million rows?

The table that needs partitioning contains a list of Components and their Values. So one table is tblComponents. Another is tblFields. Then this table, tblValues, contains the values of the ...
1
vote
1answer
3k 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 ...
1
vote
1answer
1k views

MySQL, “columns partitioning” when multiple columns are individiually important

MySQL 5.5 introduces "columns partitioning". http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html I'm trying to better understand how it works when two columns are important individually. ...
1
vote
1answer
94 views

Where does it make sense to store Indexes for partitioned tables?

Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
1
vote
1answer
130 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?
1
vote
1answer
16 views

mysql RANGE partitioning - how do I find partition boundaries?

I am implementing partitioning support for mysql in eludia engine I need to compare partitioning description in model with actual database contents and alter table, if necessary. To extract all ...
1
vote
1answer
33 views

What's the best way to structure logging application data in MySQL

I may be overthinking this but: I have an API; I want to log calls to the API for stat/debugging purposes. Ideally the speed of reads should be quick but is not essential (as will primarily be read ...
1
vote
1answer
61 views

How to migrate data of to another sql server

Good day, I have a database running on a virtual machine hosted at our ISP. It is a fairly large database which has about 5GB of data inserted into it every day (network logs). It is partitioned ...
1
vote
2answers
99 views

SQL Server 2012 Table Partitioning - Have I done it correctly?

I have a table that stores accounting transactions for what we call "jobs". The table looks like this: ( REGION char(3) JobNumber char(17) SeqNum decimal(5,0) ..... ..... ..... AcctYear decimal(4,0) ...
1
vote
1answer
30 views

How to efficiently swap a portion of a large table's data?

I run an application (price comparison website) where I need to update a pricing table with dozens of millions of rows every day. Basically, every product feed I download from a merchant results in ...
1
vote
1answer
150 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 ...
1
vote
1answer
53 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 ...
1
vote
1answer
261 views

MySQL 5.6.17 running out of memory when opening table with many partitions

We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB ...
1
vote
2answers
2k 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 ...
1
vote
1answer
70 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
vote
2answers
6k 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 ...
1
vote
1answer
133 views

Partition Function with only one value, what does this effect?

I am currently looking at a database that has been setup by the default installation routine of AppFabric. Looking at the creation scripts I encountered this partition function: CREATE PARTITION ...
1
vote
1answer
88 views

What is a “leaf expression”?

What is a "leaf expression"? Partition-clause expressions are limited in the following ways: Your partition-clause must calculate a single non-null value for each row. You can reference multiple ...
1
vote
1answer
444 views

MS SQL Server 2005 DB backup with partitioned tables to non-partitioned SQL Server 2012

I have a database with several partitioned tables on MS SQL Server 2005 Enterprise, and I have to restore this DB on MS SQL Server 2012 Standard. Since Standard doesn't support partitioning - it's a ...
1
vote
1answer
160 views

Monitor Oracle query plans for PARTITION LIST (ALL)

Is there any way I can monitor all the queries running on an Oracle instance and report which queries are using PARTITION LIST (ALL)? What I'm essentially asking for is a way to automatically ...
1
vote
1answer
203 views

Can a partitioned table span on multiple files across different file groups?

In SQL Server 2008 and above, if a table is partitioned, can it span across multiple files across multiple filegroups? Thanks
1
vote
1answer
121 views

Key lookup partition pruning

I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each: SELECT A.COL1, B.COL2, C.COL3 FROM A INNER JOIN B ON A.ID = B.ID INNER JOIN C ON A.ID = C.ID ...
1
vote
2answers
2k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
1
vote
1answer
142 views

Archive partition before delete it?

I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use ...
1
vote
1answer
2k 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', ...
1
vote
1answer
190 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 ...
1
vote
1answer
2k views

PostgreSQL 9.1 - Query on VIEWS taking a lot of time

Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation: We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
1
vote
1answer
378 views

What is the “Data partitioned, Shared-Nothing, Parallel Database” solution for PostgreSQL ? [closed]

I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read ...
1
vote
1answer
619 views

How many files should I create for one filegroup and 9 weekly partitions?

I have 9 weekly partitions on one filegroup. How many files do I need to create? There are 3 tables containing 46 million, 16 million and 10 million rows.
1
vote
2answers
538 views

Create multiple files for each file group for table partitioning?

I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO ( [PRIMARY], [fg2], [fg3], [fg4], ...
1
vote
0answers
20 views

Postgresql: Partition table by hour

I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
1
vote
0answers
36 views

Fast restore of dropped partitions

I want to test the restore of errorneously dropped partitions of a subpartitioned tables. This restore would consist of three steps: restore the database (or at least the necessary tablepsaces) to ...
1
vote
1answer
28 views

Mysql Partitions with Partition limit

I have a partition table on basis of Primary key having 1.5 Billion rows in it. But when i check ibd files only 80 ibd show in GB's whereas 120ibd shows 1MB file. I would like to know how does the ...