Hi omniSci Team,
In OmniSci Database, most of the time i am facing challenge and restricting to utilize for use case due to one Limitation and that is :: Unavailability of Table Partition option (with feature) on Date Field.
I think making Table partition option on Date field (with features) can make OMNISCI Database extremely useful and efficient for many many more use cases.
Let me try to explain
Data Deletion : As objective to 30 Days records. Hence would like to drop any Data which is older than 30 days. Now I am using Delete command but this will not clear OS level disk Space. If Daily Partition option is available, can drop daily Partition which will be less costly query and release OS disk Space.
Data Insertion : I am using Kafka Importer for data Loading. As would like to make data availability quickly so using the small Batch Size (–batch). This create lots of small Chunks of 16MB. Lets assume it create 1000 Blocks of 16MB per day. If Daily Partition Merge Block feature is available, can run Merge Block query next day to rearrange data for a Day partition and may be Data Blocks will reduce to 10 or 50 Blocks. This will improve Database performance and speed up Database restart.
Backup & Restore : Lets assume Daily Record count is 100 Million and would like to keep 30 days data. If Daily Partition option is available then only need to take 100 Million record Backup and restore. Taking 3 Billion record Backup and Restore is not efficient and time consuming job as Failure Chances are high.
Efficient Utilization of GPU and CPU Memory : Based on Query where Clause (if Date Field included), Optimizer can load required Partition in Memory and utilize memory more efficiently.
In above, i always mentioned Date partition (with Features), (With Features) denotes
a) Drop partition
b) Copy a partition
c) Backup a partition
d) Restore a partition
e) Merge DB Blocks for a partition
f) CTAS from Partition
g) Load Partition in Memory based on where Clause (if date field Included)