4 Replies Latest reply: May 10, 2011 12:45 AM by 698658 RSS

    Does delete rows improve index range scan performance?

    698658
      Hi,
      let say I'm on 9.2.0.8 and got table with few indexes .
      Now I delete 90% rows and DO NOT rebuild table indexes nor coalesce indexes .
      In that case does index range scans using queries perform faster (less i/o) ?
      Please explain Your answer .
      Regards
      GregG
        • 1. Re: Does delete rows improve index range scan performance?
          sb92075
          In that case does index range scans using queries perform faster (less i/o) ?
          Faster? Faster than what? Faster than a speeding bullet?
          Less I/O? Less than what?

          What prevents YOU from actually testing on your own DB to answer this mystery?
          • 2. Re: Does delete rows improve index range scan performance?
            698658
            I'm at home . :)
            Faster than before delete.
            Regards .
            GregG
            • 3. Re: Does delete rows improve index range scan performance?
              Iordan Iotzov
              I’ve got two points (one of them quite obvious):
                   It depends on what you deleted and the index you are using. If the number of records for the key that you using in your search has not changed, even if the table is 10 times smaller, the index scan would consume the same resources. Let say we have a table with an index on a date column. The table contains information since 2001 (2001-2011). You delete everything before May, 2010. If you search for a date in 2011 you would not get significant performance benefit from the delete, but if you search for a date in 2003 you would get significant performance benefits
                   It depends on the clustering factor of the index:
              o     High clustering factor indicates that data is almost randomly scattered in regards to the index, so there is a higher chance that there is one (or very few) records per block for a given index value. Deleting most of the records would cause many blocks not to have any entry for the given index key value, so the index scan will not visit them at all. That translates to good performance benefits.
              o     Low clustering factor indicates that data is ordered in regards to the index key. That means that many of the records for an index key reside in one block. Deleting large number of records would cause the records for the index key to get sparse in that block, but there is a smaller chance that the block would not hold any records for the given index key. That means that the block would most likely be visited, even though fewer records would be retrieved, and therefore the performance benefits of delete would be (much) smaller.
              • 4. Re: Does delete rows improve index range scan performance?
                698658
                Great, thanks.
                GregG