Splitting a database table into multiple segments for performance or manageability.
1
vote
0answers
68 views
Dynamic MySQL partitioning based on UnixTime
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing unixtime.
I am designing an aging ...
1
vote
0answers
68 views
MySQL Partitioning, Reorganize Max Value partition (Known Bug / Feature)
I have written a crone job which reorganizes last max value partition every week into 2 new partitions , one for the previous week , and one for the future data (new max value partition which ...
2
votes
1answer
722 views
After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
4
votes
1answer
75 views
Expanding a partitioned table
I have a table that holds four years of data, where for each month I have a partition that is based on a filegroup with two files. Unexpectedly, I received eight more years of data and prepared the ...
1
vote
2answers
308 views
Best way to organize compound indexes on partitioned table (date by range)?
I have a table with an auto increment primary key, but in order to use the partition feature in MySQL I had to include the date in the primary key so I would use partition by range using the date ...
8
votes
1answer
230 views
What is the modern way to partition PostgreSQL across machines, when the data is “naturally partitionable”
After several years of dwelling into the "NoSQL" space, now I have a problem that is quite "relational" in its nature.
Today I see data stores with quite different eyes than before. Things like Riak ...
3
votes
2answers
246 views
Large Fact table and partitioning key dilemma
I have fairly large fact table (2 billion records, approx 120 GB). This table is not partitioned and the queries are very slow to respond. I am planning to partition the table and indexes.
The table ...
0
votes
0answers
39 views
How to check on my partitions?
I am a newbie into partition. First I have a table with auto increment with its primary key and many other fields. I wanted to partition based on dataInsertDateTime column. It have me error so I had ...
3
votes
3answers
219 views
High insert rate on MySQL
I have a project at the moment that is close to be finished. I have been asked (at the very end) to log a specific event. The event will occur very often (easily a few times each 5sec). Each event ...
4
votes
1answer
224 views
Why isn't partition-level lock escalation the default?
In SQL Server, locks are normally escalated from row or page -> table. Starting with SQL Server 2008, a new level of lock escalation was added - partition level.
However, this isn't automatically ...
2
votes
1answer
87 views
Where are partitions defined in mysql
I have a large table that I want to spread across multiple disks/LUNs. It will contain blobs and I want to have it separated out from the other data.
How do I go from new disks (lets call them ...
1
vote
2answers
221 views
Table partitioning
Recently I have been looking to optimize my database and its size is 7GB. I have an option to partition big table. But partitioning would just divide the big table into small ones while remaining on ...
0
votes
0answers
38 views
Partitioning and Sub-partitioning in MySQL
I want to partition a table by month and sub-partition by day.
When new data is added for a new month, I don't want to manually update the database.
When I initially create my database, I have data ...
3
votes
1answer
770 views
How to create a partitioned table based on date?
I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an ...
2
votes
1answer
172 views
SELECT multiple sensor values in one query
Background
I have a couple of devices, each with a couple of sensors. I log these every now and then and stores them in a table described below. When someone requests a web page, I fetch a couple of ...
2
votes
1answer
692 views
ALTER TABLE SWITCH statement failed
ALTER TABLE SWITCH statement failed because column does not have the same ANSI trimming semantics in tables...
Does anyone knows how to fix that on already existing table full of data?
Well, I've ...
4
votes
1answer
344 views
Best way to increase no of partition in SQL Server Table
I am maintaining a database with a big big table, which portioned for each month. There is already tens of partition there but its till January 2013, So its time to create new partitions.
Can anyone ...
5
votes
1answer
390 views
Database archive solutions
In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
2
votes
1answer
202 views
Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard
I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise ...
1
vote
1answer
63 views
Partitions and Indexes, in which cases should be used
I know some knowledges:
Partitions used to achieve better performance (like described in this article)
And indexes used to increase performance in selection operations.
And as result my question: ...
2
votes
3answers
622 views
db2 range partitioning attach and detach issue
We have a table which is used in many select Queries, as it is be accessed by customers to fetch their information. Also they can change their information too whenever they want.
We had to retrieve ...
4
votes
1answer
173 views
Does UPDATE INDEXES work for local indexes?
I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):
CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
PARTITION p1 VALUES(1),
PARTITION ...
0
votes
1answer
265 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 ...
1
vote
0answers
42 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 ...
4
votes
1answer
126 views
MySQL table design of logging tables
I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization.
Currently we have a table that logs session data, it writes about ...
1
vote
2answers
94 views
How to check if there is partitions in use in fact tables?
I'm going to take a first look of a large data warehouse with many fact tables. How can I easily check if there is partitions and partition functions in use in these fact tables?
2
votes
1answer
141 views
Do I need to index a key that I partition by?
I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...
1
vote
0answers
47 views
why size of data files is not grow up even existing data?
currently, I created 1 partition table with 1 file group and 4 data files (4 quarter/year).
Data have been inserted successfully. And everything is right when I check records belong to which ...
2
votes
2answers
167 views
Investigating/Troubleshooting I/O Spikes
We have been experiencing some relatively large I/O (IOPS) spikes on the SAN that supports our production SQL Server.
The spikes seem to fall at an exact time after the hour, each hour. We have ...
4
votes
1answer
304 views
Partitioning a table vs moving entries to another table
I don't understand how partitioning helps me more or less than using a separate table to store older records.
I need to maintain a list of stock entries along with historical records about the stock.
...
1
vote
1answer
505 views
SSIS: “The buffer manager cannot write 8 bytes to file”
We have about 60 or more SSAS cubes on one server. All the cubes have the same structure. Each cube has a different size because each cube has a different underlying data warehouse. However the schema ...
0
votes
0answers
52 views
MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
6
votes
2answers
150 views
Is it worth partitioning a Table by single value in Oracle?
Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
4
votes
1answer
513 views
MySQL Partitioning: Is there a performance tradeoff between number of partitions and size of each partition?
I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
1
vote
2answers
294 views
SQL Partioning on date, how does it work for future records?
I am setting up a new system and I would like to partition the data for speed. Basically what I would like to do is have one database with all the latest information (e.g. last 3 months). then have ...
1
vote
2answers
81 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 ...
2
votes
1answer
367 views
Rotate a table in PostgreSQL
Problem description
I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.
At present, I have a program which replaces (DROP + CREATE) this table.
Sometimes, just ...
3
votes
1answer
356 views
Why do we have to use SWITCH in table partitioning?
I'm a beginner in SQL.
I'd like to know what is the importance of SWITCH in table partitioning?
I've created one table partitioned month-wise, with a total of 12 partitions.
The ranges are:
...
9
votes
2answers
1k views
SQL Server does not optimize parallel merge join on two equivalently partitioned tables
Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
0
votes
1answer
375 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 ...
5
votes
2answers
349 views
Large accounting table, best way to partitioning by two dates
I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call ...
4
votes
1answer
123 views
SQL Server data partitioning
This is for SQL Server:
I have a table that has been partitioned. Sometimes, I need to completely update an existing partition - the updates could be extensive enough to touch all rows of the ...
5
votes
2answers
240 views
Partition Function vs. Clustered Index
We have a table that is clustered on identity/datetime2. It is partitioned on the same datetime2. Are there any reasons to cluster on datetime2/identity instead? I understand the reasoning behind ...
1
vote
0answers
190 views
Pros/Cons of Using Table/Index Partitioning in SQL Server
We have a number of tables that were partitioned primarily to reduce problems when purging data (via sliding-window partitioning / truncate). I have found a number of caveats that come with ...
2
votes
0answers
614 views
How to structure Master/Detail tables for performance without denormalizing?
Some Background Info
We have a set of tables that holds all the transactions for our system, TransactionHeaders and TransactionDetails. There are roughly 80k Transactions a day which translates to ...
0
votes
0answers
51 views
Partition manager utility/script?
Does anyone know of any Utilities or scripts out there for partition management. Most of our partitioning is range based and I have a set of home grown scripts to expand as needed as well as maintain ...
1
vote
1answer
520 views
PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation:
We have a partitioned Table "buz_scdr" over which we have built a VIEW ...
1
vote
2answers
1k views
Troubleshooting Oracle ORA-08103 - object no longer exists
I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it.
Since I'm truncating a different ...
0
votes
0answers
412 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:
...
3
votes
1answer
143 views
Oracle: Interval partitioning and dependant tables
I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
