Browse Prior Art Database

A method to create a cross table index with include columns

IP.com Disclosure Number: IPCOM000248898D
Publication Date: 2017-Jan-20
Document File: 9 page(s) / 382K

Publishing Venue

The IP.com Prior Art Database

Abstract

Currently, in traditional relational database, the relationship between tables are more and more complex. Normally a SQL(Structured Query Language) statement may reference multiple tables in which the scanning multiple tables and join between tables are the hot point of cost. Especially when there are some tables contain many records but lack of effective access method, the performance for SQL statement may not be good. The access methods used to retrieve data may significantly affect the speed of the retrieval and the amount of resources consumed during the retrieval process.

In additional, the most common relationship between tables is parent-child referential constraint. When the parent table or child table is updated, database will also need to check the child table or parent table to enforce referential constraint. If there is not an effective access method on child table, database will have to scan the whole table each time to find the corresponding rows when parent table is updated. So the access method between parent and child table may significantly affect the performance of SQL statement on them.

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

1

A method to create a cross table index with include columns

Currently, in traditional relational database, the relationship between tables are more and more complex. Normally a SQL statement may reference multiple tables in which the

scanning multiple tables and join between tables are the hot point of cost. Especially when there are some tables contain many records but lack of effective access method, the

performance for SQL statement may not be good. The access methods used to retrieve data may significantly affect the speed of the retrieval and the amount of resources

consumed during the retrieval process.

In additional, the most common relationship between tables is parent-child referential constraint. When the parent table or child table is updated, database will also need to

check the child table or parent table to enforce referential constraint. If there is not an effective access method on child table, database will have to scan the whole table each

time to find the corresponding rows when parent table is updated. So the access method between parent and child table may significantly affect the performance of SQL

statement on them.

We would like to introduce a new kind of index: cross table index with include columns. The index is created on tables in which there is at least one common column and the

common column in one of the table is unique (called leading table), such as tables with parent-child relationship. Database create the index on the common columns of the

leading table, include some non-key columns from other tables (including table). It is a two-way index. The index key will be appended with the leading table RID (Row ID)

as well as the including table RID. Database can find the leading table row as well as the including table row by a giving ordering key in the index.

There are several advantages by using this new kind of index:

• Allows cross table columns to be stored in one index which can be used by the optimizer to get non-key data cross tables. • Increases index-only access for some cross table queries. Although the same index-only access could be achieved using the separate index, there are benefits to using

cross table index: less space to store the index, a fastpath for updating tables, improved performance of index access etc.

• Avoid scanning table and join between tables for some queries, which effectively reduce the cost for the SQL statement. • when the index creates on a parent-child pair tables, it can effectively improve the performance of insert, update and delete on both parent and child tables, as the

checking referential constraint can be done by one accessing the index.

There are several characteristics for the cross table index with include columns:

a) The index is created on two or more tables, in which there is a leading table and others is including table.

b) The index is a unique index with the ordering key columns defined on the leading table. The including table have some columns which refer...