MySQL Forums
Forum List  »  Partitioning

Subpartitioning on number of rows in partition(column) --AND-- Aggregate Query Execution
Posted by: Ijaz Rashid
Date: January 04, 2007 05:32AM

===========
Question 1:
===========
How to perform subpartitioning on size (# of rows) in partition (column). I.E. WHEN NUMBER OF RECORDS FOR "ID=100" EXCEED 1 MILLION, CREATE NEW SUBPARTITION AND INSERT ROWS IN THIS PARTITION, So on....

=========
Detail:
=========
Drop table if exists XA;

CREATE TABLE XA(ID smallint NOT NULL, LogTime timestamp NOT NULL, Value smallint NOT NULL) ENGINE=ARCHIVE
PARTITION BY RANGE(ID)
SUBPARTITION BY HASH(month(LogTime))
(
PARTITION p0 VALUES LESS THAN (1)
(
SUBPARTITION s0,
SUBPARTITION s1
)
);


I'm having one table on which I've defined partition on column "ID" and subpartionion on "HASH(month(LogTime))".

I WANT TO DEFINE SUBPARTIOIN ON SIZE (NO. OF ROWS) OF PARTITIONED COLUMN. I.E. WHEN NUMBER OF RECORDS FOR "ID=100" EXCEED 1 MILLION, CREATE NEW SUBPARTITION AND INSERT ROWS IN THIS PARTITION, So on....


My table contains 100,000 "IDs" and billions of records.
================================
ID LogTime Value
================================
1 2006-12-28 00:00:01 100
1 2006-12-28 00:00:02 101
1 2006-12-28 00:00:03 105
2 2006-12-28 00:00:01 150
2 2006-12-28 00:00:02 151
2 2006-12-28 00:00:03 155
.....
100,000 2006-12-28 00:00:01 180
100,000 2006-12-28 00:00:02 182
100,000 2006-12-28 00:00:03 194

==========
Question 2:
==========
Should i use BULK INSERT to insert 100,000 records per second in my table ?

===========
Question 3:
===========
Subpartition should support aggregate queries e.g.

SELECT SUM(VALUE), AVERAGE (VALUE)
FROM XA
WHERE tagid in (1,2,3,4,5) AND
time >= '2006-12-10 10:10:10'
time <= '2007-01-09 10:10:09'
GROUP BY tagid;


(Thank you for help).

Options: ReplyQuote


Subject
Views
Written By
Posted
Subpartitioning on number of rows in partition(column) --AND-- Aggregate Query Execution
4803
January 04, 2007 05:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.