US Patent No. 11,113,283

COST-BASED OPTIMIZATIONS FOR JOIN QUERIES WITH UNION ALL VIEWS AND DERIVED TABLES


Patent No. 11,113,283
Issue Date September 07, 2021
Title Cost-based Optimizations For Join Queries With Union All Views And Derived Tables
Inventorship Mohammed Al-Kateb, Rolling Hills Estates, CA (US)
Grace Kwan-On Au, Rancho Palos Verdes, CA (US)
Rama Krishna Korlapati, El Segundo, CA (US)
Lu Ma, Tenafly, NJ (US)
Sanjay Nair, El Segundo, CA (US)
Assignee Teradata US, Inc., San Diego, CA (US)

Claim of US Patent No. 11,113,283


1. A method, comprising:identifying a query with a Union All (UA) operation;
obtaining cost-based processing statistics for branches of the UA operation, wherein the obtaining further includes identifying each of the branches of the UA operation as independent join processing steps, each of the independent join processing steps comprising a unique table view being joined with outer tables in the query;
generating permutations of joins that satisfy each of the independent join processing steps and assigning a total cost to each of the permutations of joins using the cost-based processing statistics;
providing a select one of the permutations of joins that satisfy each of the independent join processing steps having a least total cost as an optimal query execution plan to a database engine for executing the optimal query execution plan against a database; and
outputting results from the optimal query execution plan from the select one of the permutations of joins that satisfy each of the join processing steps to a common spool.