Re: partition by range on decimal type

[Greg K]

Hi Rick, thank you for all your hints.

I want to use paritioning to speed up the queries against the table(s) with WHERE clause including the starttime column. The production table(s) are much bigger than this (10x) and query like:

SELECT bts_name, str_to_date(convert(STARTTIME, CHAR(10)), '%Y%m%d%H') as Date, 2G_CSSR_Ph1_den
FROM BTS_HOUR
WHERE STARTTIME>=2012110900 and STARTTIME<=2012111623
AND bts_name IN ('BTEAG0716A', 'BTEAG0716B', 'BTEAG0716C')
ORDER BY bts_name, starttime;

takes long time.

I'm not the architect of this schema, so I cannot modify the table structure much (e.g. cannot add new column 'hr' or shrink), apart from adding/removing indexes or partitioning.
One developer tried to add index and it hanged (due to huge table's size) so he had to break the process to release the lock.
Then I thought than applying a partitioning will make it and be less stressful (however it is probably not concluding from tests) and playing with it as you see.

The dropping of partitions is not the case here, only performance.
Would you recommend partitioning (what?) or maybe a right indexing is enough?