Browse Prior Art Database

Method And System For Creating A Floating Partial Index

IP.com Disclosure Number: IPCOM000201143D
Publication Date: 2010-Nov-09
Document File: 3 page(s) / 48K

Publishing Venue

The IP.com Prior Art Database

Abstract

A method and system for creating a floating partial index is disclosed. The maximum size of the floating partial index is predefined and the total number of keys in the floating partial index is fixed.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 52% of the total text.

Page 01 of 3

Method And System For Creating A Floating Partial Index

Disclosed is a method and system for creating a floating partial index. The maximum size of the floating partial index is predefined and the total number of keys in the floating partial index is fixed.

A partial index is conventionally defined for a portion of a table. Generally, the partial

index is defined by associating a predicate with index definition. For example, the following index definition is for a partial index for a portion of a table:

create index I on t(c

)

In accordance with an instance of the method and system, the floating partial index is implemented using a B+ tree as illustrated in FIG. 1. Leaf nodes of the B+ tree are connected to each other as a doubly linked list.

Accordingly, the leaf nodes have two

(This page contains 00 pictures or other non-text object)

Figure 1

Initially, the floating partial index defined using the index definition is empty and is

)

where c > 100 .

In this case, the partial index covers rows of the table which satisfy the predicate 'c > 100 '. The predicate used in the index definition is required to be chosen such that the predicate covers a minimal number of keys and covers a maximum number of incoming queries. However, there may be scenarios wherein a large set of incoming queries are not covered by a predicate defined for a partial index.

The method and system disclosed herein defines the maximum size for a floating partial index for a table and fixes the maximum number of keys in the partial index. For example, a floating partial index may be defined with 1000 keys using the following index definition:

create index I on t(c

where KEY

_COUNT = 1000

.

additional pointers corresponding to previous and next nodes.

1


Page 02 of 3

populated based on the incoming queries. In accordance with an instance of the method and system, FIG. 2 illustrates a flow diagram for population and scanning of the floating partial index.

(This page contains 00 pictures or other non-text object)

Figure 2

When a query is received, a scan is performed on the table and a page including one or more rows from the table corresponding to the query is returned. Subsequently, the method and system disclosed herein inserts a key corresponding to the page in the partial index as illustrated in FIG. 2. Thereafter, "Last node" pointer of the doubly linked list of the B+ tree is moved to a new node and remaining pointers of the doubly linked list are adjusted accordingly.

Similarly, multiple keys are inserted into the floating partial index based on queries that are received.

Additionally, keys may be inserted into the floating partial index based on

insertion of new rows in the table. In this case, if a new row is determined to be frequently accessed in the future, a key corresponding to the new row is inserted into the floating partial index. In an instance of the method and system, insertion of...