Using Distribution Schemes to Eliminate Fragments

Fragment elimination is a database server feature that reduces the number of fragments involved in a database operation. This capability can improve performance significantly and reduce contention for the disks on which fragments reside.

Fragment elimination improves both response time for a given query and concurrency between queries. Because the database server does not need to read in unnecessary fragments, I/O for a query is reduced. Activity in the LRU queues is also reduced.

If you use an appropriate distribution scheme, the database server can eliminate fragments from the following database operations:

• The fetch portion of the SELECT, INSERT, delete or update statements in SQL

  The database server can eliminate fragments when these SQL statements are optimized, before the actual search.

• Nested-loop joins

  When the database server obtains the key value from the outer table, it can eliminate fragments to search on the inner table.

Whether the database server can eliminate fragments from a search depends on two factors:

• The distribution scheme in the fragmentation strategy of the table that is being searched
• The form of the query expression (the expression in the WHERE clause of a SELECT, INSERT, delete or update statement)