Browse Prior Art Database

Method And System For Creating A Variable Density Index

IP.com Disclosure Number: IPCOM000200551D
Publication Date: 2010-Oct-19
Document File: 5 page(s) / 27K

Publishing Venue

The IP.com Prior Art Database

Abstract

A method and system for creating a global variable density index on a range partitioned database table is disclosed.

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

Page 01 of 5

Method And System For Creating A Variable Density Index

Disclosed is a method and system for creating a global variable density index on a range partitioned database table. The global variable density index is a non-partitioned index in which a single b-tree index is used to refer rows from all range partitions. Further, the density of an index created for a partition depends on the usage of the partition. The partitions having frequent usage are defined as hot data partitions and the partitions having data that are not frequently used are defined as cold data partitions. For the hot data partitions, a traditional scan is performed which is fine grained. In contrast, for the cold data partitions, a table page sequential scan is performed. Thus, an index with variable density is created.

In an exemplary scenario, assume a table including a column C1

                                             15 rows as illustrated below. Column C1 has three range partitions such as, a Part1, a Part2, and a Part3. Part1 includes values ranging from 1 to 20; Part2 includes values ranging from 21 to 40; and Part3 includes values ranging from 51 to 70. Further, assume that Part1 and Part3 include cold data and Part2 includes hot data. Therefore, according to the method and system disclosed herein, a regular index named ROW index may be created for Part2 and a sparse index named PART index may be created for Part1 and Part3. The granularity of ROW index is one index reference per row and the granularity of PART index is at partition level. The granularity of indexes may also be adjusted by a specified density function.

Row(Key)values: 2, 3, 5, 8, 14, 24, 26, 31, 32, 37, 51, 53, 64, 66, 69

Row ID: R1 R2 R3 R4 R5 R1 R2 R3 R4 R5 R1 R2 R3 R4 R5

Partition ID: |---------- Part1 ---------| |--------- Part2 ----------| |--------- Part3 ----------|

Partition Boundary: [1, 20] [21, 40] [51, 70]

Index Density: PART INDEX ROW INDEX PART INDEX

In order to create index on the table, an index key is prefixed with the table partitioning key. In this scenario, the index key is C1.

Additionally, there is a function F()

associated with the table that can return the right partition ID for any given valid table partitioning key value. In case of any invalid key, an out of range error may occur. Further, the index record has the format of { Key, RID }. The RID of the index record consists of { Partition ID, Row ID } and the Key value of the index record is C1 for this scenario. The Partition ID is a part of the RID. However, the Partition ID is also considered as part of the Key internally. For example, the index key record for key = 31 is {31, Part2, R3}. Here, the internal key is {31, Part2} for C1 = 31 and RID is {Part2,

1

with


Page 02 of 5

R3}. The internal key is used during traversing the b-tree index for searching or any other index maintenance. The index maintenance may be one of insert, update, and delete.

Thereafter, an index density control map is created based on the option specified during the index cre...