|
View previous topic :: View next topic
|
| Author |
Message |
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 131
Location: brisbane
|
|
|
|
I have a table T that I want to index on a date column (ASC) + Instance ID (ASC) to support the primary workload.
The date values will *mostly* be received in ascending order, however we need to allow that some (up to 5 %) will be out of order, by as much as one week.
So, a representative sample might look like:
| Code: |
Received on Date value count(*) Instance Id allocated
1/1/21 1/1/21 1000 1 to 1000
2/1/21 2/1/21 1200 1001 to 2200
3/1/21 3/1/21 800 2201 to 3000
3/1/21 28/12/20 6 3001 to 3006
4/1/21 4/1/21 900 3007 to 3906
|
My question is whether a single clustered index on Date + Instance ID would be sufficient - or - to cater for the number of out-of-sequence date values , should I have Date + Instance ID as non-clustered, and add a clustered index on , say, Month(Date column) - assuming this is supported in db2 ?
so:
option A :
clustered index on Date-col ASC + instance ID ASC
option B :
non clustered index on Date-col ASC + instance ID ASC
clustered index on Month(date-col)
or ?
Option C :
non clustered index on Date-col ASC + instance ID ASC
clustered index on date-column
I expect table T to grow quickly and into the millions. No Update operations on the Date column allowed. Also, (at this stage), no physical deletes allowed |
|
| Back to top |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| If you are not a DBA then please talk to your site DBA. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hey There,
How data is going to be fetched from this table? |
|
| Back to top |
|
|
jzhardy
Active User
Joined: 31 Oct 2006
Posts: 131
Location: brisbane
|
|
|
|
| fetch order is Date DESC, Instance ID DESC |
|
| Back to top |
|
|
|
|
