Oracle International Corporation

Kamaljit Shergill

Shasank Kisan Chavan

Aurosish Mishra

Ken Kumar

Hariharan Lakshmanan

Viral Shah

Techniques for accelerating query execution by optimizing data transfer between storage nodes and database nodes are provided. In one technique, a compute node receives a database statement and transmits a set of one or more selection criteria associated with the database statement to a storage node. Based on the database statement, the storage node retrieves a set of data blocks from storage. Each data block comprises multiple rows of an index-organized table (IOT), each row comprising a key section and a non-key section. The storage node applies the set of selection criteria to a data block, resulting in a modified data block. The storage node generates a modified header data for the modified data block and transmits the modified data block to the compute node.

Accelerating query execution by optimizing data transfer between storage nodes and database nodes

Techniques for adaptive, independent compression of key and non-key sections of rows in index-organized tables (IOTs) are provided. In one technique, an IOT is stored that comprises a plurality of rows, each of which comprises a key section and a non-key section. After storing the IOT, a compression technique is performed on the non-key section of each row in the plurality of rows to generate a plurality of compressed non-key sections. However, none of the key sections of the plurality of rows is compressed. In a related technique, instead of compressing the non-key section of each row, the key section of each row is compressed. In a related technique, both sections are compressed, but using different compression techniques. The compression techniques may be determined based on data access history of the different sections of the rows.

Techniques for adaptive independent compression of key and non-key portions of database rows in index organized tables (IOTs)

Techniques are provided for a DBMS automating ILM on indexes, based on index composition, to efficiently reduce index storage footprints. According to an embodiment, a user sets an index-specific ILM (ISILM) policy, which comprises one or both of an index-test requirement and a time requirement. Based on the ISILM policy being met, or on some other way of initiating analysis, the DBMS automatically analyzes the data blocks storing the index to determine an index condition metric (e.g., percentage of free space). This analysis is performed on a sample of data blocks storing the index without blocking the index from other operations during the analysis. The condition metric for the entire index is estimated based on analysis of the sample data blocks. Using the determined condition metric for an index, the DBMS automatically selects an option for optimally managing the index (e.g., coalesce, shrink space, index rebuild, no action, etc.).

Growing community of inventors

Maidenhead, United Kingdom

Kamaljit Shergill