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

learn more… | top users | synonyms (1)

2
votes
2answers
355 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 ...
0
votes
1answer
123 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
2
votes
1answer
146 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 ?
0
votes
1answer
82 views

Does Oracle Partition by Reference optimizes join queries

Oracle 11g has a new feature in partitioning tables called partition by reference. I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
0
votes
1answer
59 views

Proper drive partitioning in Linux for Oracle

I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions. Can ...
4
votes
2answers
298 views

Strategy to deal with multi billion row simulation results and partitioning

I am trying to come up with a good way to deal withthe results of statistical simulations from a dba point of view. We generate about 500 million rows per day, most of which are "garbage" (i.e. the ...
6
votes
3answers
20k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?
0
votes
1answer
476 views

Bulk loading to partitioned table

I am designing a database application which will be using PostgreSQL 9.3. My question is regarding bulk loading a CSV to a partitioned table. I have done a lot of research, but I'm still unsure of ...
5
votes
2answers
640 views

Oracle partition by interval using persian calendar

Oracle supports Persian (Jalali) calendar in DDL queries, I can easily say: select to_char(register_date, 'YYYY-MM-DD', 'nls_calendar=persian') from my_table; I created a table as: create ...
4
votes
2answers
977 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. ...
3
votes
1answer
1k views

How to structure Master/Detail tables for performance without denormalizing?

Some Background Info We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails. There are roughly 80k Transactions a day which translates to ...
3
votes
2answers
2k views

ALTER TABLE SWITCH statement failed

ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables... Does anyone knows how to fix that on already existing table full of data? Well, I've ...
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', ...
6
votes
1answer
741 views

How accurate is the sys.partition.rows column?

The system view sys.partitions has a column "rows" that is the total number of rows in a given partition. For a table that is not partitioned (or only has one partition depending on how you look at ...
2
votes
1answer
4k 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" ...
1
vote
1answer
218 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 ...
0
votes
1answer
78 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
0
votes
1answer
420 views

MySQL Read Speed and Partitioning on Separate Drives

Introduction Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
3
votes
0answers
221 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
4
votes
1answer
278 views

Large difference in disk space used by partitions

I have a partitioned table that contains about 155 million records total. It's partitioned by year and the partitions for both the current and previous years contain about 25 million records each. ...
5
votes
0answers
251 views

Should I group by and then join, or join and then group by?

(oracle's optimizer does the same thing for both approaches when just two tables are involved, thus my question involves three tables) Assume three tables t1, t2 and t3 with a pairwise 1:n ...
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 ...
2
votes
1answer
314 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 ...
10
votes
4answers
3k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing ...
2
votes
1answer
294 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 ...
0
votes
0answers
74 views

MySQL: Optimizing for large but discrete data sets

In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). ...
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 ...
0
votes
0answers
70 views

How do I debug an Oracle Linux Diskspace Issue?

I have about 429GB Diskspace allocated to this VM. I see that only 179GB is being used. Now when I try to add a 128GB file I get an I/O error saying I do not have any space left. I need to be able ...
11
votes
3answers
3k views

Can I move rows between partitions by updating the partition key?

I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this. The question: Can you move rows of data within a partitioned table from ...
2
votes
3answers
2k views

How to partition a table by timestamp, where data from every i'th day goes to partition i?

I'm looking into partitioning a table in my InnoDB database. I have a column corresponding to a UTC timestamp, and I want to partition around that. The basic idea that I want to capture is a ...
3
votes
1answer
1k views

partitioning by datetime vs date vs int performance

I am trying to determine which partitioning strategy will be most efficient for my queries on SQL Server. We know we want daily partitions. The data has a natural DATETIME field in it, so my gut ...
2
votes
1answer
162 views

MySQL table architecture

Background information: I have a table containing upload information, every day I get more and more uploads but most of the queries I run center around information gathered within the last 12 months; ...
2
votes
1answer
543 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 ...
0
votes
1answer
169 views

Partitioning table with billion row

I have a big table referenced by other tables and need to split the table on several parts. I create table by CREATE TABLE product_part0 () INHERITS (product); Move some rows to product_part0 by ...
0
votes
1answer
127 views

In partition definition filegroup name appears twice, what does that mean?

short question regarding partitions, consider the following partition definition: CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS PARTITION [MSAppServerPF_ASWcfEventsTable] TO ...
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
0answers
404 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
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 ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...
0
votes
1answer
202 views

Deciding the best way to partition (Postgresql)

I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of ...
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 ...
0
votes
1answer
96 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
0
votes
1answer
460 views

Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operation

I am having a Database. The size of my database is near about 4GB(Only .mdf file). In that database only few tables present which have the maximum data of database. One of them and the biggest table ...
0
votes
2answers
1k views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
3
votes
1answer
2k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
8
votes
2answers
11k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
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
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 ...
2
votes
1answer
744 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 ...
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 ...