The database-partitioning tag has no wiki summary.
1
vote
3answers
122 views
+100
Storing changes on entities: Is MySQL the proper solution?
i want to store changes that i do on my "entity" table. This should be like a log. Currently it is implemented with this table in MySQL:
CREATE TABLE `entitychange` (
`id` int(11) unsigned NOT NULL ...
-3
votes
1answer
32 views
SQL Server Partitioining on Date field [closed]
Need help - How to create partition in SQL server which will held 2011, 2012, 2013 data in separate file groups.
I'm struggling to understand between RANGE RIGHT or RANGE LEFT....
0
votes
0answers
15 views
How to partition a very large table? [migrated]
I have a very big table. I want to partition it, but I cannot.
Database size: 1TB, Free space 200GB
Table:
Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data.
...
0
votes
0answers
21 views
How to create a partition for mysql database?
I have questions about partition my MySQL tables. I have a very large table with 5 Million Records. It is a transnational database so this table is going to keep incriminating. So I am thinking to add ...
0
votes
1answer
28 views
how to use vars in the command “create partition” with mysql
I want to create an event that every 15 days executes a procedure. This procedure must to create a new partition. I am doing the following:
/*Vars
v_tab --> logic tables's name
v_year --> ...
0
votes
0answers
28 views
postgreSQL get last ID in partitioned tables /
my question is basically the same as this one, but i couldn't find an answer, its also written "to be solved in the next release" and "easy for min/max scans"
PostgreSQL+table partitioning: ...
0
votes
1answer
38 views
Postgres: number of rows inserted into table partitioned using 'on insert … do instead' rules
Looking into http://www.postgresql.org/docs/9.2/static/rules-status.html I'm under impression that to Postgres will return correct number of inserted rows if in addition to non-overlapping rules you ...
0
votes
1answer
36 views
MySQL performance with large number of records - partitioning?
I am trying to build a database that would contain a large number of records, each with a lot of columns(fields) - maybe around 200-300 fields total for all tables. Let's say that I would have in a ...
0
votes
0answers
22 views
Script SQL Server Database without partitions
I've got a SQL Server 2008 R2 database that has several tables which have been partitioned. I need to get a new, empty version of the database, but without the partitions. I know I can generate a ...
0
votes
1answer
62 views
Dump CSV into Postgresql table
below is my whole procedure to dum data from a file to POSTGRESQL-8.0 on RedHAt-7.2
now my issues is for every ROW the trigger is getting called and the procedure is executing .
I want to do is once ...
0
votes
1answer
28 views
Mysql puts all data in one partition
I've a 30M rows table and I want to partition it by dates.
mysql > SHOW CREATE TABLE `parameters`
CREATE TABLE `parameters` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`add_time` ...
3
votes
2answers
134 views
PostgreSQL partition query by date optimization
We have a table that has approximately one billion records per month. Considering 18 months of history we are talking about 18 billion records.
This table is partitioned weekly by date (so we have ...
0
votes
0answers
25 views
from where i will get vertically partitioned dataset for mining association rules?
I am working on project privacy preserving data mining for association rules, for that I require sample vertically partitioned binary data set. can anyone help me?
0
votes
0answers
52 views
Does Amazon RDS Oracle support partition or not?
Recently I'm evaluating the feasibility to migrate current application to Amazon RDS Oracle, since the scale of the data becomes larger and larger.
I want to know whether Amazon RDS Oracle could ...
1
vote
1answer
112 views
Postgres partition management with triggers. Issue altering tables
SHORT STORY:
I have a partitioned postgres database with a table to track the partitions and triggers. The triggers need to alter the constraints on the partition tables (their valid_date ...
1
vote
1answer
105 views
How do I create a partition for every month of the current year
I want to partition my data by "MONTH"
I am trying this,
ALTER TABLE t1 PARTITION BY RANGE(TO_DAYS(FROM_UNIXTIME(transaction_date)))(
PARTITION JAN VALUES LESS THAN (TO_DAYS('2013-02-01')),
...
0
votes
1answer
19 views
MySQL partitioning considerations
I have a table that will eventually have about 1.4 billion records in it, 11 columns, 3 primary keys.
I've populated about the first 1/3 of the table and the query times are already quite slow. I've ...
0
votes
2answers
120 views
JPA multiple databases - simple example / best pattern?
I have 2 mySql databases that I'm trying use in a simple Java EE Web Application. To simplify the scenario, the schemas are identical, and for example lets take the Customer table in each.
I'm ...
0
votes
1answer
32 views
How to partition by mysql table into two layers?
I want to partition a table this code will show you the structure of the table. the table currently have about 5 million records.
I need the MySql partition syntax this table like so
Main partition ...
0
votes
0answers
39 views
How to partition a mysql table after the table is already created
I am trying to partition my table so I can narrow down the record so accessing data won't take as long as it is taking now.
this table that I want to partition has 2 key fields
(1) 'tigger_on' which ...
0
votes
1answer
35 views
Sharding schemes
I am looking for some sharding schemes, and I also need some literature on that. So far the ones I have found:
- Key based partitioning
- Vertical partitioning
- Directory based partitioning
Any ...
1
vote
1answer
91 views
How to partition MySQL table with two indexes
I have a table game_log with fields id, game_id and several varchar fields.
id is primary key and game_id is non-unique key.
There're two frequent queries:
SELECT * FROM game_log ORDER BY id DESC ...
0
votes
2answers
22 views
Having partitiion on all distinct values where new value can come
I have an use case where i need to partition an Oracle table based on city. Now I do not know the city beforehand. So in this case how will i create the partition. (Btw I have already done hash ...
0
votes
0answers
44 views
how can i convert unix timstamp in to the corresponding integer (BIGINT) value
I'm trying to create a partition table based on the column which has the unixtimestamp in number format.
CREATE TABLE TABLE_NAME (
TIME_CREATED number, // it has unix timestamp as number
ID ...
0
votes
2answers
31 views
Understanding the “P” in “CAP” with DDBMSes
In the CAP Theorem, the "P" (Partitioning) component essentially states that the system works well despite physical network partitions.
I guess the "C" (Consistency) and "A" (Availability) make ...
0
votes
1answer
50 views
On MySQL 5.1, how can I query a specific partition?
I need to run a query on a partitioned table, and I'd like a result by partition, like so:
partition result count
----------- ---------- ------
p1 apples 2
p1 ...
0
votes
3answers
116 views
Table scaling with partitions or with separate databases?
Let's say I have a table (let's call it BigTable) which could experience 5,000,000 INSERTS per day (with possibly just as many SELECTs). Each row inserted is about 50kb.
These daily INSERTs are ...
azure
1
vote
2answers
179 views
how to drop partition without dropping data in MySQL?
I have a table like:
create table registrations(
id int not null auto_increment primary key,
name varchar(50),
mobile_number varchar(13))
engine=innodb
partition by range(id) (
partition p0 values ...
0
votes
1answer
154 views
Automatic Partioning in Oracle11g
I have a table with a huge volume of data. I need partioning to be done on a daily basis automatically. I need the name of the partition to be the date of sysdate. How can I go about doing this?
1
vote
3answers
153 views
Selecting from mySQL partition
I made partition my 300MB table and trying to make select query from p0 partition with this command;
SELECT * FROM employees PARTITION (p0);
But i am getting ERROR 1064 (42000): You have an error ...
3
votes
1answer
582 views
Optimize Postgres timestamp query range
I have the following table and indices defined:
CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
eid bigint,
created timestamp with time zone NOT NULL ...
1
vote
0answers
89 views
Handling ID in a SQL Server Standard edition partitioning situation using views
Short backgroung
Hello every body. I am currently facing a situation where I need to increase performance on a very large table on SQL Server standard edition. The table is transaction heavy and ...
0
votes
3answers
301 views
how to find partition of record oracle
I have a table and there is a partition on it.
There are 16 hash partition which is starting from SUBSCRIBER_01 .. etc
Table name: SUBSCRIBER
Partition Column: CUSTOMER_ID (VARCHAR2 10)
Database ...
11
votes
3answers
627 views
Mysql 5.5 Table partition user and friends
I have two tables in my db that have millions of rows now, the selection and insertion is getting slower and slower.
I am using spring+hibernate+mysql 5.5 and read about the sharding as well as ...
0
votes
0answers
84 views
MySQL partitioning with binary data and string functions
I have a slight dilemma that I believe I've solved, but I could use some feedback and maybe some other options.
I'm trying to partition a table in MySQL based on only a portion of my primary key. ...
0
votes
2answers
210 views
Range partition table creation with large number of paritions
i have to create an range partitioned table with two hundred partitions. for eg:
CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(30),
sal NUMBER
)
PARTITION BY RANGE(empno) (
...
1
vote
1answer
54 views
Mysql Subpartioning --Partition by day and then a value
I have a table on a Linux Mysql server running 5.5.24 that looks like this:
CREATE TABLE Level (
snr float DEFAULT NULL,
checkDate datetime DEFAULT NULL,
id bigint(20) unsigned ...
3
votes
1answer
126 views
horizontal partitioning a mysql table with two indices
I have a mysql table that stores user ratings for different items. It has the following fields:
id (int, pk)
userId (int)
itemId (int)
rating (float)
timestamp (int)
and following indices:
...
0
votes
1answer
269 views
select max of a column using range partition
I have a Table which is range partitioned on numeric(parameterinstanceid) value. I want to select max+1 value of the same column .i have global non-partition index on parameterinstanceid.
select /*+ ...
0
votes
2answers
167 views
What is the value of maxvalue in partition
How can I know the exact value of maxvalue in high_value in partition?
PARTITION "PARTMAXVALUE" VALUES LESS THAN (MAXVALUE)
Also if the table has already a partition created where high value is ...
0
votes
1answer
74 views
MySQL DB: performance and partitioning
I've migrate an access DB to a MySQL DB. In particular, in this DB I've a table with almost 5 million of rows. The most part of operations on this table are queries (filters to select a data subset).
...
0
votes
2answers
94 views
MySQL Partition pruning on variable
I am trying to optimize a query that is taking almost 30 minutes to run. What I am trying to do is to take advantage of partition pruning to minimize the rows searched. The range of the variable by ...
1
vote
2answers
377 views
Mysql 5.6 Partitioning on timestamp (with microseconds)
I am playing around with mysql 5.6.
Im especially interested in the possibility:
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) ...
0
votes
3answers
554 views
SQL Server Scripting Partitioning
Had a good look on the net and books online and couldn't find an answer to my question, so here goes.
Working on someone else's design, I have several tables all tied to the same partition schema ...
2
votes
1answer
215 views
How to partition and subpartition MySQL by key?
I want to add partition to my innoDB table. I have tried to search the syntax for this, but have not found specifics.
Is this syntax wrong? :
ALTER TABLE Product PARTITION BY HASH(catetoryID1) ...
0
votes
2answers
259 views
can i avoid creation of a foreign key constraint for a one-to-one relationship?
i want to use eclipselink to partition my database. for performance reasons i will have one table (entity A) replicated to all nodes and one table (entity B) that is hash partitioned over all nodes.
...
0
votes
1answer
343 views
MySQL ALTER TABLE with subpartition fails
I'm trying to alter an existing table to add year and week subpartitions, like so:
CREATE TABLE test_table(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
dtime DATETIME NOT NULL);
ALTER TABLE ...
4
votes
3answers
2k views
Oracle: how to drop a subpartition of a specific partition
I am using an oracle 11 table with interval partitioning and list subpartitioning like this (simplified):
CREATE TABLE LOG
(
ID NUMBER(15, 0) NOT NULL PRIMARY KEY
, MSG_TIME DATE NOT NULL
, MSG_NR ...
0
votes
2answers
67 views
SQL Server partition unnormalized data
I think there might be a way to partition this to find what I want, but I can't see it. I have some slightly unnormalized data about product sets:
SetItemID ComponentItemID quantity IsPrimary
...
0
votes
1answer
136 views
Partitioning and Sequence Increment
I have table call it scdr_buz and have partitioned it on monthly basis, I have created trigger on insert which take care of upsert and create table if not present then upsert. I have sequence ...
