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

learn more… | top users | synonyms (1)

3
votes
3answers
2k views

Best practices for configuring the Drive/Filegroup setup for Microsoft SQL 2008

I am a software developer for a small company. I wish we had a dba but not in the cards at this point for this company. I was working on migrating our 75gb SQL Server 2000 database to SQL Server 2008 ...
2
votes
2answers
585 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
193 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 ...
0
votes
1answer
175 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 ...
2
votes
2answers
169 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. ...
0
votes
1answer
71 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 ...
8
votes
1answer
432 views

Storing and querying rolling data in PostgreSQL

I have a large quantity of weather model data being put into a PostgreSQL database. The machine has 8 cores and 16 GB of RAM. I'm running PostgreSQL 9.3 with PostGIS 2.1. Each table will have a ...
5
votes
2answers
1k 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 ...
6
votes
1answer
1k 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 ...
1
vote
1answer
381 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
3k views

Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
1
vote
2answers
541 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 ...
0
votes
1answer
104 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: ...
2
votes
2answers
602 views

Creating a partition on my soft-delete indicator column

In SQL Server 2008 R2 I am exploring the use of partitioning tables. We have a soft-delete scheme which is sometimes an IsDeleted bit NOT NULL and sometimes a DateDeleted date NULL I want to focus ...
3
votes
0answers
333 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 ...
2
votes
1answer
647 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
9k 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 ...
4
votes
1answer
376 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
2answers
441 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 ...
3
votes
4answers
569 views

Is database partitioning for performance reasons meaningful in a virtualized environment?

When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over ...
0
votes
1answer
890 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 ...
0
votes
0answers
80 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). ...
0
votes
3answers
262 views

The column order of composite clustered primary key on a partitioned table? [closed]

I have the following table. create table T (K1 date, K2 int, ....) on partitionScheme(K1) The table will be partitioned by K1 (K1 has low selective. the data will be appended in the order by K1). ...
1
vote
1answer
87 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
108 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 ...
4
votes
1answer
2k 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 ...
3
votes
3answers
4k 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 ...
2
votes
1answer
980 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
223 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
181 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 ...
2
votes
2answers
541 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 ...
1
vote
1answer
158 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 ...
3
votes
2answers
2k views

Partitioning a Table Referenced by a Foreign Key

I have one table: CREATE TABLE [dbo].[entry] ( [ID] [bigint] IDENTITY(1,1) NOT NULL, [EntryDate] [datetime] NOT NULL, [createddate] [datetime] NOT NULL, CONSTRAINT [PK_Entry_ID] ...
1
vote
1answer
95 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 ...
2
votes
0answers
196 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
301 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
671 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
24 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
777 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 ...
4
votes
1answer
4k 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 ...
10
votes
2answers
20k 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
771 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 ...
3
votes
1answer
174 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; ...
1
vote
1answer
484 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 ...
3
votes
1answer
318 views

What's the optimal server configuration for fastest inserting in a MySql database in this scenario?

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating ...
2
votes
2answers
345 views

Partitioning a multi-terabyte database [closed]

We have a database which is nearly 3TB in size, out of which 8 tables together are 1.5TB which we want to partition based on year. But as we will be partitioning over a terabyte of data, this will be ...
2
votes
1answer
467 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
427 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 ...
1
vote
1answer
209 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 ...