Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

LOAD REPLACE PART INDEX MAINTENANCE

IP.com Disclosure Number: IPCOM000248711D
Publication Date: 2016-Dec-28
Document File: 2 page(s) / 83K

Publishing Venue

The IP.com Prior Art Database

Abstract

Disclosure allows user to define a threshold for behavior change, when rows number in table is less than threshold, during load replace partition, DBMS uses index tree to delete keys one by one. When rows number in table is more than threshold in a partition, DBMS scans NPI leaf pages to delete all keys belong to that partition. By this way DBMS does not have to take a long time to scan entire NPI for few rows.

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

1

LOAD REPLACE PART INDEX MAINTENANCE

In database system, data can be kept in different partitions. Thus database can just operate on specific partition during SQL(Structured Query Language) access or table

maintenance operations. Access to part 1 won’t impact part 2 so this is big benefit and this data partition skill is wide used in different database product.

In case of data is kept in different partitions, index can be not partitioned as the index key may not be related to data partitioning key. Let’s say data is partitioned by first

column: ACOUNT_NUMBER. And we need an index on second column: DEPOSIT. We call this kind of index on partitioned table a NPI (nonpartitioned index)

LOAD REPLACE PART is a kind of LOAD operation on partition level, it will cleanup data partition and load new data into it. It also need to maintain table’s index,

including NPI. As NPI contains keys from all partitions, so it is hard to just delete all keys belong to one partition from NPI. Current technic is to scan all NPI leaf pages, and

delete all keys belong to specific partition. However, this operation is very expensive when we the partition just contains several rows (let’s say 10 rows). Because at that time

we need to scan all NPI to find out just 10 keys to delete.

LOAD REPLACE PART will not go through index tree to delete keys, because it takes too much time when there are lots of data need to be cleaned.

2

This invention uses statistic information for table part, when we have few row...