Tagged Questions

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

learn more… | top users | synonyms (1)

0
votes
3answers
179 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
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
68 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 ...
3
votes
1answer
991 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
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 ...
2
votes
1answer
529 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 ...
2
votes
1answer
292 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
118 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 ...
2
votes
2answers
1k 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
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
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
200 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
395 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 ...
0
votes
1answer
452 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 ...
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
10k 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
550 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 ...
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; ...
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 ...
3
votes
1answer
202 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
246 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
283 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
310 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
135 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
595 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 ...
4
votes
2answers
296 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 ...
3
votes
1answer
511 views

Will table partitioning speed up my queries?

My database is set up with what we call our staging tables and our live tables. Currently for we have about 2 million rows for a table called products. Our customers we call manufacturers load their ...
1
vote
2answers
489 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 ...
3
votes
1answer
1k views

How to take partitions or filegroups out of service

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows ...
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 ...
2
votes
1answer
569 views

How to partition a very large table with limited db space?

I have a very big table. I want to partition it, but I cannot. Database size: 1TB, Free space 200GB Table: Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data. ...
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
0
votes
1answer
104 views

Is there an advantage to moving to 'real' date partitions on MySQL 5.5?

We are using partitions per day for some our tables. We started with MySQL 5.1, so we had to partition using the to_days (end_time is a datetime column): ENGINE=InnoDB DEFAULT CHARSET=utf8 ...
2
votes
1answer
349 views

Does SQL Server have a reference partitioning equivalent?

Oracle 11g has introduced a new feature that is called Reference Partition. Using reference partitioning, a child table can inherit the partitioning characteristics from a parent table. ...
0
votes
0answers
77 views

Find the partition where data lives in MySQL

Is there a way to select the partition name where a specified record actually lives in? The idea is something like: SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition> I found ...
2
votes
0answers
85 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
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
0answers
170 views

When to split a large table

I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc). However, in my use-case, every single day, the table gets 2160000 new rows. It's also crucial that ...
4
votes
1answer
166 views

Table partitioning filegroup

Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
4
votes
2answers
1k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
1
vote
3answers
257 views

Improve performance by partitioning

I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process. ...
2
votes
1answer
742 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 ...
0
votes
1answer
483 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...
0
votes
2answers
449 views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
0
votes
1answer
417 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 ...