locked
why reorganize index is quicker than rebuild index ? RRS feed

  • Question

  • can some one explain me breifly.

     

    Thanks & Regards

    Ramakrishna K

     

    Sunday, November 27, 2011 9:02 PM

Answers

  • REORGANIZE is necessarily not quicker, but it may be in several situations.

    REBUILD rebuilds the index from scratch. Resorts the data, and makes a full update of statistics, since it is read all the data. By default REBUILD is an offline operation (the table is unavailable), but it can be an online operation in Enterprise edition.

    REORGANIZE shuffles around the index pages page by page, and only looks the pages it is working with. Therefore it is always online. It may performs less work: if the index suffers from a lot of unused space, that will not change with REORGANIZE, but REBUILD addresses that.

    For more information, I suggest that you look through Paul Randall's blog at www.sqlskills.com


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 27, 2011 9:43 PM

All replies

  • REORGANIZE is necessarily not quicker, but it may be in several situations.

    REBUILD rebuilds the index from scratch. Resorts the data, and makes a full update of statistics, since it is read all the data. By default REBUILD is an offline operation (the table is unavailable), but it can be an online operation in Enterprise edition.

    REORGANIZE shuffles around the index pages page by page, and only looks the pages it is working with. Therefore it is always online. It may performs less work: if the index suffers from a lot of unused space, that will not change with REORGANIZE, but REBUILD addresses that.

    For more information, I suggest that you look through Paul Randall's blog at www.sqlskills.com


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 27, 2011 9:43 PM
  • Thanks Erland..

     

    Thanks

    Ramakrishna.K

    Monday, November 28, 2011 8:04 AM