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

learn more… | top users | synonyms (1)

-2
votes
0answers
29 views

Whats is the maximum size a relational database can handle before considering switching to a NoSQL database [on hold]

Whats is the maximum size a relational database can handle before considering switching to a NoSQL database? How much oracle with partition can store? What should I consider in my analysis?
2
votes
2answers
38 views

Benefits of table partitioning on same disk

Maybe is stupid question, but I must ask to be clear with that. I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks. What I need ...
0
votes
1answer
249 views

Oracle 11g r2: Cannot create partition on timestamp field

I have data with a timestamp field of the form '2014-01-01 00:00:00.99' in one of the columns. I want to create a new partition for the table every month. For this I did something like this. CREATE ...
1
vote
2answers
42 views

Compressing partition - what about index?

To save space on disk I want to compress some partitions of my tables: alter table TEST_TAB move partition TEST_TAB_2014 compress for all operations; I have also indexes on this partition. Should I ...
1
vote
2answers
386 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
0answers
8 views

Delete datafile from parttioned table

I'm used SQL Server 2014. My big table partitioned by date column and each partition has own datafile. I want delete oldest partition data and data file too. How to do it?
0
votes
0answers
15 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
0
votes
0answers
8 views

Moving Replicated & Partitioned SQL2005 Enterprise to SQL 2008R2 Enterprise

Our SQL2005 Enterprise has 4 databases which are all partitioned, each have 5 files and a handful of tables replicated, compatibility mode is 90. They are on an old Windows Server 2003 32bit machine ...
0
votes
1answer
40 views

Maximum Partitioning PostgreSQL

I have 400,000 records I need to partition. The records will increase 100,000 per year and those new records might need to have 1000 new partitions added. Here are some of my concerns: How many ...
2
votes
1answer
52 views

Why do we need partitions in PostgresSQL? (or any other RDBMS)

So, I am trying to teach myself partitions in PostgreSQL. I understand that a database can become slow when a table hits millions+ of lines and indexes don't fit into memory anymore. One thing that ...
2
votes
1answer
738 views

PostgreSQL partitioning query performance inexplicably bad

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap: evlampts=# explain select max(timestamp) from only mcdata; ...
0
votes
1answer
26 views

Partition Function Question - DatetimeOffset

I am planning to partition a large table on [datetimeoffset]. I thought the data in that column were all yyyy-mm-dd hh:mm:ss -08:00, but there are multiple offsets in that column such as -07:00 and ...
0
votes
1answer
223 views

Is searching an alternate index on a partitioned myISAM table concurrent or serial?

I have a myISAM table with 30 partitions. The primary key is a SHA1 hash value, which is also used in the partition selection function. I also have an alternate key - an autoincrement integer. Since ...
0
votes
2answers
35 views

Counting unique records across 2 columns and computing a score based on boolean values

I have a table that looks like this in Google Big Query: custID InteractionDate Purchased? Sales 1 20150312 F 0 1 20150312 T 200 1 20150314 ...
0
votes
1answer
25 views

MySQL partitioning table by date and hour

I have a table with a timestamp field. This table will have millions of rows every day so the idea is to create partitions by date and hour. Every day I will delete partitions older than 10 days and ...
0
votes
0answers
10 views

PostgreSQL analyze only parent table only

I am moving data from a few billion records table to newly created partitions. I am inserting into the child table directly and deleting from the main table with filters on the appropriate columns in ...
3
votes
0answers
61 views

Why is my SQL Server query behaving differently on UPDATE than on SELECT?

I've written a SQL Server query that updates records to have a sequential number after partitioning on a field. When I run it as a SELECT statement, everything looks great: DECLARE @RunDetailID INT = ...
1
vote
2answers
122 views

SQL Server Standard edition attach partitioned database

I have a little problem. I'm using a SQL Server database version 11. I have to change database edition from enterprise to standard. I overlooked fact that standard edition doesn't have partitioned ...
3
votes
2answers
766 views

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

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with. My project is about collecting data of ...
1
vote
2answers
65 views

Maintain Up-To-Date Copy of Very Large Table if Clustering/Partitioning/Moving Operation Needs to be Killed/Rollback

I've appended the following information in a previous post but I asked the 'why' instead of the 'how'. So here's the question... SQL Server 2014, CU6. 4.5 TB database, Simple recovery. The most ...
0
votes
1answer
135 views

How to split a large table in Teradata

I'm trying to load data from a very large table (200 million rows) into a Presentation Datamart (PDM) for an Enterprise Data Warehouse using Teradata 14.10. I want to split this large table into ...
9
votes
2answers
15k 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 ...
0
votes
1answer
634 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 ...
14
votes
4answers
2k views

effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

I am looking for advice on table/index design for the following situation: I have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
0
votes
1answer
116 views

Add partition works but not the drop partition

We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any ...
0
votes
1answer
30 views

Multiple records and determine if a combination if values exist and write this to a new column

I'm struggling with a problem. An order can have multiple lines and accordingly a type (F=Footwear/N=Non-FTW,Mixed=Both) I need to determine if the order consists of which type and create a new ...
3
votes
0answers
49 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
0
votes
1answer
39 views

How to keep archive table accessible for some users which is not frequently accessible?

I have partitioned my table based on some boundary ranges. My Database is built at MSSQL server 2008 R2. I have done partitioning for a table which has 40 million records in it. Now it's time to ...
1
vote
1answer
17 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
0answers
52 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
0answers
44 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 ...
0
votes
0answers
22 views

Import non-partitioned table into an existing range partitioned table - ORACLE

I have export non-partitioned table1 on disk with some records, then I create same new table2 in my schema (different name) but with range partitioning, I'm talking about Oracle database. So my ...
0
votes
0answers
36 views

Partitioning large table requires lots of extra disk space: does switching recovery model help?

We are in the process of implementing partitioning on a SQL Server 2012 database. It worked fine in our environment, but in the client environment we ran out of disk space. The entire database was ...
11
votes
1answer
5k views

Index optimization with dates

I have a large table of objects (15M+ row) in PostgreSQL 9.0.8, for which I want to query for outdated field. I want to divide the query by millions, for scalability & concurrency purposes, and I ...
1
vote
1answer
64 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 ...
0
votes
1answer
40 views

How to remove the duplicate records from primary filegroup after partitioning?

Basically, our database is created based on primary filegroup alone. Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which ...
0
votes
0answers
34 views

SQL Server Partitioning - parent/child tables reference partitioning

I have a requirement to partition quick growing tables in our product and below are the table information with key details. PERIODIC_EVENTS (PARENT TABLE which has ID (sequence no) as Primary KEY and ...
0
votes
1answer
67 views

MySQL Create Table with Partition Slow on server machine

Following create table statement with Partition runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz ...
1
vote
1answer
32 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 ...
1
vote
1answer
56 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
57 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 ...
0
votes
0answers
26 views

How to determine is a value exists in a column within a partition

I have a set of data where I need to look at partitioned data and determine if a value exists within that partition. Example data: AccountNumber Code Status Credited ...
1
vote
0answers
25 views

Using the partition function in the Where clause

I've been asked to test out performance of a database (a test system) after implementing horizontal partitioning. The database is on SQL Server 2008 r2. I'm inheriting this from someone else and ...
3
votes
1answer
61 views

(How) does table inheritance interfere with locking in postgres?

I have a table inheritance setup that might be simplified like this: CREATE TABLE p ( id BIGSERIAL PRIMARY KEY, type_id BIGINT, approved BOOLEAN ); CREATE INDEX ON p(approved); CREATE ...
3
votes
3answers
449 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 ...
1
vote
1answer
44 views

SQL Server Database Table Partitioning Consideration

I have a SQL Server table with 42 million + records. This table acts as a staging area for transaction records loaded from a different system. Data from this staging table have to be transformed and ...
1
vote
1answer
65 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
0answers
54 views

Does partitioning help if you don't query on the partition value?

Say I have an orders.Order table that is partitioned on CreatedWhen (a datetime column set to GetDate() when the row is created). The table has over 10 million rows and the partition is quarterly ...
1
vote
1answer
127 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 ...
2
votes
2answers
432 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 ...