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

learn more… | top users | synonyms (1)

0
votes
1answer
416 views

Partitioning in SQL Server 2008

I want to partition some of our tables in a SQL Server 2008 database. The partition key will be date based. I have several questions: Is there a rule of thumb regarding the recommended number of ...
0
votes
2answers
1k views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
0
votes
1answer
111 views

Multitable partition guide

We have a main table called tblLink in it will have a date field.The tblLink will have its primary key as foreign in few other tables too and one of the table will tblMainData which over time will be ...
0
votes
1answer
350 views

horizontal partitioning or multiple tables?

I'm developing an application and need to propose a database schema. We plan for a MySQL database with millions of records which should be distributed over multiple machines for performance and ...
0
votes
2answers
269 views

shall I use multiple separete tables or one table with multiple partition on oracle

I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
0
votes
1answer
386 views

MySql - Global index

I'm new to MySql partitioning and have a question regarding indexes. let say i have the following table: CREATE TABLE `members` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, ...
0
votes
1answer
198 views

Multiple Partition Keys?

This is a follow-on from a previous question. I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is ...
0
votes
1answer
17 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
0answers
22 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 ...
0
votes
0answers
42 views

SQL Server 2012/SSIS - How to Import Partitioned Table Data to Reporting DB?

What's a recommended SSIS process flow for importing a daily partitioned table to a reporting DB table (SQL Server 2012)? Will be switching partitions on main DB to a staging table each day and ...
0
votes
1answer
42 views

Table partition in aws MYSQL RDS master slave setup

we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this ...
0
votes
1answer
57 views

Table partitioning in MYSQL Master Slave

I have to Partition a MYSQL table using Range partitioning. Table is 200M rows big and I have Master/Slave replication. What I want to do is to stop replication on slave, partition the table on ...
0
votes
2answers
34 views

Partitioning visitor logs database

I have set up a table that logs every page view a visitor makes. This is going to result in a table with about 50,000 entries per week. To keep things manageable and performing well I am intending on ...
0
votes
1answer
110 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 ...
0
votes
2answers
69 views

sql server transactional - replication - the internal procedures often deadlock with user processes

Very often I have deadlocks on my subscription server, involving the replication internal stored procedures and user processes either ad hoc or procedure queries. For example: Deadlock of the ...
0
votes
1answer
27 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
2answers
40 views

Table partition should not be selected

I am looking to partition a table (nearly 30M rows) by date ranges (mysql version : 5.1.73) CREATE TABLE `TABLE_X` ( `name_x` varchar(50) NOT NULL, `type_x` varchar(5) NOT NULL, `status_x` ...
0
votes
1answer
38 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
0answers
11 views

Delete datafile from parttioned table

I'm used SQL Server 2014. My big table partitioned by date column and each partition has own datafile. I want delete oldest partition data and data file too. How to do it?
0
votes
0answers
34 views

Is there a workaround for the lack of foreign keys constraint to a partitioned table in PostgreSQL?

I have a big table A (~30 million lines) at the core of my model. Another table B has a foreign key referencing A. I would like to partition A using the well-known inheritance hack. Is there a ...
0
votes
0answers
21 views

Moving Replicated & Partitioned SQL2005 Enterprise to SQL 2008R2 Enterprise

Our SQL2005 Enterprise has 4 databases which are all partitioned, each have 5 files and a handful of tables replicated, compatibility mode is 90. They are on an old Windows Server 2003 32bit machine ...
0
votes
1answer
60 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 ...
0
votes
0answers
26 views

PostgreSQL analyze only parent table only

I am moving data from a few billion records table to newly created partitions. I am inserting into the child table directly and deleting from the main table with filters on the appropriate columns in ...
0
votes
0answers
51 views

Partitioning large table requires lots of extra disk space: does switching recovery model help?

We are in the process of implementing partitioning on a SQL Server 2012 database. It worked fine in our environment, but in the client environment we ran out of disk space. The entire database was ...
0
votes
0answers
69 views

Import non-partitioned table into an existing range partitioned table - ORACLE

I have export non-partitioned table1 on disk with some records, then I create same new table2 in my schema (different name) but with range partitioning, I'm talking about Oracle database. So my ...
0
votes
0answers
61 views

SQL Server Partitioning - parent/child tables reference partitioning

I have a requirement to partition quick growing tables in our product and below are the table information with key details. PERIODIC_EVENTS (PARENT TABLE which has ID (sequence no) as Primary KEY and ...
0
votes
1answer
53 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 ...
0
votes
1answer
108 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 ...
0
votes
0answers
39 views

How to determine is a value exists in a column within a partition

I have a set of data where I need to look at partitioned data and determine if a value exists within that partition. Example data: AccountNumber Code Status Credited ...
0
votes
0answers
28 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 ...
0
votes
1answer
81 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` ...
0
votes
0answers
177 views

How to partition a MySQL table by Week using a timestamp column?

I have the following MySQL 5.6 table, which uses InnoDB and contains a timestamp column (I've omitted all the extra columns, in order to simplify my question): create table partition_by_week ( id ...
0
votes
0answers
63 views

High CPU Usage for Mysql after adding partitions

On an amazon m3.large instance (vCPU:2, Mem: 7.5), I run a mysql(5.5.29) server. Initially, the state of the server is OK except that the query is too slow. However, after partitioning the table ...
0
votes
0answers
35 views

Is database sharding done on a table level or database level?

I'm new to sharding databases, so I'm trying to wrap my head around it. Let's say we have a database that has 3 tables: Customer, Suppliers and Orders. Each table has 10 million records. I want to ...
0
votes
0answers
18 views

Support for dynamically creating partitions

I would like to know the following: Is there a RDBMS which offers support for creating partitions for a table dynamically? For e.g.: If I choose to partition a table by day/week etc., I want it to ...
0
votes
0answers
151 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. ...
0
votes
0answers
78 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
0answers
50 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 ...
0
votes
0answers
133 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 ...
0
votes
0answers
23 views

How to figure out how to split a table using the proper scheme

If you have a big table that you want to partition and you have 2 candidates as the partitioning key (in the sense that 50% of the queries use one column and 50% of the queries use the other column ...
0
votes
0answers
41 views

Adding Partitions to an Existing Filegroup

For maintenance reasons, the organization that controls the servers where I work has decided to limit the number of file groups and files for partitioning to just 1 of each. Their reason for this is ...
0
votes
0answers
78 views

How do triggers work on partitioned tables?

I have partitioned a table on the basis of a column. This column can either have 0 or 1. So, only two partitions (p0, p1) have been created internally. On this table there are 3 triggers which execute ...
0
votes
0answers
209 views

Copying a table with its structure, indexes, and associated triggers?

I have studied this post on Stack Overflow: Fastest way to copy a table in MySQL. But no answer is copying everything like the table's structure, data, indexes as well as associated triggers. Is it ...
0
votes
1answer
71 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
1answer
67 views

MySQL 5.1: How to rollback partitioned table

I have partitioned a table on the basis of one flag column have value either 0 or 1. Now, I want to roll back these tables. How can I do this?
0
votes
1answer
236 views

How to split the Wordpress database?

FYI - I originally posted this question under the Wordpress Development site here: http://wordpress.stackexchange.com/questions/145861/how-to-split-the-wordpress-database I have a site that is used ...
0
votes
1answer
175 views

Does Oracle Partition by Reference optimizes join queries

Oracle 11g has a new feature in partitioning tables called partition by reference. I want to know whether Oracle joins (nested-loop-joins or hash-join or ...) rows from proper partitions when joining ...
0
votes
0answers
80 views

MySQL: Optimizing for large but discrete data sets

In brief: I'm developing a database that handles GTFS datasets from multiple transit agencies. Each dataset contains millions of rows in the stop_times.txt file (and thus its corresponding table). ...
0
votes
0answers
108 views

How do I debug an Oracle Linux Diskspace Issue?

I have about 429GB Diskspace allocated to this VM. I see that only 179GB is being used. Now when I try to add a 128GB file I get an I/O error saying I do not have any space left. I need to be able ...
0
votes
1answer
43 views

SELECT to only see Offsetting Transactions in table?

I have a temp table like this I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos. ...