Splitting a database table into multiple segments for performance or manageability.
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 ...
3
votes
1answer
151 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 ...
3
votes
1answer
182 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 ...
3
votes
1answer
190 views
SQL Server Partitioning - Parent / Child tables
I'm currently investigating implementing partitioning for a fairly hefty (800GB and growing) SQL Server database. All of the data in the tables is relatable to a date/time, however this date/time is ...
3
votes
1answer
249 views
Full-text index in partitioned table
I have a table with just over a million records with a full-text index.
This table has been split in two in the past, and the data older than a certain date is moved to another table with the exact ...
3
votes
0answers
106 views
How do I know if my database partitioning is done well?
I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts ...
2
votes
3answers
239 views
Is it a good practice to apply Vertical Partition to a newly created model?
Suppose you are modeling the ER model for a new Object Oriented Web Application.
There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of ...
2
votes
2answers
545 views
Is it possible with SQL Server 2008 to have a Partioned Tables with an Identity Column?
I'm not thinking of using it for the partition function.
I just want to know, if it is supported or not.
Don't tell me why it is not a good idea to use such columns on partitioned tables.
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
2answers
390 views
T-SQL: How to switch all partitions from one table to another?
Is it possible in a single statement to switch ALL partitions from one partitioned table to a staging table?
2
votes
1answer
110 views
When is data moved during an Oracle partition split?
I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do:
alter table ...
2
votes
1answer
123 views
Can not add list partitions
Following is the create table statement that is working as expected. But if I have a table without any partitions and if I try add partitions, it seems that I can not add more than 16 values in it.
...
2
votes
1answer
711 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 ...
2
votes
2answers
608 views
Best practices for creating partition schemes and functions
I'm implementing partitioning for the first time in SQL Server 2008 R2. I've several large tables that I am partitioning.
My main question is: Should I use the same Partition Scheme and Function for ...
2
votes
2answers
206 views
MySQL Partitioning
I have a MyISAM table that holds a few million Messages.
I am thinking about converting it to an partition InnoDB table.
Just wondering about the best partition strategy.
I would like to do this ...
2
votes
2answers
146 views
Partitioning on a single filegroup
I have some very large tables in my database, but a substantial chunk of this data is "old".
Due to circumstances beyond my control, I am not allowed to remove this "old" data. The other ...
2
votes
1answer
208 views
Setting up a Postgresql Partition with foreign key
I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed.
I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them.
...
2
votes
1answer
463 views
Rename interval partitioning system generated partition names
I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.
PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1" ...
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 ...
2
votes
3answers
619 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 ...
2
votes
1answer
345 views
PostgreSQL partitioning query performance inexplicably bad
I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap:
evlampts=# explain select max(timestamp) from only mcdata;
...
2
votes
1answer
68 views
Always On and Partition Swapping
Is it possible to use SQL 2012 Always On over two machines and perform partition swapping on the primary machine ?
2
votes
1answer
86 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 ...
2
votes
1answer
201 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 ...
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 ...
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 ...
2
votes
2answers
582 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 ...
2
votes
0answers
37 views
Why is MySQL order by performance poor within a single partition of a partitioned table?
I have to store some sequence numbered data in MySQL. I have about 300,000 data items per day for about a 10 year span. Let's say the table structure is just sequence number (a big int) and data (a ...
2
votes
0answers
607 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 ...
2
votes
1answer
691 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 ...
1
vote
2answers
784 views
Why the query scan all the partitions?
I have the following partitioned table
create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month)
The partition function is
CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
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: ...
1
vote
2answers
293 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
219 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 ...
1
vote
3answers
65 views
Improve performance by partitioning
I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.
...
1
vote
3answers
771 views
SQL Server 2008 Partitioning Evaluation
I'm trying to do a performance comparison of a table with partitioning and the same table without partitioning.
So we have Subcriber and SubscriberPartitioned tables.
Tables structure is:
...
1
vote
1answer
503 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 ...
1
vote
1answer
67 views
Does 'update global indexes' not work if the index is already unusable?
I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with
ALTER TABLE tbl1 TRUNCATE PARTITION p1;
the global index has the status UNUSABLE.
If I ...
1
vote
2answers
212 views
Best option to back up mysql innodb partition
We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link ...
1
vote
1answer
262 views
Triggers not being run on a partitioned table
I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
1
vote
4answers
316 views
Top 2 rows per partition from an absolute value date difference
I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.
Location + Camera + image capture_date is the ...
1
vote
2answers
303 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 ...
1
vote
2answers
92 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?
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 ...
1
vote
1answer
58 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. ...
1
vote
1answer
117 views
Reasons against using local indexes
For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
1
vote
1answer
33 views
SQL Server Key lookup Partition Pruning
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each
SELECT
A.COL1
B.COL2
C.COL3
FROM
A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
...
1
vote
1answer
69 views
Get all partition names for a table
I want to list all the Partitions created by dynamic triggers in Postgresql 9.1
I was able to generate a count of partitions for tables which are partitioned using Frank Heikens' answer in ...
1
vote
1answer
147 views
conversion of high_value to date datatype
I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old.
The code is some what ...
1
vote
1answer
263 views
Indexing issues while partitioning with dbms_redefinition
I get an error while doing the redefinition. Following are the steps which I have taken so far:
Step 1. Start redefinition
DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'T_FDC_TOOLCONTEXT',
...
