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

learn more… | top users | synonyms (1)

0
votes
0answers
33 views

How to Efficiently Handle Queries On a Table Subset

I have a MySQL database where one table in the database contains a datetime field and the other fields contain numeric data. One application frequently queries the server using a WHERE clause on one ...
1
vote
1answer
105 views

SQL Server Partition

I am trying to create partitions on a table of one of my test databases called WingTipToys2012. Following the recomendations of many sources, I created the three file groups called FgOne, FgTwo and ...
0
votes
0answers
52 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 query:...
1
vote
0answers
35 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
195 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
34 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
78 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
61 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
239 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
1answer
44 views

Restore from SQL Server 2014 Enterprise to BI

Is it possible to restore a SQL Server 2014 Enterprise database with table partitioning (Production) to a SQL Server 2014 BI Version (Test/Development) ? Of course I don't expect to use the table ...
1
vote
0answers
25 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 ...
0
votes
0answers
18 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 most-...
0
votes
0answers
62 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 ...
0
votes
0answers
31 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 ...
3
votes
2answers
64 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 ...
1
vote
0answers
70 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 ...
0
votes
0answers
56 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 ...
1
vote
1answer
136 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 ...
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 ...
19
votes
2answers
308 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, ...
9
votes
2answers
255 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
1answer
65 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
73 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 ...
3
votes
1answer
52 views

Returning all rows from multiple partitions of a partitioned table

I have a table that is manually partitioned into multiple filegroups and spread out over multiple tables by month. My partition scheme is using a date column. What I would like to do is something like ...
0
votes
0answers
31 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
44 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 backup/...
5
votes
1answer
322 views

Why should I “Avoid partitioning dimension tables” on SQL Server?

SQL Cat have a list of tips titled Top 10 Best Practices for Building a Large Scale Relational Data Warehouse. Under section 4 - Design dimension tables appropriately they state: Avoid ...
1
vote
1answer
53 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 ...
1
vote
0answers
40 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
71 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 ...
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
44 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 11:00pm,...
0
votes
0answers
35 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
0answers
10 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
1answer
35 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 ...
1
vote
1answer
108 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 ...
3
votes
1answer
100 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, ...
2
votes
0answers
40 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
122 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 ...
0
votes
0answers
36 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
30 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 ...
0
votes
1answer
40 views

How do I create a table best suited for storing address information

I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this Table Address ID | Address Line1 | Address Line2 | Address Line3| City ...
1
vote
1answer
91 views

how to check whether or not queries are doing partition elimination when accessing my partitioned table?

I am considering partitioning a table. I am trying to think in terms of how we query that table so that I achieve partition elimination. Suppose I have a table that has a partition by month, what ...
1
vote
2answers
104 views

Partitioned Tables and Indexes - what are the downsides?

When talking about Partitioned Tables and Indexes for tables with less than 100 partitions, no nonaligned indexes: with that I mean: Nonaligned index An index partitioned ...
1
vote
2answers
1k views

Understanding Disk Partition Alignment for Windows Server 2012 and SQL Server 2014

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size. Now I am stuck using Diskpart to set ...
1
vote
1answer
90 views

Aligned index column order

Situation: There is one SQL Server (2014) database that has multiple partitioned tables, that all share the same partitioning strategy. Tables have a partition per unique [PartitionKey]. Every [Id] is ...
3
votes
0answers
795 views

Slow inserts/updates into partitioned table with lot of partitions

I'm designing the partitioning for our new database, but we are facing performance issues in inserting or updating the partitioned table. For simplicity suppose we have one table T, which have an ...
20
votes
1answer
678 views

Seek and you shall Scan… on partitioned tables

I've read these articles in PCMag by Itzik Ben-Gan: Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize Seek and You Shall Scan Part II: Ascending Keys I’m currently having a "...
2
votes
1answer
370 views

Can't execute dynamic DDL, argument is NULL [closed]

I'm running PostgreSQL 9.4.4. I'm new to Postgres (used to Oracle & SQL Server) so I might be doing something really silly. I'm trying to dynamically create partitions for a table. I've got the ...