Splitting a database table into multiple segments for performance or manageability.
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 ...
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 ...
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 ...
1
vote
1answer
523 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
1answer
179 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 ...
1
vote
1answer
478 views
How many files should I create for one filegroup and 9 weekly partitions?
I have 9 weekly partitions on one filegroup. How many files do I need to create?
There are 3 tables containing 46 million, 16 million and 10 million rows.
1
vote
1answer
248 views
Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema
I have this table definition:
CREATE TABLE `va_ventas` (
`ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Date` DATE NOT NULL,
`Time` TIME NULL DEFAULT NULL,
`Country` VARCHAR(3) NULL DEFAULT NULL,
...
1
vote
2answers
302 views
Create multiple files for each file group for table partitioning?
I am partitioning a table by date (month). The server has four drives. The partition scheme is defined as
CREATE PARTITION SCHEME ByDate AS PARTITION ByDate TO (
[PRIMARY], [fg2], [fg3], [fg4],
...
1
vote
0answers
13 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 ...
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
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 ...
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 ...
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 ...
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 ...
1
vote
0answers
48 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 ...
1
vote
0answers
192 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 ...
1
vote
2answers
83 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
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 ...
1
vote
3answers
801 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: ...
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 ...
0
votes
2answers
155 views
Inputs on handling large volumes of data for specific tables in SQL Server standard
I have large OLTP ERP database with 10 million records in few tables. The database size is as large as 10GB.
I have few critical transaction tables like this:
1. Payments
2. Cheques etc.,
and each of ...
0
votes
1answer
386 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 ...
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 ...
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 ...
0
votes
1answer
267 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 ...
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.
...
0
votes
1answer
1k views
Reindexing Partitioned Table Indexes
Historic Reporting Server running SQL 2008 R2.
I'm trying to tweak the fill factor. I have a job that records the fragmentation when the jobs runs in the early hours.
For every table that had high ...
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 ...
0
votes
1answer
106 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
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 ...
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 ...
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 ...
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
2answers
94 views
shall I use multiple separete tables or one table with multiple partition on oracle
I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
0
votes
1answer
155 views
MySql - Global index
I'm new to MySql partitioning and have a question regarding indexes.
let say i have the following table:
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
...
0
votes
1answer
691 views
oracle global index vs local index on a partitioned table
I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
0
votes
1answer
151 views
Multiple Partition Keys?
This is a follow-on from a previous question.
I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is ...
0
votes
0answers
13 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
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 ...
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
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 ...
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
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 ...
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 ...
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
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 ...
