1

Let's say you have a table of invoices

CREATE TABLE Invoice
(  InvoiceID int IDENTITY(1,1) PRIMARY KEY,
   InvoiceDate DateTime CONSTRAINT [df_invoicedate] DEFAULT GETDATE(),
   ....
)

The invoice table has an InvoiceDate which is always the current date/time when the record is inserted.

Let's say you index this date

CREATE INDEX idx_InvoiceDate ON Invoice(InvoiceDate)

Note this is a non-clustered index (held in a separate file).

Let's say we know the RDBMS uses B+tree format for index files.

Bearing in mind that all inserts will hit the same page of the index file (because they are always at the end of the sort order), and that inserts into a single location are worst-case for B+tree insert, what special considerations should be observed?

Will this index end up lopsided? Should it be reindexed regularly? Should I use a low, normal, or high fill factor?

Should we try to find something else to put into the index prior to the date, e.g. an invoice type, to get the tree to balance out more evenly?

Will there be issues with hotspots or index page contention, because all inserts hits the same part of the index? What can I do to mitigate that?

I understand that for an ascending surrogate key, a reverse key index might be better. But this is a date and I may want to perform scans of a date range. Is there anything I can do that will alleviate any of the issues that reverse key indices are meant to address?

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?

  • How many writes per second do you expect? What's the spec. of the server hardware? This is a very common scenario and even a modest platform will be able to support many hundreds of inserts per second. – Michael Green Oct 21 '16 at 3:37
  • Your example DDL is clearly for SQL server, but to my knowledge SQL Server does not have "reverse indexes" - only Oracle does. The question on now to define and maintain indexes highly depends on the DBMS being used. Will frequent re-indexing is required in SQL Server, this is a total waste of resources in Oracle. And not all DBMS do have non-clustered indexes. For some that is the default for e.g. the primary key for others it's not. – a_horse_with_no_name Oct 21 '16 at 11:16
0

Will this index end up lopsided?
No. The way the BTree algorithm works keeps it balanced. (One interpretation of the "B" in BTree is for "balanced".) As leaf pages fill they cause parent pages to split, with half the rows going to each new parent. Further rows cause further splits, which cascade up the tree until the root has to split, at which point a new root page is created. At every stage all paths from root to leaf have the same length and hence "balanced".

Should it be reindexed regularly?
Yes. Each DBMS has its own idiosynchracies but likely regular re-build of indexes will be recommended. If for no other reason, the statistics will get out of date eventually which will result in less efficient query plans.

Should I use a low, normal, or high fill factor?
As all writes will be on the right-most pages leaving free space anywhere else will be a waste. There will never be any writes to use up that free space. Use a fill factor of 100%.

Should we try to find something else to put into the index
Indexes aren't (generally speaking) created for entertainment purposes. They're there to improve query performance or support constraint enforcement. If your workload would benefit from a <something> + date index then create that. If not, don't. Indexes require extra work during writes and consume space and maintenance time. Create the ones you need and no more. And no less. Keep in mind filtered indexes and included columns. Bear in mind that optimiser math may mean your indexes aren't used anyway.

Will there be issues with hotspots or index page contention .. What can I do to mitigate that?
Well, yes, in theory. First test and prove you'll have a problem meeting your expectied transaction-per-second count. If you can't, are you sure it's this index that's the limiting factor? If it is, in-memory tables and snapshot isolation may implement concurrency mechanisms different to the "base" system's, depending on DBMS. There may be alternative storage engines whose characteristics differ. Try those to see if the pain receeds. You can always post a follow-up question here with quantatative observations!

I understand that for an ascending surrogate key..
Surrogate keys are made-up values that replace human-intelligible natural keys. They're used for reasons releated to DBMS implementation peculiarities and are not inherent to the relational model. Your date is not a surrogate key - it is business data.

a reverse key index might be better.
Hmmm .. OK, so instead of writing all today's rows to key value 20161021 you'd write them to .. 12016102? How does that help? Hashing won't help either: to use the hashed values for lookup each date would have to produce the same hash so there's still a hotspot.

Or is all of this a total non-issue, and indices of this kind are fine and I should stop worrying about it?
Probably. The vast majority of indexes on most systems are like this. By-and-large they work just fine and the DBMS is able to process significant workloads on modest hardware. Indexing is one of those things you can (and should) tweak ad nauseam after go-live. Concentrate on delivering a normalised, feature-rich, debugged system. Tune it afterwards.

  • "Should it be reindexed regularly?" - that's a definite NO for Oracle. SQL Server on the other hand does seem to require that. I also don't know of any DBMS that requires re-indexing to update statistics. All majro DBMS have a specific command to update them without the need to re-index. Also: a "reverse index" in Oracle does help to avoid contention when inserting with a high rate with constantly increasing values (typically: IDs generated by a sequence). – a_horse_with_no_name Oct 21 '16 at 11:13
  • Fair point; reindex will recalculate stats but is not the only way to recalculate stats. With a surrogate key where every row has a different value the reverse technique will remove the hotspot at the cost of low fill factor or fragmentation. For datetime type (with 3ms resolution in SQL Server) operating at 300-ish tps will cause concurrent writes to leaf pages. So, depends on load and impact. – Michael Green Oct 21 '16 at 11:30

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.