Splitting a database table into multiple segments for performance or manageability.
4
votes
1answer
227 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 ...
1
vote
1answer
99 views
Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?
I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will ...
3
votes
3answers
221 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 ...
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
812 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
173 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 ...
4
votes
1answer
351 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
392 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 ...
3
votes
2answers
248 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 ...
2
votes
1answer
203 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
64 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: ...
4
votes
1answer
177 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
266 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
128 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 ...
0
votes
1answer
257 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 ...
1
vote
2answers
97 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
142 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
169 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
307 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.
...
4
votes
1answer
517 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 ...
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 ...
1
vote
1answer
509 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 ...
6
votes
2answers
153 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 ...
3
votes
1answer
359 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:
...
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 ...
0
votes
1answer
380 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 ...
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
202 views
Get a record's current partition
In SQL Server 2008 R2, how would I determine what partition a record currently lives in?
1
vote
0answers
191 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 ...
5
votes
2answers
241 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 ...
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, ...
2
votes
0answers
617 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 ...
0
votes
0answers
414 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:
...
1
vote
2answers
82 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
522 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 ...
3
votes
1answer
145 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 ...
0
votes
1answer
390 views
MySQL, “columns partitioning” when multiple columns are individiually important
MySQL 5.5 introduces "columns partitioning".
http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns.html
I'm trying to better understand how it works when two columns are important individually.
...
1
vote
1answer
61 views
Where does it make sense to store Indexes for partitioned tables?
Prior to table partitioning, all the indexes were stored on a separate drive, per this question. Now that I partitioned a couple of tables, SSMS is offering to store indexes in the partition scheme. ...
3
votes
1answer
152 views
Why am I getting Table scan on a partitioned table query?
I have table Inventory_Break partitioned on IR_ID (int). I also created a non-clustered index for IR_ID on the table.
When I run the following:
SELECT *
FROM dbo.INVENTORY_BREAK
WHERE IR_ID ...
11
votes
3answers
799 views
Does the partition key also have to be part of the primary key?
I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
5
votes
2answers
119 views
Mechanics of partitioning
Imagine the following hypothetical database structure. Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).
I want to partition my SQL ...
7
votes
3answers
710 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 ...
0
votes
1answer
86 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 ...
3
votes
1answer
183 views
Adding new key(s) to enable partitioning, INSERT performance?
I have a table to storage GPS coordinates with multiple (~30) inserts per second (from 50 diferent devices) during 12 hours per day, so the table is growing fast, to improve the SELECT speed I was ...
1
vote
1answer
177 views
What is the “Data partitioned, Shared-Nothing, Parallel Database” solution for PostgreSQL ?
I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read ...
0
votes
1answer
148 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 ...
