Browse Prior Art Database

The fast search function with the additional index table to improve performance

IP.com Disclosure Number: IPCOM000012247D
Original Publication Date: 2003-Apr-23
Included in the Prior Art Database: 2003-Apr-23
Document File: 3 page(s) / 22K

Publishing Venue

IBM

Abstract

The technique of accelerating the database search time is proposed, in case the data which met search conditions via the index table is acquired. Time shortening of the search time is carried out by reading the record on required enough tables as a result of index table reference. In order to shorten search time, an auxiliary index table is prepared apart from an index table. The search conditions searched in the past are saved for an auxiliary index table as an index key, and it gives the column name of the index to which the flag corresponding to the search result is set to a corresponding column. If search conditions are searched to KEY and the record which agreed finds an auxiliary index table before index table search, it will make it possible to shorten search time by reading only the record on the table which the pointer of the index record which has the search result as a flag has pointed out.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 51% of the total text.

Page 1 of 3

THIS COPY WAS MADE FROM AN INTERNAL IBM DOCUMENT AND NOT FROM THE PUBLISHED BOOK

JP820020314 Koichiro Kato/Japan/IBM Tomoaki Hayashi, Yohichi Hattori

The fast search function with the additional index table to improve performance

How does the invention solve the problem Index table makes the data of the column which gives an index a key value, and has the pointer value which points out the record with which each key value of an index corresponds as column data. In this invention, an index newly adds one or more columns other than the column for key values. It is used for the added column in order to set a flag to the record which was in agreement with the search result with execution of the past search. The auxiliary index table which newly in addition to an index table saves the past search conditions on the other hand is created. This auxiliary index table gives the column name which had search conditions as a key value and data as corresponding column data.

Before reading the record of an index table, what has the past search conditions as a key value is searched with the record in an auxiliary index table. If there are some which are in agreement with search conditions as a result of this search, the column name of the index of the record will be acquired. In reading of an index table, only the pointer which the record which the flag has set in the column name acquired on the auxiliary index table is used, and the record of a table is read. Therefore, when reading of an index record is completed, the record of the table suitable for search conditions is decided. Since reading of a useless record does not occur in reading of the record of a table, search time is shortened.

Does this solution differ from othes and why is it better ? The given index is optimizing the optimizer currently offered by the database system, and it is possible to shorten reference time. However, an effect is restricted when the distribution with the flat variation in the index key value created in this case is shown. Moreover, when column values other than an index key are included as search conditions, unless it reads the record on a table from an index, it is not decided whether finally a search result is met. For this reason, reading of the record on a useless table occurs.

Figure: In the example of 1, since all the records on an index table meet the conditions of DATA_A>10, the record of all tables is read from the pointer which the record has. However, since the record of PK = KEY2 on a table does not meet the conditions of DATA_B>10, reading becomes useless.

SELECT DATA_C FROM TABLE WHERE (DATA_A > 10) and (DATA_B >10)

Index Key

Table

PK

DATA_A

DATA_B

DATA_C

KEY1

12

15

11

KEY2

11

05

08

KEY3

15

11

06

This record reading is no use

Index Table

11

All records are read via Index Table

Index Key

Pointer

12

15

Fig.1

Technical details The example which performs search on condition that (DATA_A>10 & DATA_B>10) is come out of and

1

[This page co...