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

learn more… | top users | synonyms (1)

1
vote
2answers
944 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
657 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
39 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
74 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
21 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
2answers
304 views

Is database partitioning for performance reasons meaningful in virtualized environment? [closed]

When running SQL Server on real hardware partitioning database and placing different partitions with different tables on different hard drives can be beneficial for query performance. Or, to simplify ...
1
vote
1answer
558 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
91 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
129 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
23 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
52 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
40 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
119 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
816 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
67 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
1answer
119 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
84 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
418 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
136 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
163 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
109 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
1k 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
126 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
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

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
179 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
355 views

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

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
604 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
508 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
1answer
25 views

Is there any way to use “like” operator during oracle table partitioning

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me. In ...
1
vote
0answers
27 views

MySQL table partition strange behavior (slow query suddenly)

(I asked this question in "stack overflow" and was recommended to ask in here). (MySQL version: 5.6.15) I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a ...
1
vote
2answers
176 views

Slow querys on billions-rows-table // index used

Since I'm a young developer and not realy skiled in using databases (PostgreSQL 9.3) i ran into some problems with a project, where i realy need help with. My project is about collecting data of ...
1
vote
1answer
58 views

Paritioning performance for full table scan on non-paritioned index

We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key. My question is: Can partitioning the table ...
1
vote
0answers
81 views

SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file ...
1
vote
1answer
28 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
vote
2answers
269 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 ...
1
vote
1answer
83 views

Mysql, sql server, oracle tables get too large [closed]

I hear a lot about things that, it is a lot cheaper to buy new harddisk than pay staff to come up with genius ideas how to save resources. But what I don't understand, is how these databases handle a ...
1
vote
0answers
137 views

Postgres - Partitioning old tables, partition query planning, optimisation

I've been trying to optimise our database, but not being a DBA and not having done a whole lot of tuning before I'm struggling a little with "too many options". I've already run pg_tune and bumped a ...
1
vote
0answers
405 views

Incorrect information in .frm file after a power outage?

CentOS release 6.3 (Final) x86_64 mysql Ver 14.14 Distrib 5.5.31, for Linux (x86_64) using readline 5.1 SHOW SLAVE STATUS\G: Slave_IO_Running: Yes Slave_SQL_Running: No ...
1
vote
0answers
22 views

How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an ...
1
vote
0answers
17 views

How to take partitions or filegroups out of service [migration] [duplicate]

I'm an experienced SQL Server DBA but new to partitioning, and a had a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows by ...
1
vote
3answers
2k 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 ...
1
vote
0answers
88 views

MySQL Partitions

I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance. We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
0answers
191 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 ...
1
vote
0answers
140 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 ...
1
vote
0answers
58 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 ...
1
vote
0answers
906 views

Pros/Cons of Using Table/Index Partitioning in SQL Server [closed]

We have a number of tables that were partitioned primarily to reduce problems when purging data (via sliding-window partitioning / truncate). I have found a number of caveats that come with ...
1
vote
2answers
99 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
1
vote
2answers
494 views

Difference between partitioning and fragmentation [closed]

I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results ...