5

I just had a situation where I had to include a column in an index. This required dropping the index, and recreating it. This got me thinking, surely that's a lot of unnecessary work.

Say I had instead created the new index first, then dropped the old one. Let's also assume that I have some way of indicating that the old index will not be dropped until after the new one has been created.

Would the server get any performance benefit by using the old index while creating the new one?

  • Which DBMS product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. – a_horse_with_no_name Nov 27 '17 at 21:34
  • Yes, I know. I figured an answer to this question would be database-agnostic – Andrew Williamson Nov 27 '17 at 21:48
  • No it's most definitely not – a_horse_with_no_name Nov 27 '17 at 22:31
  • 2
    I've found some funny behavior with index creation that you might be interested in. – Erik Darling Nov 27 '17 at 23:00
8

You can do

CREATE TABLE T(A INT, B INT, C INT)

INSERT INTO T 
SELECT 1,1,1
FROM master..spt_values

GO

CREATE INDEX ix ON T(A);

GO

--This won't benefit from existing index as noncovering
CREATE INDEX ix ON T(A) INCLUDE (B) WITH(DROP_EXISTING = ON);

GO

--This can benefit from existing index
CREATE INDEX ix ON T(A)  WITH(DROP_EXISTING = ON);

GO

DROP TABLE T;

There is no particular advantage of doing so in the first case.

Whilst the existing index provides the desired sort order it will not be used for this as it does not contain the newly added column B and so it would require a lookup back to the base table for each row. The plan shows a table scan and sort.

enter image description here

The use of DROP_EXISTING here could provide benefit if you were removing an included column though - as in the second DROP_EXISTING example (as the original index would both have the desired order and cover all columns).

The plan for this shows that the original index was read to create the replacement.

enter image description here

  • Ooh neat, I've never used WITH(DROP_EXISTING = ON) before. Does the cost of a row lookup really outweigh the benefit of the index scan (rather than a full table sort)? – Andrew Williamson Nov 27 '17 at 21:55
  • @AndrewWilliamson its the cost of a row lookup for every single row in the table. and yes that is costed as more than a scan and sort. Theres quite a lot of overhead to each lookup even if the pages to be read are already in memory and obv more so if physical IO is required. – Martin Smith Nov 28 '17 at 6:18

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.