> - Option to load any partition to cache (heap
> storage engine may not help)
>>Not sure which cache you refer to here?
Load/ keep a copy of any parition in main memory.
Alter table <table_name> change partition <partition_name> cache = Y ( cache =N)
> - Optimization hints for Index usage and other
> select / join operations.
>>Is there anything more than the normal FORCE INDEX you refer to here?
'Select' can span on many partitions, a hint can control how many max/min OS process/thread will be used for the select.
I will list few more hints later...
> - Don't allow to drop all the partitions. Atleast
> the table must have one active partition.
>>Sounds like something which should be configurable since I presume there
are differing requirements here.
Most of the DBAs automate the partitions' rotation and will accidently delete all the partitions. It will break all the applications and stored procedures. If DBA wants to delete all the partitions then he/she will use the 'DROP TABLE..'