Database endpoint devices put in groups for responding to queries
Leveraging the competence of a computing cluster and a storage cluster by dynamically adjusting an SQL (structured query language) execution path to optimize system performance with fast response time under a distributed relational database on a data storage platform. Also, endpoint devices are divided into groups for the purpose of responding to queries and/or query blocks. The most suitable group of endpoint devices is chosen to respond to a given query or query block.
1. A computer-implemented method (CIM) for use with a database running on a database computer system that includes a first computing orientated clusters on a cloud platform and a first storage orientated cluster on the cloud platform, the CIM comprising:
determining that a first computing orientated cluster of the first computing orientated clusters is under high intensive workload and the first storage orientated cluster is not under high intensive workload by monitoring system workload intensity of the first computing orientated cluster and the first storage orientated cluster;
receiving, from a requester device over network, a first database query including a first query block that effectively specifies a selected portion of data stored in the database;
determining, by machine logic, an access path for performing the first query block with the access path specifying the first storage orientated cluster, by dynamically pushing down an access path corresponding to the first computing orientated cluster to a position below an access path corresponding to the first storage orientated cluster to pop up the access path for the first storage orientated cluster; dynamically building a redundant work file based on data with a specific range in a table which is frequently accessed using Structured Query Languages (SQLs); and distributing the redundant work file into multiple shards to avoid a cross shard JOIN operation; and
using the first storage orientated cluster to respond to the first query block and to thereby obtain first query block results.
2. The CIM of claim 1 further comprising: dynamically building a join buffer based on columns of a JOIN predicate with a specific range, which is frequently accessed using SQLs; and distributing the join buffer into multiple shards to perform a pre-join operation so that a number of moving data points among shards is reduced.
3. The CIM of claim 1 further comprising: cross locking the join buffer and the redundant work file when corresponding data, with a specific range, is modified and updated to reflect a value in both the redundant work file and join area.