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

learn more… | top users | synonyms (1)

2
votes
1answer
185 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
2answers
96 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
275 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 ...
1
vote
1answer
242 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 ...
1
vote
2answers
1k 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 ...
1
vote
2answers
108 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
91 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 ...
4
votes
1answer
724 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 ...
1
vote
1answer
56 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
95 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
582 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 ...
3
votes
0answers
352 views

Fast restore of dropped partitions

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps: restore the database (or at least the necessary tablespaces) to the ...
0
votes
1answer
66 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 ...
2
votes
1answer
238 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 ,...
2
votes
1answer
164 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 ...
3
votes
1answer
526 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
341 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 ...
1
vote
0answers
51 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
361 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
90 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
86 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
429 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 ...
3
votes
2answers
605 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
29 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 ...
2
votes
1answer
351 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 ...
4
votes
1answer
383 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
0answers
57 views

Best ratio for files and file groups for a highly partitioned table

I have a number of tables with exactly 1000 partitions. Each table can be up to 200G with 100M rows and cannot be compressed. Data in partitions distributed quite equally. The SQL Server has 8 cores....
3
votes
2answers
227 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
1k 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
860 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
331 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 ...
1
vote
1answer
121 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` int(11)...
1
vote
1answer
81 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
41 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
424 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
112 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
3answers
308 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
426 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
1
vote
1answer
204 views

Recreate partition in mysql

I have a table having datetime column partitioned by RANGE (DAYOFYEAR(datetime_col)) By this I want to drop oldest of 4th week partition data and recreate immediately for next year similar week ...
0
votes
1answer
954 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 ...
0
votes
0answers
63 views

Oracle estimate cardinality on partitioned table

How does Oracle estimate cardinality on a partitioned table if the query gets the list of partitions from a subquery or a join? For example, if I have a query like this select * from ...
1
vote
1answer
118 views

Getting rid of partitioning in Postgresql 9

it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer. What's the ...
0
votes
1answer
63 views

Help with optimising a sql query on two partitioned tables

I have a SQL query that I am running on two very large tables in SQL Server 2012 Enterprise The two tables are ACKs and Logs. Both are partitioned on their time by day column with the appropriate ...
2
votes
2answers
125 views

Make sure partitioning is working

I created a partition for a table. There is a column "archived" (tinyint). 0 goes to PRIMARY data file, 1 to ARCHIVED data file. ALTER DATABASE MYDB ADD FILEGROUP [ARCHIVED]; ALTER DATABASE MYDB ADD ...
6
votes
3answers
4k 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
0answers
54 views

SQL Server partitioning of many-to-many tables - triggers

Many to many tables usually have only foreign keys to related tables, like EmployeeId PositionId Is it good idea to add column only for partitioning and set its value with INSTEAD OF INSERT/UPDATE ...
0
votes
0answers
209 views

Performance degradation after partioning tables using DBMS_REDEFINITION package

We have created partitions on per-existing tables in my application using DBMS_REDEFINITION package. just for the sake of improving performance. Now , We are facing an issue in the performance itself....
2
votes
1answer
196 views

Adding Indexes To a Partitioned table in SQL Server 2012

I have recently converted our one large table to use table partitioning in SQL Server 2012. It's a table that stores logs based on a time. So I created a partition function to split the table on my [...
0
votes
1answer
1k views

Efficient way to DELETE whole partition's data in table partitioned by HASH(DAY(datetime))?

I have a MySQL InnoDB table PARTITIONed by HASH(DAY(dateTime)). I'd like to remove partitions holding data older than 10 days. CREATE TABLE `partitioned` ( `id` bigint(20) unsigned NOT NULL ...
7
votes
1answer
1k views

Switching Data In Fails with “allows values that are not allowed by check constraints or partition function on target table”

Given the following -- table ddl create table dbo.f_word( sentence_id int NULL, sentence_word_id int NULL, word_id int NULL, lemma_id int NULL, source_id int NULL, ...