1

I have a massive (3,000,000,000 rows) fact table in a datawarehouse star schema. The table is partitioned on the date key.

I would like to add an index on one of the foreign keys. This is to allow me to identify and remove childless rows in a large dimension table.

If I just issue a CREATE INDEX statement then it would take forever.

Do any SQL gurus have any fancy techniques for this problem?

(SQL 2008)

--Simplified example...

CREATE TABLE FactRisk
(
  DateId int not null,
  TradeId int not null,
  Amount decimal not null 
)

--I want to create this index, but the straightforward way will take forever...

CREATE NONCLUSTERED INDEX IX_FactRisk_TradeId on FactRisk (TradeId)
NeedHack
  • 2,943
  • 3
  • 30
  • 44
  • 2
    Its a large table so its always going to take a long time - I think the best you can really hope for is that the table remains functional while the index is being created. – Justin Jul 01 '11 at 10:07

1 Answers1

0

I have a plan...

  1. Switch out all the daily partitions to tables
  2. Index the now empty fact table
  3. Index the individual partition
  4. Switch all the partitions back in

Initial investigation implies that this will work. I will report back...

NeedHack
  • 2,943
  • 3
  • 30
  • 44
  • As it turns out creating the index "with online" was good enough, and I didn't have to take it all to bits and try to put it back together again. – NeedHack Jun 28 '12 at 09:40