I need to insert a new two-field index on an existing table with about 600 million rows in it. The table contains data accumulated over about a year with approximately 2 million new rows added per day.
I know adding a new index to one very large table in one go will take ages, so to save time I started by only indexing the last few days worth of data using the following command…
CREATE INDEX idx_001
ON message (deviceNumber, messageTime)
WHERE messageTime >='07-May-2018 00:00:00' ;
This took about 3 hours, or about 40 minutes to index one day.
Ideally I’d like to index the whole table, but at 40 minutes for a days data doing it on one go will take over a week.
I guess I could create lots of small indexes, each for a few days of data, maybe something like this…
CREATE INDEX idx_002
ON message (deviceNumber, messageTime)
WHERE messageTime <'07-May-2018 00:00:00' AND messageTime >='01-May-2018 00:00:00' ;
But I’d prefer to stick with one index. Is there any way of ALTERing the where clause of the existing index to increase the days covered? Or do I have to create lots of small indexes?
'07-May-2018 00:00:00'
and not using a standard format, like'2018-05-07 00:00:00'
?