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

learn more… | top users | synonyms (1)

2
votes
2answers
27 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 ...
1
vote
2answers
36 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 ...
0
votes
0answers
7 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
7 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
37 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
51 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 ...
0
votes
1answer
25 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
2answers
33 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
23 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
9 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 ...
1
vote
2answers
119 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
0answers
60 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
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
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
48 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
38 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
42 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
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 ...
0
votes
0answers
21 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 ...
1
vote
0answers
50 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 ...
0
votes
0answers
31 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
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
1answer
66 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
53 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
24 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
59 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 ...
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
125 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
1answer
98 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
0
votes
0answers
26 views

Creating separate small table from big based on access frequency for “select” performance

I have table with several millions of rows which are selected a lot (many times in a second in average). It is indexed and everything is ok for now. But performance is always a question (sometimes ...
1
vote
1answer
63 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 ...
3
votes
1answer
88 views

How do I create a partition using a bit field in SQL Server 2014

I have a table where data is archived. I need to keep this data as it is rarely viewed. Instead of moving archived data to a separate table, I wanted to create a partition on the Archived field. When ...
1
vote
2answers
103 views

SQL Server 2012 Table Partitioning - Have I done it correctly?

I have a table that stores accounting transactions for what we call "jobs". The table looks like this: ( REGION char(3) JobNumber char(17) SeqNum decimal(5,0) ..... ..... ..... AcctYear decimal(4,0) ...
1
vote
2answers
246 views

Will enabling row movements on list-partitioned table cause performance problems(oracle 11g r2)

Our incident management system has run for years, and the large quantity of closed incident tickets makes the table rather huge and slows down the search queries. Our database version is oracle 11g ...
0
votes
1answer
127 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 ...
1
vote
1answer
71 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 ...
0
votes
1answer
65 views

schema for mysql database with 30million rows

We am building a SaaS backend for restaurants. We integrate directly with POS, so each POS keeps sending customer orders that we store in a following table in mysql. CREATE TABLE name ( `id` ...
1
vote
1answer
43 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
30 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 ...
2
votes
1answer
175 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
0answers
69 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 ...
0
votes
0answers
98 views

How to partition a MySQL table by Week using a timestamp column?

I have the following MySQL 5.6 table, which uses InnoDB and contains a timestamp column (I've omitted all the extra columns, in order to simplify my question): create table partition_by_week ( id ...
0
votes
3answers
105 views

Active/current (1-5%) and historical records in same table: index or partitioning?

I am trying to discover the correct architecture for the following situation. An application stores 'source data' in multiple tables. Each of these 'source data' tables contains actual, current, ...
0
votes
0answers
43 views

High CPU Usage for Mysql after adding partitions

On an amazon m3.large instance (vCPU:2, Mem: 7.5), I run a mysql(5.5.29) server. Initially, the state of the server is OK except that the query is too slow. However, after partitioning the table ...