Browse Prior Art Database

Optimization Techniques For Partial Index Maintenance

IP.com Disclosure Number: IPCOM000031859D
Original Publication Date: 2004-Oct-14
Included in the Prior Art Database: 2004-Oct-14
Document File: 4 page(s) / 37K

Publishing Venue

IBM

Abstract

Disclosed is a system or method for optimization techniques for index maintenance on modified data. The techniques illustrated in this article are applicable for partial index maintenance.

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

Page 1 of 4

Optimization Techniques For Partial Index Maintenance

Data synchronization plays an important role for data exchange in the mobile environment. There is a need to further extend optimization techniques on the database system so that data synchronization can be performed in an efficient manner and the overall processing time can be reduced. The system and optimization techniques presented in this article focuses on index maintenance for modified data, and yet the techniques are applicable for partial index maintenance.

In relational database management system or DBMS, applications/users can create tables. A table comprises of a fixed number of columns defined during table creation time. The following illustrates a simple table:

    CREATE TABLE EMPLOYEE (NAME VARCHAR(20), TITLE VARCHAR(20), SALARY DECIMAL(10,2))

The above table may store a number of employee records; each has the name of the employee, his/her title and the salary.

In order to perform data synchronization, A DBMS needs to keep track of the state of each row. That is, it needs to understand whether a row has been inserted/modified/deleted. One method to implement this is for each row to have an extra column called dirty bit column ($dirty), which is capable of storing a couple of bits representing the state information of the row. There are 4 possible states of every row:

0 - CLEAN

1 - DELETE

2 - INSERT

3 - UPDATE

After data synchronization, all rows will have the "CLEAN" (0) state. A new row inserted by an application will have the "INSERT" (2) state, whereas an existing row deleted by an application will have the "DELETE" (1) state (The row is "logically" deleted rather than physically removed from the database so that data synchronization can find out that a row was deleted). Similarly, an existing row updated by an application will have the "UPDATE" (3) state. Upon a successful data synchronization, all dirty bits will be reset to "CLEAN" state.

Typically on mobile devices, the number of modified rows is small compared with the number of "CLEAN" rows. For data synchronization purpose, we are interested in retrieving only the modified rows since the last data synchronization; that is, those rows with dirty bit values 1, 2 and 3. In order to retrieve only these rows efficiently, The system creates an index on the dirty bit column. The following example illustrates how one can create an index on the dirty bit column of the above EMPLOYEE table:

(A) CREATE INDEX EMP_IDX ON EMPLOYEE($DIRTY)

1

Page 2 of 4

Like any other indexes, the dirty bit index will be maintained when a new row is inserted into the table EMPLOYEE, or an existing row is update or logically deleted. However, the data synchronization process does not really need to keep track of "CLEAN" rows. For this reason, a partial index which does not store any information about the "CLEAN" state will be sufficient and more efficient. This can be achieved via the following CREATE INDEX statement:

(B) CREATE INDEX EMP...