Splitting a database table into multiple segments for performance or manageability.
0
votes
0answers
25 views
Find the partition where data lives in MySQL
Is there a way to select the partition name where a specified record actually lives in? The idea is something like:
SELECT <PARTITION_NAME> FROM mytable WHERE <where_condition>
I found ...
1
vote
0answers
28 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
1answer
33 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 ...
-2
votes
0answers
45 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
0answers
42 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 ...
3
votes
1answer
59 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 ...
4
votes
2answers
86 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 ...
1
vote
3answers
72 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.
...
2
votes
0answers
41 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
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 ...
0
votes
1answer
42 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 ...
0
votes
2answers
91 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 ...
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
3answers
155 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 ...
1
vote
1answer
43 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
1answer
77 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
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 ...
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 ...
5
votes
2answers
258 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 ...
5
votes
1answer
229 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 ...
1
vote
1answer
79 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
122 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 ...
1
vote
0answers
57 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
115 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
3answers
866 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: ...
2
votes
2answers
158 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
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 ...
1
vote
1answer
180 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
76 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
0answers
82 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
72 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
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
28 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
262 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
503 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" ...
0
votes
1answer
120 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 ...
1
vote
1answer
287 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',
...
2
votes
1answer
63 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 ...
1
vote
2answers
229 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 ...
2
votes
1answer
69 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
3answers
248 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 ...
0
votes
1answer
107 views
Where is my old partition in mysql show create table?
I am keep adding daily base partition for a table as e.g.
ALTER TABLE tblData PARTITION BY RANGE (to_days(`dataInsertDateTime`)) (
PARTITION p20 VALUES LESS THAN (to_days('2012-12-25')),
PARTITION ...
0
votes
2answers
90 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
0answers
70 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 ...
4
votes
1answer
77 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 ...
8
votes
1answer
250 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 ...
2
votes
1answer
789 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 ...
