Browse Prior Art Database

A new access method with OR predicate across tables

IP.com Disclosure Number: IPCOM000239395D
Publication Date: 2014-Nov-05
Document File: 4 page(s) / 92K

Publishing Venue

The IP.com Prior Art Database

Abstract

This article introduces a new method to apply typical OR predicate depending on filter factor. Optimizer would transform the predicates to get higher filter factor.

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

Page 01 of 4

A new access method with OR predicate across tables

The predicate with OR across table could not be applied until all the referring tables have been involved in the composite table set. If there is no high filtering predicates in the composite table set, the query performance might quite poor in this situation.

The following is a real example from customer as below:

SELECT ......

FROM MRCOD.PassportIssueWorkflow passportis0_

LEFT OUTER JOIN MRCOD.PreIssuePassportData preissuepa1_

ON passportis0_.preIssuePassportData_id=preissuepa1_.id
LEFT OUTER JOIN MRCOD. ForeignPassport foreignpas2_

ON passportis0_.foreignPassport_id=foreignpas2_.id

WHERE preissuepa1_.passportNumber = '750066291'

OR foreignpas2_.passportNumber = '750066291'

OR preissuepa1_.passportNumber = '750066291'

In this query, the OR predicate has a high filter in this case, and join predicates have no much filter. But the OR predicate has to be applied after all the tables joining together. The customer complains the bad performance, but there are huge scale temporary join set, so the execution time is long.

Our idea is a new method for query with OR predicate across tables. The database could apply each sub-predicates under OR predicate separately, and then compound them together with sorted RID structure.

The query above could be divided into three temporary result set. RID structure lists are constructed for each of the predicate applied. The unions or intersections of the RID structure lists produce a final list of qualified RID structure that is used to retrieve the result rows. You can consider multiple table access as an extension to prefetch with more complex RID retrieval operations in its first phase. The complex operators are union and intersection.

If the query contains OR predicate with more than one tables, optimizer would calculate whether to apply this new access method. If the new method costs less, optimizer would build up the plan for this new access. During runtime, database would build up temporary result set and apply the union and intersection for final result.

The following is the main process for this new method.

1



Page 02 of 4


1. Query Transformation


Database would detect the style for this new method. If the query qualifies the new method style, database...