Optimizing Aggregate Query Processing in Cloud Data Warehouses - Data Management in Cloud, Grid and P2P Systems - page 6

%)&

!

!

$!#

&$! "#
$! '

$ "(
$ "

&$#
$! "'

'#"
#!(

! "%

'

#!*#!

'#!"
#!(

&$ "#
$! "'

&$ "#
$'

&

#!*
#!

'#"
#!(

'#!"
#!(

"%

'#!"
#(

!%"

$(

#*

(a) MySQL Plan (b) PK-map Plan

Fig. 2. Query Processing Plan for Query1 of Table 3

3.3 Aggregate Query Processing

Inferring on referential integrity constraints and functional dependencies, we
have classified aggregate operations into two general categories based on the
type of attribute on which the aggregate operation is applied. We then generate
plan for query processing accordingly. 2

Aggregate on Primary or Foreign Key: When there is an aggregateoperation
onprimary key (PK) or foreignkey (FK), wedo not scanthe databasetable (unless
there is a filtering constraint on non-PK or non-FK), instead we scan our proposed
mapstructuresandperformaggregateoperationonthefly.Wecandothis because,
our maps are sorted on keys, and has required information on number of tuples of
foreign key table that are mapped to primary key of dimension table.
For example, Query 1 in Table 3 has count(*) operation. This query is trying
to find the total number of orders placed by the specified nations. Here, we
only require the number of rows in orders table that belong to each group in the
group-by clause (i.e., N.name). By inference, we can say that, this is an aggregate
operation on PK of the orders table. Hence, we can eliminate the scan of orders
table and get this count by scanning CustKey-map. We push down group-by on
N.name to step ”scan NationKey-map” as shown in Figure 2b.
Figure 2a is the plan generated by MySQL for processing Query 1 of Ta-
ble 3. As shown in Figure 2a, it requires three tables scan (Nation, Customer
and Orders), two joins, a sort and a group by operation. Here, query proces-
sor needs to communicate the data for each join operation, which increases the
communication overhead and response time.

2 We do not consider ”having” clause in our analysis, because having clause can be
converted into ”where” clause constraints by rewriting the query [22].