I have a report table with the following index:

providerid, date

The table is around 30M records and it grows about 100K rows per day.

I want to use in the index above as a cluster, but as I understand I need to run the cluster table command each time that I want to cluster the data, otherwise new data isn't clustered.

Is there a way to define cluster index like SQL Server so that new rows are also clustered.

I cannot stop all my process each day (cluster needs an exclusive lock).

The query is:

select date,providerid,sum(*) 
  from report_table 
    where data < x 
    and date > x 
    group by date, providerid
  • 1
    AFAIK you can't. I guess you want this because of a performance issue. If you describe your tables and the data in them and the kind of queries you are running on it someone might be able to give an alternate solution. – Eelke May 18 '13 at 13:27
  • 3
    Sounds more like a use case for partitioning rather than the cluster comand. – a_horse_with_no_name May 18 '13 at 14:07
  • @a_horse_with_no_name The table is a report table , so the provider do date range query. In a month he can have 20 millions of records – Avihai Marchiano May 18 '13 at 14:39
  • How can the table have 30 million rows in total yet have 20 million per month? Are only storing one and a half months in there? – a_horse_with_no_name May 18 '13 at 15:32
  • its new table, so only half of month is there – Avihai Marchiano May 18 '13 at 21:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.