Distribution schemes that 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.
- 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.
- 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)