Index Compression Design And Implementation On SYBASE Database

The development of database technology has penetrated to all walks of life, to some extent, the performance of the database directly affect the performance of the entire application system. More and more users are faced with the increasing of storage costs. Application increases and the accumulation of data, is not only a storage costs, but also a performance issue.Tables and indexes occupy the main targets of physical storage space in the database. Tables and indexes directly affect the disk space, at the same time, also determines the performance of the database. It is a very common thing to create a very large index for a very large table in many database systems. Especially, in some application systems, the amount of data is very huge. In order to accelerate the query speed, we always create different indexes which are always very big for different queries. With the passage of time, tables will be bigger and bigger and indexes also will be bigger and bigger. This is the reason why the database system become slower and slower. The same amount of buffer memory can hold larger amount of data than non-compressed data. Thus, less memory is required for the same operations than uncompressed databases. Less buffer are used to contain same amount data, and that translates to a higher cache hits. Compressed index has smaller on-disk footprint, and thus requires less I/O for the same amount if data.In this paper, we research how to do index compression on SAP SYBASE database. Firstly, we analyze the DOL table and APL table on their table structure and understand the specific meaning of each field on table structure. By modifying the two types of table structure and adjusting part of the field on table structure, we design and implement the index compression on SYBASE database. This paper makes the DOL table as an example, describes the design and implements process of index compression in detail. At last, we introduce how to make test on index compression function. Following is the main contribution of this paper:(1) SYBASE provides two locking schemes: All Page Lock and Data Only Lock. Because of two schemes, there are some differences between these two schemes on table structure. According to this situation, we deal with the function of each field respectively depends on the specific lock mechanism to implement the index compression.(2)This paper reorders the columns by column definition. All the columns are ordered by columns definition when defining this index, and then we know that every string in one index leaf page is sorted. The sorted of index columns will possibly lead to larger stream of prefix duplicate values in the different index rows, which is better to index compression.(3) The fixed length column has a certain limit of index compression, so we change the meaning of fixed length column. The criterion here is: all the columns after first variable length column will be treated as variable length column. Every variable length has a corresponding offset entry in offset table.(4) For index compression, we use one compression algorithm, and this compression algorithm is straightforward. Prefix Based Compression is to extract out similar long length byte strings, establish dictionary mapping, and map such long length byte string with shorter symbols, and substitute the long length byte stream with the symbol. Thus reduces the overall space needed for the data.