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

learn more… | top users | synonyms (1)

0
votes
0answers
36 views

How to drop partitioned index in SQL Server 2005

I am unable to drop partitioned index due to partition scheme and partition function existence. Table has a Primary Key, a Clustered Index and non clustered indexes. I am running the following ...
1
vote
0answers
30 views

Table clustered-index and partitioning in innoDB

I have a table with 45 columns, 3 of which are relevant: table Transactions(tr_id : int primary key, client : int, tr_date : datetime); The inserts to the table are done client-by-client The ...
5
votes
2answers
158 views

Total billions of rows and daily millions of row insert: What database system suits best [closed]

We developed a system with very high volume of data but very simplified data structure. We had only cellX, cellY, timeStamp and value columns. Operations are: Insert 455+ thousand rows per day ...
0
votes
1answer
33 views

Partitioned view with dedicated “routing” column

I have an application (Microsoft Dynamics NAV to be exact) that splits up data for different companies in tables with the exact same structure. I can not change that behavior, so I built VIEWs that ...
1
vote
1answer
61 views

Partitioning a table on a column in another table

I have a fact table called FactAccount which has the following columns: AccountId (identity) AccountMonthId - FK referring to DimMonth AccountYear The DBA has done the partitioning on the ...
0
votes
2answers
45 views

MySQL : How do I partition based on Month of date

I have a table having id as primary key and date column. I want to partition the data.. I felt month would be the best option so that I have 12 partitions.. How do I achieve that ! I am using mysql ...
1
vote
1answer
110 views

Drop partition and rebuild global indexes “online”

I am using Oracle 11g. I have a requirement to drop a partition and rebuild global indexes. The query below does job well but BLOCKS all DML operations on the table until the indexes are rebuilt. ...
1
vote
0answers
21 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
3
votes
1answer
75 views

How to implement table purge with partition on SQL Server 2012?

I've read this question: How to remove a table partition But it's not what I want. I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, ...
18
votes
1answer
378 views

Statistics disappear after incremental update

We have a large partitioned SQL Server database utilizing incremental statistics. All of the indexes are partitioned aligned. When we try to rebuild a partition online by partition all of the ...
0
votes
0answers
16 views

The most-recent-record problem with partitioned tables

Today's variant on the most-recent-record problem involves a table partitioned on the timestamp field relevant to the most-recent-record selection, and efficiently joining the result of the ...
0
votes
0answers
38 views

Why dropping TokuDB partition so slow

I have defined a simple time series table partitioned by UNIX timestamp (into 32 days). I then loaded ~17K entries, which were distributed among 8 partitions. There were at most 2880 rows in any ...
3
votes
2answers
43 views

Dealing with partitioned tables in Postgresql

Due to historical reasons we used to use one really big table in our Postgresql database to store time-based series of measurements. After a long usage, as the table become 200+ GB in size, we decided ...
0
votes
0answers
23 views

Mysql partition - How to do list partitioning of a table that contains unique column?

I am doing mysql list partitioning. my table data is as below ---------------------------------------- id | unique_token | city | student_name | ---------------------------------------- 1 | xyz ...
1
vote
1answer
114 views

How can I add multiple partitions to a Greenplum table using dynamic SQL

I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE statement to add a partition for ...
1
vote
0answers
53 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
3
votes
2answers
274 views

Any way to speed up reorganize partition of a very large table in MySQL?

So we have this massive 1.5TB+ table with billions of records that has been neglected for the past few months and needs to have new partitions added. The partitions are based off a timestamp which are ...
0
votes
0answers
45 views

Oracle - compressed subpartitions in uncompressed table

I am working with Oracle 11.2. I have table which on subpartition level has in DBA_Tab_subPartitions column COMPRESSION set to ENABLED for all subpartitions. But on partition level in ...
3
votes
0answers
316 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 ...
2
votes
0answers
34 views

Is there impact on query execution as more partitions are added to a table?

We are looking to heavily use partitioned tables in our data warehouse environment. We are looking to take advantage of fast partition switching as well as partition elimination during analytical ...
2
votes
1answer
223 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 ...
19
votes
2answers
286 views

Is it possible to force the optimizer to eliminate irrelevant tables in this partitioned view?

I'm testing different architectures for large tables and one suggestion that I've seen is to use a partitioned view, whereby a large table is broken into a series of smaller, "partitioned" tables. 1, ...
2
votes
1answer
62 views

Split Mysql Table internally

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in ...
0
votes
1answer
96 views

Working on a blog creation website; should I be creating a new table for each blog, or keep everything on the same table?

So I'm working on this blog creation website as a side project. I don't actually expect it to become anything, but I want to make it as efficient as possible, even if just for practice. The idea is ...
12
votes
2answers
29k 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
418 views

Postgres Partition Table Trigger inserting duplicate records

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger CREATE OR REPLACE FUNCTION ...
2
votes
1answer
223 views

Does it makes sense to partition on a foreign key?

I have a MySQL table which looks a bit like this: ID (BIGINT, PK), OtherID (INT, FK, indexed), NegativeTimestamp (BIGINT, indexed) Selects should go very fast, updates almost never happens, inserts ...
0
votes
1answer
43 views

Mysql hash partition

In my system's database we have a table which stores 3 fields: id int, name(varchar) and description (mediumtext). The table is by far the biggest in our database and the queries on it are exclusively ...
0
votes
1answer
53 views

Package DBMS_REDEFINITION missing from Oracle 11g?

I'm trying to partition an existing table in Oracle and the research I've done seems to say that using the DBMS_REDEFINITION package seems to be the best way to do this. The problem is I doesn't seem ...
0
votes
0answers
25 views

Broken partition function/schemes

The dilemma SQL Server 2008 Enterprise 2 partition schemes using the same partition function partition function splits on date column, monthly basis maintenance script to split partition function ...
1
vote
0answers
38 views

Restoring SQL Server 2012 Enterprise to Web Edition not possible due to full text partitioning

I have an SQL Server 2012 Enterprise database I’m trying to restore to SQL Server 2012 Web Edition instance. I’ve used sys.dm_db_persisted_sku_features to find features to remove before a ...
8
votes
1answer
2k 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 ...
2
votes
2answers
721 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 ...
1
vote
0answers
36 views

Can I Implement a Sliding Window for a Table Spread Across Two Partition Schemes on the Same Partition Function?

I have a table with a PK based on a bigint identity and will have a clustered index on a datetime2 column. I've created a partition function based on calendar quarter and was planning on two partition ...
-1
votes
1answer
65 views

What is the best source for deep learning about table partitioning? [closed]

I know some of the basics of table partitioning, but I haven't used it for an operational database yet, as I went studying more about the topic, I found some information that is written for SQL server ...
1
vote
0answers
363 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 ...
0
votes
0answers
45 views

Insert strategy for partitioned table

Is there a separate strategy for inserting / loading data into a table that is partitioned? We are using TVPs inside a single proc to load multiple tables into a SQL Server DB and I did not change the ...
0
votes
0answers
42 views

Scheduled Partition one time by day

I am trying to schedule partitioning so that it runs only at 11:00pm. For example, I have the following table: Table 1 Name status John 0 Thomas 1 Dimitri 1 Yohann 0 At ...
0
votes
0answers
9 views

Designing Users table on DistributedDB

I am using SQL Server 2012 SP2 Express with PDB Wrapper 1.0.4 on 7x Partition nodes + Common server. Currently my users table is shared on common but we see high network latency. We left users table ...
0
votes
0answers
33 views

SQL Server: can reduce data file with partition table?

I had a database with one table called Saler stored on 'C:\'. Primary MDF file is very big (100G and about 200 million rows) and my disc is nearly full. Now I added a new file group 'Secondary' and a ...
0
votes
1answer
34 views

SQL Server - Show Content of a partition

I did somes partitions on my sql database with the wizard of microsoft Sql Server Management Studio. But i wanted to know if it's possible to do an request for show just lines contains by one ...
10
votes
2answers
98 views

SQL Server 2008 R2 Partitioning - same FileGroup, 1 File, 2 partition_numbers - HELP

It's my first go at partitioning in SQL Server, I learned from the Brent Ozar guide which is fantastic :) A few times I have ran into a weird scenario; when I run: SELECT * FROM ph.FileGroupDetail ...
1
vote
1answer
88 views

Postgresql INSERT triggers “0 row affected” workaround

In a INSERT trigger based table partition, insert always give "0 row affected". I think this problem is well know, it is even in the todo. Is there any workarounds for this issue in 9.4?
0
votes
0answers
51 views

Optimizing DB for performance by archiving/partitioning

I have a table in my Database which would be loaded with huge amounts of data each day. These are basically status of the orders customers has placed. The UI in my application which calls the DB for ...
2
votes
0answers
38 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
1
vote
1answer
94 views

Bringing SQL Server filegroups online

We've experienced an sql injection attack which corrupted our main db. The main db had two partitioned tables into filegroups. The corrupt database entered into Suspect mode, and it's been ...
3
votes
2answers
529 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
34 views

Increasing system load due to multiple inserts every millisecond

In my project I need to do multiple database inserts every millisecond and thus Postgres takes huge amount of CPU. I need to find a way to reduce CPU load as I need to process about millions of rows. ...
0
votes
1answer
28 views

Partitioning a database

I have a database that's growing larger by the week (as expected). Key tables are becoming huge and slower to perform queries against. Whilst the indexes are sufficient, I've read the partitioning ...
4
votes
2answers
2k views

Separate tables or partition one huge table?

I'm building a rather large table that includes a particular column, my_column. There are a small-ish number of possible values for my_column, and each unique value of my_column should have the same ...