System and method for query acceleration for use with data analytics environments

Patent metadata

Field	Value
Publication number	US-12481670-B2
Application number	US-202217895665-A
Country	US
Kind code	B2
Filing date	Aug 25, 2022
Priority date	Aug 27, 2021
Publication date	Nov 25, 2025
Grant date	Nov 25, 2025

Abstract

In accordance with an embodiment, described herein is a system and method for providing query acceleration with a computing environment such as, for example, a business intelligence environment, database, data warehouse, or other type of environment that supports data analytics. A middle layer is provided as a long-term table data storage format; and one more acceleration formats, or acceleration tables, can be periodically regenerated from the middle layer, wherein a determination can be made as to whether an accelerated table exists for a dataset table, and if so, then the accelerated table is used to process the query.

First claim

What is claimed is:

1 . A system for providing query acceleration with a computing environment that supports data analytics, comprising:
a computer having a processor, memory, and data analytics environment operating thereon; a business intelligence server, comprising a plurality of query engine nodes, that describes data available as subject areas for queries, and receives and processes incoming queries directed to data stored at a database, in accordance with query execution plans; and wherein the system provides a query acceleration process for use in examining datasets including the use of a dataset lifecycle and one or more query flows, wherein a dataset operates as a data model that indicates one or more data source connections, data enrichments, or data transformations, and wherein said query acceleration process includes:
generating a data structure comprising a plurality of accelerated tables for use with a plurality of datasets, and associating each dataset, of the plurality of datasets, with a metadata to indicate the dataset has been cached within the data structure as one or more accelerated tables of the plurality of accelerated tables; and
coordinating use of the data structure comprising the plurality of accelerated tables by the plurality of query engine nodes including, in response to a query received via a query engine node, of the plurality of query engine nodes, directed to a particular dataset of the plurality of datasets, and associated with a query execution plan:
determining whether the query can be processed using an accelerated table associated with the particular dataset, according to the data lifecycle that assesses whether one or more of the accelerated table, or a local native format table, or a remote native format table, exists; and,
in response to a determination is made that an accelerated table associated with the particular dataset exists and can be used to process the query, modifying the query execution plan to obtain query results by reading, from the data structure, the accelerated table associated with the particular dataset;

wherein use of the accelerated tables is distributed across query engine nodes sharing an endpoint, including that each query engine node sharing the endpoint uses locks to load datasets into memory, or unload datasets, depending on current outstanding requests.

2 . The system of claim 1 ,
wherein a middle layer is provided as a long-term table data storage format; and wherein the plurality of accelerated tables are periodically regenerated from the middle layer, wherein a determination is made as to whether an accelerated table exists for a dataset table, and in response to determining said accelerated table exists, the accelerated table is used to process the query.

3 . The system of claim 2 , wherein in response to a determination is made that the accelerated table does not exist for the dataset table, then a determination is made as to whether a local native format table exists, and in response to determining said local native format table exists, the query is processed using the local native format table.

4 . The system of claim 3 , wherein in response to a determination is made that no local native format table exists, then a determination is made as to whether a remote native format table exists, and in response to determining said remote native format table exists, the remote native format table is downloaded, and the query is processed.

5 . The system of claim 4 , wherein in response to a determination is made that no remote native format table exists, then:
a determination is made as to whether the dataset is enriched; a prepared format is downloaded; the system applies output expressions, converts the dataset table to a native format table, and processes the query.

6 . The system of claim 1 , wherein the system is provided within a cloud environment.

7 . The system of claim 1 , wherein responsibility of coordinating use of the data structure comprising the plurality of accelerated tables by the plurality of query engine nodes is distributed across the plurality of query engine nodes sharing an endpoint, including that each query engine node, of the plurality of query engine nodes, runs a daemon thread that periodically attempts to load and unload datasets depending on current outstanding requests.

8 . The system of claim 1 , wherein each customer or tenant of the environment is associated with their own customer schema and is additionally provided with read-only access to an analytic applications schema, for use in receiving and processing incoming queries.

9 . A method for providing query acceleration with a computing environment that supports data analytics, comprising:
providing, at a computer having a processor, memory, for use with a data analytics environment, a business intelligence server, comprising a plurality of query engine nodes, that describes data available as subject areas for queries, and receives and processes incoming queries directed to data stored at a database, in accordance with query execution plans; and providing a query acceleration process for use in examining datasets including the use of a dataset lifecycle and one or more query flows, wherein a dataset operates as a data model that indicates one or more data source connections, data enrichments, or data transformations, and wherein said query acceleration process includes:
generating a data structure comprising a plurality of accelerated tables for use with a plurality of datasets and associating each dataset, of the plurality of datasets, with a metadata to indicate the dataset has been cached within the data structure as one or more accelerated tables of the plurality of accelerated tables; and
coordinating use of the data structure comprising the plurality of accelerated tables by the plurality of query engine nodes including, in response to a query received via a query engine node, of the plurality of query engine nodes, directed to a particular dataset of the plurality of datasets, and associated with a query execution plan:
determining whether the query can be processed using an accelerated table associated with the particular dataset, according to the data lifecycle that assesses whether one or more of the accelerated table, or a local native format table, or a remote native format table, exists; and,
in response to a determination is made that an accelerated table associated with the particular dataset exists and can be used to process the query, modifying the query execution plan to obtain query results by reading, from the data structure, the accelerated table associated with the particular dataset;

wherein use of the accelerated tables is distributed across query engine nodes sharing an endpoint, including that each query engine node sharing the endpoint uses locks to load datasets into memory, or unload datasets, depending on current outstanding requests.

10 . The method of claim 9 ,
wherein a middle layer is provided as a long-term table data storage format; and wherein the plurality of accelerated tables are periodically regenerated from the middle layer, wherein a determination is made as to whether an accelerated table exists for a dataset table, and in response to determining said accelerated table exists, the accelerated table is used to process the query.

11 . The method of claim 10 , wherein in response to a determination is made that the accelerated table does not exist, then a determination is made as to whether a local native format table exists, and in response to determining said local native format table exists, the query is processed using the local native format table.

Assignees

• Oracle Int Corp

Inventors

• Mittal Ashish
• Eng Kenneth
• Mascarenhas Alextair
• Wong David
• Hebbar Praful
• Ge Yi
• Rajagopalan Mahadevan
• Bolsius Roger
• Ranganathan Vijayakumar
• Lotia Samar

Classifications

• G06F16/258

  Data format conversion from or to a database · CPC title

• G06F16/24549

  Run-time optimisation · CPC title

• G06F16/27

  Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor · CPC title

• G06F16/256

  in federated or virtual databases · CPC title

• G06F16/2471

  Distributed queries · CPC title