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

learn more… | top users | synonyms (1)

1
vote
0answers
67 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
1
vote
2answers
104 views

Basic PARTITION question in MySQL

Say I have an application using MySQL and at some point decide that implementing Partitions on certain tables might improve performance. Would I need to change the applications select statements ...
1
vote
1answer
53 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
2answers
1k views

Difference between partitioning and fragmentation [closed]

I'm trying to learn something about distributed databases and I stumbled upon the concepts of fragmentation and partitioning. It seems to me as if both do the same thing and produce similar results ...
1
vote
2answers
732 views

Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

I have this table definition: CREATE TABLE `va_ventas` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `Date` DATE NOT NULL, `Time` TIME NULL DEFAULT NULL, `Country` VARCHAR(3) NULL DEFAULT NULL, ...
1
vote
2answers
2k views

MySql - partition by range and unique key

i'm trying to create the following table: CREATE TABLE `s_relations_with_partition` ( `id` int(11) NOT NULL AUTO_INCREMENT, `source_persona_id` int(11) NOT NULL, `relation_type` int(11) NOT ...
1
vote
2answers
290 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 ...
0
votes
1answer
226 views

strange situation with very large partitioning table

I have a very strange situation here... On my SQL Box there is a large Table this table is partitioned, everything was fine until I forget to make new partition ranges. in the first picture the last ...
0
votes
1answer
111 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 ...
0
votes
2answers
74 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
1answer
134 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
0
votes
1answer
1k views

Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operation

I am having a Database. The size of my database is near about 4GB(Only .mdf file). In that database only few tables present which have the maximum data of database. One of them and the biggest table ...
0
votes
2answers
212 views

Inputs on handling large volumes of data for specific tables in SQL Server standard

I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB. I have few critical transaction tables like this: 1. Payments 2. Cheques etc., and each of ...
0
votes
2answers
152 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
3answers
248 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
2answers
5k views

Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs. These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved ...
0
votes
2answers
432 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
1answer
71 views

Does partitioning sometimes require denormalization?

Specific example of what I'm asking: Say that a SaaS app has an Order table with CustomerID and a child OrderItem table. We want to partition both tables and we want to use CustomerID as the ...
0
votes
1answer
3k 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 ...
0
votes
2answers
27 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 ...
0
votes
1answer
103 views

postgres fetch record from partitions without the check constraints

I am using postgres 9.4, I have a messages table that has 100M records, messages belongs to one feed_id, so I decided to partition the tables by posted_at. Table "public.messages" ...
0
votes
1answer
416 views

How to split a MySQL table file?

I have a table in a MySQL database for which innodb_file_per_table is enabled. One of my tables is about 20GB and growing. How can I partition the table into separate files? Suppose that I have a ...
0
votes
1answer
60 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 ...
0
votes
1answer
872 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 ...
0
votes
2answers
166 views

Connection problems with ManagePartition executable

I'm currently running SQL Server 2014 and we have some tables that have been partitioned and I'm trying to use the ManagePartition application here (http://sqlpartitionmgmt.codeplex.com/) I've typed ...
0
votes
2answers
583 views

Sync Indexes between two tables

I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database. I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync ...
0
votes
2answers
266 views

MySQL Cluster - partitions on disk

I have question about MySQL Cluster: Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks) ...
0
votes
1answer
882 views

Postgres 9.1 query plan not using check_constraints on partitions after large data load

I've created a table in Postgres 9.1 called 'markets' that contains data about a market in a specific US zip code. I've partitioned the data on the zip code column, using the first digit of the zip ...
0
votes
1answer
2k views

Partitioning not working need primary key?

We are trying some partitioning as below. ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) ( PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), PARTITION p2 VALUES LESS THAN ...
0
votes
1answer
2k views

Reindexing Partitioned Table Indexes

Historic Reporting Server running SQL 2008 R2. I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours. For every table that had high ...
0
votes
1answer
33 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. ...
0
votes
1answer
29 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 ...
0
votes
1answer
275 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 ...
0
votes
1answer
290 views

Sql Server thinks I have different Data Compression when swaping partitions

I am setting partition switching for my database. I created a script using SqlCmd variable so that the same script would make both my source and target tables for partition swapping. Here is an ...
0
votes
1answer
77 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 ...
0
votes
1answer
70 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 ...
0
votes
1answer
615 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 ...
0
votes
1answer
693 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
3answers
62 views

Redesign basic Mysql storage architecture

We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this: capture_table ---------- ...
0
votes
1answer
209 views

Oracle DB - Is it possible to distribute data of a table onto multiple servers?

BigTable[1]-like systems (HBase[2] for instance) allows a table to be distributed transparently accross multiple servers. This works as follows: Each server perform the read/writes operations on ...
0
votes
1answer
59 views

whether to merge partitioned table with only partially overlapping columns

I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general. I have data with a few "cross-sectional" ...
0
votes
1answer
2k views

How to get accurate row counts in partitions in a table

I am trying to fetch row counts in each partition for a table. But I got confused as in sys.partitions row counts is on basis of indexes. So how can I get exact row count for partitions in a table.
0
votes
1answer
243 views

What is the best partition technique for large table with TEXT column?

MySQL: What is the best partition technique for large InnoDB table (around 1.5M rows) with TEXT type of column? There is a FULLTEXT index on the TEXT column, which is very essential for my searching ...
0
votes
1answer
86 views

Proper drive partitioning in Linux for Oracle

I have a current production physical server running on RHEL and I'm going to be building a new Oracle Linux VM to take it's place. Below is a listing of the existing physical drive partitions. Can ...
0
votes
1answer
4k views

Database partitioning - Horizontal and Vertical sharding - Difference between Normalization and Row Splitting?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a ...
0
votes
1answer
255 views

Partitioning table with billion row

I have a big table referenced by other tables and need to split the table on several parts. I create table by CREATE TABLE product_part0 () INHERITS (product); Move some rows to product_part0 by ...
0
votes
1answer
201 views

In partition definition filegroup name appears twice, what does that mean?

short question regarding partitions, consider the following partition definition: CREATE PARTITION SCHEME [MSAppServerPS_ASWcfEventsTable] AS PARTITION [MSAppServerPF_ASWcfEventsTable] TO ...
0
votes
1answer
402 views

Deciding the best way to partition (Postgresql)

I have been trying to research this for quite a while now and I am having a hard time trying to figure it out. I am out of ideas other then setting up an extra test database and running through all of ...
0
votes
1answer
106 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
0
votes
1answer
988 views

Are there any disadvantages to partitioning on financial year?

Our current set up has one table per financial year (May 1- April 30). Each table has approx 1.5 million rows. We have about 8 years of data, and will obviously be adding each year. The majority of ...