Database Index Pre-allocation for Contiguous Page Storage

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Database fragmentation leads to significant performance degradation in sequential index scans, as conventional approaches fail to maintain physical contiguity of pages, resulting in increased disk head movement and IO costs during data inserts after index build.

Innovation Solution

Pre-allocate a block of contiguous data storage space for subsequent inserts into a sub-range of an index if its size exceeds a predetermined value, ensuring physical contiguity and reducing fragmentation.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If pages are allocated on demand for subsequent inserts after index build, then the system is flexible and adaptive, but physical contiguity is lost and fragmentation increases

Engineering Contradiction:

Improveflexibility of page allocationVSAvoidphysical contiguity of pages

Core Design Contradiction:

Adaptability or versatilityVSStability of the object's composition

Solution Approach 1:

The patent pre-allocates a block of physically contiguous pages for each index sub-range before data inserts occur. This preliminary allocation ensures that subsequent inserts into that sub-range will use contiguous pages, maintaining physical order matching logical order and reducing fragmentation, while still allowing flexible allocation across different sub-ranges.

Inventive Principle:

Principle #10Preliminary action

2Productivity

If conventional allocation systems allocate pages from different locations for concurrent inserts, then multiple users can access simultaneously, but page separation increases fragmentation

Engineering Contradiction:

Improveconcurrent access capabilityVSAvoidpage contiguity

Core Design Contradiction:

ProductivityVSStability of the object's composition

Solution Approach 1:

The patent divides the index into multiple sub-ranges, each with its own pre-allocated block of contiguous pages. Concurrent inserts to different sub-ranges can proceed simultaneously without interfering with each other's contiguity, as each sub-range maintains its own dedicated contiguous page block.

Inventive Principle:

Principle #1Segmentation

3Quantity of substance

If index size is made large to accommodate data warehouse queries, then more data can be stored, but disk head movement increases and IO performance decreases

Engineering Contradiction:

Improvedata storage capacityVSAvoiddisk IO completion time

Core Design Contradiction:

Quantity of substanceVSLoss of time

Solution Approach 1:

By pre-allocating contiguous page blocks for each index sub-range before data inserts, the patent ensures that even as the index grows to accommodate large data warehouse queries, the pages remain physically contiguous. This eliminates random disk head movements and enables efficient sequential scanning of large indexes.

Inventive Principle:

Principle #10Preliminary action

Data Source

PatentUS7747578B2Reducing database fragmentation

Publication Date: 2010.06.29 MICROSOFT TECHNOLOGY LICENSING LLC

AI summary

Reducing database fragmentation is disclosed. One embodiment includes determining whether the building of an index of data that is to be stored in a database has been initiated and determining whether the size of a sub-range of the index exceeds a predetermined size. A block of data storage space is pre-allocated contiguous to a block of data storage space allocated for the sub-range to accommodate subsequent inserts of data into the sub-range, if it is determined that the sub-range exceeds the predetermined size.