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

learn more… | top users | synonyms (1)

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/...
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
739 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
38 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 ...
1
vote
0answers
381 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
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
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
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
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
100 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
110 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
597 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
35 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
3k 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 ...
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
85 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
97 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 ...
3
votes
0answers
741 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 ...
2
votes
3answers
7k views

Partition by year and sub-partition by month mysql

I have a database with a date column having datatype as date. I am using mysql 5.6 The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub ...
1
vote
1answer
89 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 ...
1
vote
2answers
103 views

Inefficient queries on partitioned tables

Consider a scenario in SQL Server that includes a partitioned table with over 500 partitions. The number of partitioning column values in a temp table are less than 10. We join the temp table to the ...
2
votes
3answers
120 views

Performance of rare SELECT vs. frequent INSERT in timeseries data

I have a simple timeseries table movement_history ( data_id serial, item_id character varying (8), event_time timestamp without timezone, location_id character varying (7), ...
2
votes
1answer
336 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 ...
1
vote
1answer
201 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 ...
4
votes
1answer
258 views

Star join query optimization - Change partitioning, use columnstore?

I am trying to know the best possible way to improve performance of a query given to me by my client. It contains a few tables which are joined and one of table is called dwh.fac_sale_detail which ...
12
votes
4answers
4k views

Table partitioning for archiving data

Scenario: two databases: DB_A and DB_Archive with one very big table called tableA. every day, records older than 60 days are deleted from DB_A and moved to DB_Archive mainly to leave thing "...
1
vote
1answer
160 views

ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint ''

I'm using SQL Server 2008. I have two tables PARTITIONTESTTABLE and PARTITIONTESTTABLESUB. PARTITIONTESTTABLESUB has a foreign key to PARTITIONTESTTABLE. I created partition and both of these two ...
2
votes
1answer
56 views

Partition scheme filegroup metadata

When you create a partition scheme against a particular partition function, you specify a series of filegroups. I cannot find the metadata store where this filegroup list can be found & queried. ...
0
votes
1answer
4k views

Unable to select the records from specific partition in mysql?

I've created a table using the following query, CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id ...
9
votes
1answer
287 views

Why my database mirror break down after changing file group settings from RESTRICTED_USER to MULTI_USER?

My environment is the following: VMWare 5.5 vitalized server MS Windows Server 2008R2 Enterprise domain and SQL Server 2008 R2 Enterprise. Centralized storage with fibre-channel connection. I have ...
5
votes
1answer
243 views

Managing and speeding up queries on PostgreSQL table with over 3 trillion rows

I have time series data which spans over 10 years and has over 3 trillion rows and 10 columns. At the moment I use a PCIe SSD with 128GB of RAM and I am finding that querying takes a significant ...
0
votes
1answer
78 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
1
vote
1answer
162 views

Diffrences between master and slave replication in mysql?

In a database named asterisk, there is a table named cdr which was partitioned on the time field. mysql> show create table cdr; Create Table: CREATE TABLE `cdr` ( `gid` bigint(20) NOT NULL ...
0
votes
0answers
41 views

SPLIT PARTITION issue?

we had written program to ADD partition on every month. But, one of our customer forgotten to execute the program from Jan to July and now, he is facing performance issues (because corresponding table ...
2
votes
1answer
97 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
0
votes
1answer
533 views

Oracle 11g - drop maxvalue partition, keep data

I have some tables with data like this: CREATE TABLE "PARTTEST4" ("year" number(4,0) NOT NULL ENABLE) PARTITION BY RANGE ("year") (PARTITION "P_OLD" VALUES LESS THAN (2010), PARTITION "...
4
votes
1answer
232 views

Index is not used with table inheritance

I have a PostgreSQL 9.0.12 database with a master table and 2 child tables. My tables: CREATE TABLE test2 ( id serial PRIMARY KEY, coll character varying(15), ts timestamp without time ...
4
votes
1answer
387 views

Performance issues with inherited tables and indices

I have a PostgreSQL database with a master table and 2 child tables. My master table: CREATE TABLE test ( id serial PRIMARY KEY, date timestamp without time zone ); CREATE INDEX ON test(...
0
votes
1answer
143 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 ...
8
votes
3answers
152 views

Why NOT partition?

When would one NOT want to partition a database? (thinking MySQL partitioning) In my case I'll be starting with a couple millions rows, it should grow from there. Primary key on a character field ...
2
votes
1answer
98 views

Deciding what columns to use for partitioning

I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up ...
2
votes
0answers
42 views

Better performance to have nullable columns or an extra table? [duplicate]

There are many ways to get rid of nulls in tables, and I have heard of various reasons to get rid of them, but can anyone show me some real numbers as to how your database might perform differently ...
2
votes
1answer
956 views

Can I exchange partition between two table with partition with same name?

I am trying to restore one partition from a backup table. Both tables have partitions with the same name. My goal is to grab the data from the backup table partition x and put it in original table ...
0
votes
0answers
88 views

Problem Setting up MariaDB Spider HA

I have problem setting MariaDB with Spider for partitioning and HA.My environment consists of: Spider Proxy - 10.64.20.207 Backend1 - 10.64.20.208 Backend2 - 10.64.20.209 First I am creating user ...