Database Reference
In-Depth Information
executing Query 13 (Q 13) that comprises of IQ24
IQ27, and CTT-and-VET by
Query 14 (Q 14) that comprises of IQ28, IQ22, IQ29, and IQ27 respectively. This
experiment is used to study, retrieving data from three targeted tables. The
-
rst
table is the
'
product
'
table, the second table is the
'
sales_fact
'
table, and the third
table is
. The structure of these three tables is shown in Fig.
7
(a),
(b) and (c). These tables have relationships between them, and multiple query
'
sales_fact_details
'
lters
are used in each of these tables to
lter data based on the results of subqueries,
starting from the
'
product
'
table (Root Table) until the
'
sales_fact_details
'
table
(Targeted Table). The
'
product
'
table is
ltered by retrieving only products with
product IDs equal to 100. Then the
ltered by retrieving the
sales transactions that their product IDs match the sales IDs that are retrieved from
the
'
sales_fact
'
table is
'
product
'
table, and the quantity values that are greater or equal than 9000.
Finally, the
ltered by retrieving the sales details that
their sales IDs matches sales IDs retrieved from the
'
sales_fact_details
'
table is
table, and the sales
discounts that are greater or equal 30 %. Figure
13
shows how the queries are
'
sales_fact
'
ltered from the three tables. In Q 12, the three CTTs are used as stated above. The
Q 13 uses three VETs, including the
'
product VET that equals 16, the
'
sales_fact
'
'
'
VET that equals 17, and the
sales_fact_details
VET that equals 18. The Q 14 uses
'
'
'
'
two CTTs and one VET. The two CTTs are the
product
CTT and the
sales_fact
'
'
CTT, and the VET is the
that equals 18. The experimental
results of Exp. 5 shows that the query execution time of CTT is faster than VET,
and CTT-and-VET is the fastest of the three queries that retrieve 1 row. On the
other hand, 100 rows are retrieved, the query execution time of VET is faster than
CTT, and CTT-and-VET is the fastest of the three queries. Most importantly, the
query execution times when retrieve 1 row or 100 rows from a VET or a CTT-and-
VET are approximately the same, whereas the increase in retrieving 100 rows from
CTT is approximately 70 % higher than when 1 row is retrieved. The details of
the queries used in this experiment are shown in Tables
3
and
4
, the output of these
queries is shown in Fig.
14
, and the throughputs of this experiment are depicted in
Fig.
16
(i) and (j).
sales_fact_details
Fig. 13. The query lters of the Targeted Tables experiment
Search WWH ::
Custom Search