Splitting a database table into multiple segments for performance or manageability.
1
vote
0answers
17 views
MySQL Partitioning Performance
I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM.
I am unable to index ...
0
votes
0answers
14 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
424 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:
...
4
votes
2answers
81 views
Partitioning and freeing disk space
I am running MySql innodb with innodb_file_per_table.
As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
-2
votes
0answers
43 views
Unexpected index scan against partitioned table [closed]
I have a set of partitioned tables with CIX on (ID, DATE) and partitioned on DATE.
There is another identical set of non-partitioned tables with CIX on ID.
Case A: T1 and T2 are non-partitioned ...
0
votes
2answers
87 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 ...
1
vote
1answer
41 views
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
3answers
147 views
Performance issues moving 1 million rows between tables
I want to replicate 1 million rows from table1 to table2. I have written a job with a query like this:
delete from table1 OUTPUT *.delete into table2
I am executing this job every hour, but it ...
0
votes
0answers
39 views
When to split a large table
I run the SQL Anywhere DBMS, and we have a table of about 10 columns(normal text, timestamps etc).
However, in my use-case, every single day, the table gets 2160000 new rows.
It's also crucial that ...
5
votes
1answer
221 views
effective mysql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried
I am looking for advice on table/index design for the following situation:
i have a large table (stock price history data, InnoDB, 35 million rows and growing) with a compound primary key (assetid ...
3
votes
1answer
58 views
Table partitioning filegroup
Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I ...
1
vote
3answers
71 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.
...
0
votes
0answers
24 views
MySQL poor order by performance on partitioned table [duplicate]
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
39 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 ...
0
votes
0answers
32 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 ...
1
vote
1answer
39 views
Archive partition before delete it?
I have manage to create an event and with the following scripts could both create and delete the partition. The issue now I need help on how to save the partition before its deleted? Should I use ...
1
vote
1answer
75 views
MySQL partitioned tables?
I have a database that supports a web application with several large tables. I'm wondering if partitioned tables will help speed up certain queries. Each of these tables has a colum called ...
0
votes
1answer
32 views
Add partition works but not the drop partition
We have set an event as below. What we notice is that the add partition is working well as we can see on a daily basis the partition list is growing but the drop partition is not working well any ...
0
votes
1answer
60 views
MySQL Read Speed and Partitioning on Separate Drives
Introduction
Simple project but for an heavy read load -90% reads- over a table(~20Milion Rows ever-growing where I have to boost performance and ensure response low time for read queries) that was ...
1
vote
1answer
75 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
3answers
802 views
Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?
I have some tables that many users need to access to:
mysql> show create table v3_cam_date\G
*************************** 1. row ***************************
Table: v3_cam_date
Create Table: ...
5
votes
2answers
254 views
Recreate Indexes on 1 billion record table
I have a table with over 1 billion records and it has 6 indexes (including Clustered index (ID)). I need to partition this table on a new Clustered index with date column. I have just enough space ...
0
votes
0answers
50 views
MySQL Partitions
I've done a lot of work with MySQL but generally on a much smaller scale than this, so please excuse my ignorance.
We've got an InnoDB table which is currently storing ~3.5 million rows, which I know ...
1
vote
1answer
76 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 ...
2
votes
1answer
117 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 ...
5
votes
2answers
634 views
Making the Oracle optimizer use a virtual column to find out about a partition
I have a large table that has three columns like this:
"START_DATE" DATE,
"START_VALUE" NUMBER(10,7)
"START_DATE_VALUE" NUMBER(18,7)
GENERATED ALWAYS AS
(
(extract(YEAR FROM START_DATE) * ...
1
vote
0answers
56 views
How to make Greenplum 4.2.3 only scan the intended partition?
When I uses unnest() in a View, and uses that View in a select statement, Greenplum seems to fail in only searching for the intended partition and search through all the partition of the main table ...
3
votes
0answers
111 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 ...
1
vote
1answer
100 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 ...
0
votes
0answers
26 views
MySQL partitioning by customer
We have a product that uses different MySQL schemas for different customers, and a single Java application that uses different persistence units for one for every customer. This makes it difficult to ...
2
votes
2answers
155 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 ...
0
votes
1answer
258 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
534 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 ...
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
166 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 ...
0
votes
1answer
204 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
0answers
81 views
Renaming the system generated partitions during interval partitioning
I am trying to rename the system genrated partition names(generated during interval partitioning). I try to do this while my table is live(data read and write is happening).
I need two scripts.
I ...
0
votes
2answers
70 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
116 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 ...
2
votes
1answer
244 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.
...
0
votes
0answers
44 views
Partitioning for queue tables
I have a queue table where entries are added by a service with the WAITING status and a due date, which may be seconds to weeks from now. Those due entries are consumed and updated with the PENDING ...
0
votes
0answers
27 views
How Best to Parition Temporal Data for Access By Date
The latest edition of the High Performance MySQL book has this to say about paritioning a large table where all the hot data is clustered temporally:
Suppose you have a table with an ...
0
votes
0answers
75 views
XML errors while copying dependents during a redefinition to an interval partitioning scheme
I am partitioning an existing table. I used the following process:
Checks
DBMS_REDEFINITION.CAN_REDEF_TABLE(...);
Successful
Create duplicate table
CREATE TABLE "USER"."MYTABLE_ONLINE"
( ...
2
votes
1answer
484 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" ...
5
votes
2answers
203 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
2answers
220 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 ...
0
votes
2answers
88 views
Best Way to Partition By City / State in MySQL 5.5
I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:
ALTER TABLE ...
1
vote
1answer
278 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',
...
1
vote
1answer
52 views
Unique Non Clustered Column in Partitioned table
We have partitioned one of our SQL Server tables on a Clustered Index datetime column, we have a Primary key Non-clustered index column with Identity data type and this column is setup as a ...
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 ?
