Partitioning on data: Without storing data in table.
================================
QUESTION = CAN I USE PARTITIOINING ?
================================
I DON'T WANT TO STORE "POINTID" OR "DATE" INSIDE TABEL(to reduce storage size).
I WANT TO MARK THE PARTIOINS WITH "POINTID" AND CREATE SUB-PARTIOIN INSIDE PARTITION, TO BE MARKED WITH "DATE", WITHOUT STORING POINTID & DATE IN TABLE.
I'm having data 100,000 points data to be stored every Second as below, (All points data is recieved sequentially after every second )
Please let me know, how to define smallest storage for data below with possible efficient retrival time
================================
PointID DataTime 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
=========
SOLUTION 1:
=========
If i define 100,000 tables (One for each PointID), I can save 3 bytes Bcz i don't have to save PointID for any record.
=========
Solution 2:
=========
I can generate new ID by combining 2 colums (PointID + Date = PointDateID). I have to generate 100,000 new IDs every day and save them in new table
================================
PointID Date PointDateID
================================
1 2006-12-28 1
2 2006-12-28 2
3 2006-12-28 3
.....
100,000 2006-12-28 100,000 (Note:Date Changed in rows below)
1 2006-12-29 100,001
2 2006-12-29 100,002
3 2006-12-29 100,003
.....
100,000 2006-12-29 200,000.......
==========================
PointDateID Time Value
==========================
1 00:00:01 101
1 00:00:02 105
1 00:00:03 199
2 00:00:01 131
2 00:00:02 135
2 00:00:03 149
.....
100,000 00:00:01 121
100,000 00:00:02 131
100,000 00:00:01 141 .....