Recent developments in solid-state drive (SSD) technology have significantly enhanced the access speed of these storage devices, surpassing traditional magnetic hard drives by orders of magnitude. This remarkable capability for swift data retrieval aligns seamlessly with On-Line Analytical Processing (OLAP) techniques, which heavily depend on read speeds rather than write speeds. In this paper, we analyze the performance of OLAP techniques on an SSD as compared with a normal hard drive using a trace-based approach. The advantage of this approach is that it (1) allows simulating any HDD, SSD and DBMS (2) allows the DBMS to treat the SSD as though it was a regular hard drive and (3) allows to observe the diferences in execution times without altering any data structure or algorithms of the target DBMS. Although SSDs do not substantially improve write speeds, our experiments empirically indicate that their exceptional read speed makes them the prime candidate for data storage in read-oriented database environment.
of contemporary data mandates a shift beyond the limitations of HDDs. Additionally, as storage technology Online Analytical Processing OLAP [1] is a set of tech- advances, it becomes imperative to carefully examine niques that often involves scanning large datasets apply- the limitations of hard drives and, therefore, advocate ing aggregations at certain granularity levels and writing for the adoption of new media such as Solid State Drives summary information back to the database. In addition, (SSDs)[9, 10].
OLAP can also involve the retrieval of specific groups One of the most significant drawbacks of HDDs is from a large precomputed repository of data. For an their mechanical nature. Unlike SSDs, which use flash OLAP user, performance is often the most important memory for data storage, HDDs rely on spinning disks and sought-after quality. Like many other techniques and a moving read/write head. This mechanical structure in database management systems, the efectiveness of introduces latency, leading to slower data access times OLAP relies fundamentally on the speed of memory, CPU and increased susceptibility to wear and tear[11, 12, 13]. capabilities, and hard drive speeds[2]. In nearly all in- The constant movement of parts within an HDD not only stances, it’s the hard drives that constitute the limiting hinders overall performance but also makes them more factor in terms of performance. In the ever-changing land- prone to failure. Another notable disadvantage is the scape of data storage, hard disk drives (HDDs) have long fragility of HDDs. The delicate nature of the internal been the workhorses, serving faithfully as the primary components means that HDDs are susceptible to damage storage medium for all types of data and applications from physical shocks and vibrations[14, 15, 16]. This and particularly for DBMSs and their operational data vulnerability can result in data loss or, in severe cases, . As data volume grows annually and new data types render the entire drive inoperable. In contrast, SSDs, (semi-structured, multimedia, graph, etc.) consistently being devoid of moving parts, are inherently more robust emerge, the need for cutting-edge technologies capable and better equipped to withstand shocks, making them a of eficiently managing this extensive and diverse data more reliable choice for data storage. Energy eficiency becomes paramount[3, 4, 5, 6, 7, 8]. Addressing the evolv- is also a significant concern when comparing HDDs to ing challenges posed by the expanding and varied nature SSDs. HDDs consume more power due to the continuous spinning of disks and the movement of mechanical parts.
SnYeeSrTinEgMa2n0d25M: a1t1htehmSaatpiicesn.zRaomYeea,rJlyunSey4m-6p,o2si0u2m5 of Technology, Engi- In environments where energy eficiency is a priority, $ c.salmi@univ-boumerdes.dz (C. Salmi); such as in laptops or data centers, SSDs stand out as n.senoussi@univ-boumerdes.dz (N. E. Senoussi); a more energy-eficient alternative. Their lower power djoumana.chaal@gmail.com (D. Chaal); consumption not only contributes to a greener computing boudjadi.mlohammed@gmail.com (M. Boudjadi) environment but also translates to longer battery life in (N.0E0.0S0e-n00o0u1s-s7i)131-6158 (C. Salmi); 0009-0009-4906-9686 portable devices. SSDs are based on flash memory which © 2025 Copyright for this paper by its authors. Use permitted under Creative Commons License is a widely used medium in embedded systems (e.g. PDAs, Attribution 4.0 International (CC BY 4.0). mobile phones, etc.) and promises to replace the magnetic HDD and SSD and finally, lazy-cleaning in which dirty hard drive for secondary storage system in all IT devices pages are first written to SSD and later copied from the recent and data centers. SSD to HDD (lazy updates). Authors in [25] focused on
More specifically, the Solid State Drive (SSD) is a data lfash memory as a write cache for databases stored on storage device that uses NAND flash memory and can be conventional hard disk. In this case, when dirty pages used in the same way as an HDD disk via IDE, SATA or are evicted from the memory bufer pool, they are first SCSI interfaces or more recently with NVMe interfaces written on the flash-based-cache and later propagated that are four times faster than SATA. to hard disk, applying some replacement strategies to
OLAP is a technique for multidimensional analysis, reduce the amount of data written to flash. The issue which allows decision-makers to have quick and interac- of SDD bufer in the OLTP environment has been extive access to relevant information presented from var- amined in [26], with a significant focus on enhancing ious and multiple angles, according to their particular recovery time and ensuring data integrity following a needs. OLAP is therefore a technique whose function- crash or a routine database restart[27, 28, 29]. Metadata alities are used to facilitate multidimensional analysis: about the contents of the SSD bufer are stored on the so operations that can be carried out on the hypercube to called: SSD bufer table. This table can be reconstructed extract data. Moreover, OLAP processing necessitates using transactional log files and is periodically flushed fewer write operations compared to other database ap- in an asynchronous mode. A caching algorithm at the plications like operational OLTP databases (online trans- granularity of subtuples is proposed in [30]. The algoaction processing) . This characteristic makes OLAP a rithm partitions vertically a database table and then the suitable candidate for execution on SSD-type media, the subtuples are cached. Updates are gathered and when a speed of which has consistently improved since their page eviction occurs, subtuples are flushed to flash meminception. The aim of this work is to study the suitabil- ory in the same area (a page or more) which reduces the ity of flash memories and SSD disks to data warehouse amount of data written to flash memory. In the paper [ 31], technology. The paper is organized as follows. In Section authors introduced a caching system that utilizes flash 2, we review some related works related to the integra- memory as an intermediary layer positioned between tion of flash memory in Database Management Systems the main memory bufer pool and the magnetic disk. A (DBMS). In Section 3 we present the fundamental con- theoretical cost model and a strategy that decides which cepts of flash memory and SSD drive. In section 4 we data to be cached were also proposed. Despite their impresent the data warehouses and the OLAP technique portance in rapid decision-making and their diferences which constitutes the context of our work. In Section 5, from classical relational databases, data warehouses and we present our proposal, emphasizing the hybridization OLAP queries have not been studied much, especially in of HDD and SSD media. Section 6 is dedicated to imple- the context of recent storage media such as flash memory. mentation and experimentation. Within this section, we The authors in [32] have proposed an approach whose introduce the flash memory simulator utilized, provide principle is to generate small lattices by reducing the redetails about the environments, and present the results dundancy of prefixes and sufixes to manage condensed of all conducted simulations. Section 7 concludes our cubes. The authors of [5] have conducted an experimenpaper. tal investigation aimed at enhancing the performance of OLAP cube processing on SSDs. This study utilized specific types of storage disks, specifically a Seagate 160 GB 2. Related Work magnetic disk and a Patriote 64 GB Solid State Drive[33]. Ever since the authors of [17] announced that ’tape is dead, disk is tape, and flash is disk’, there has been a lot 3. Solid State Drive of research that has attempted to study the use of flash memory in conjunction with databases. Many techniques A solid-state drive (SSD) is a permanent storage medium have been studied such as query processing [18, 19, 20] that uses flash memory-based chips for data storage. Unand page layout [21]. intense work has been done to like the traditional hard disk drive (HDD), an SSD lacks extend the bufer with flash memory. The work of [ 22] moving mechanical parts and consists of an array of flash propose to use SSDs as an extension to database bufer memory cells that depend on MOS transistors (Metal Oxpool and exploit them to manage the dirty pages. Before ide Semiconductor, e.g., USB flash drive). These cells can being evicted from the bufer, the authors propose to first trap electrical current to encode the binary digits ’0’ or store the dirty pages on the SSD to avoid the communi- ’1’. Consequently, this memory type features three prication with the HDD[23, 24]. Three eviction strategies mary input/output operations: read, write, and erase. As were proposed. Clean-write: never write dirty pages previously mentioned, SSDs ofer numerous advantages to SSD, dual-write: write an evicted dirty page to both over HDDs, including greater eficiency, enhanced relia
Controller
Parser
SRAM
System Bus Host
Host Interface
Flash Interface
F l a s h C h i p s Flash Bus bility, lower energy consumption, and silent operation.
While their prices have historically been higher, they are gradually decreasing.
type of memory allows fast data addressing. Conversely,
write and erase times are long. These memories are used
to store code intended to be executed in place without
copying the code into the main memory (XIP, for Execute
In Place). They are generally used to host the operating
systems (OS) of various embedded systems such as
smartphones, etc. (
Internally, NAND flash memory comprises two primary
components: (
x-en 3.3.1. Address translation = = %
ping tables stored in the SRAM. Each entry of the table maps a logical address to a unique physical address.
There are mainly three basic address translation schemes,
each has its advantages and drawbacks: page mapping,
block mapping, and hybrid mapping [47, 41, 45]. Page
mapping is an intuitive technique that consists of directly
associating a physical page with each logical page. It
requires a large mapping table since an entry is needed for
each page of flash memory. During a write request, if the
page is not empty, the FTL chooses a free physical page,
and updates the mapping table. Upon receipt of an I/O
request from the host system addressing a page, the FTL
ifrst calculates the corresponding logical block number
(LBN) according to the following formulas:
customize or optimize its internal parameters. However,
simulation ofers a convenient solution to this limitation
by allowing users to manipulate the internal parameters
of the disk and conduct desired tests efectively. The
common methods are (
simulates the entire flash memory system, including
in(
The first equation determines the address of the physical block by dividing the logical page address (LPN) by the number of pages contained in a block (NPPB). Then, using the mapping table, the FTL determines the corresponding physical block. The second equation determines the ofset between the first page of the physical block (page 0) and the page addressed for reading or writing. The disadvantage of block mapping is the generation of extra operations in case of write requests compared to page mapping. Indeed, if a write operation requires an update of only a few pages of the block, the entire block must be re-mapped to another free physical block. Hybridmapping is an approach designed to overcome the shortcoming of the two previous techniques. Other works have been based on this method to improve mapping performance. In their work, J. Kim et al. [48] introduce a technique that employs a hybrid approach, combining block-level and page-level (coarse-grain and fine-grain) methods. This log block scheme optimizes the SRAM map size and enhances performance for both small and large write operations. Additionally, C. Park et al. [45] propose 4. Data Warehouse and OLAP a flexible group mapping method, building upon the previously presented log block scheme. This method allows A data warehouse (DW) is explicitly crafted for anafor the configuration of the degree of log block sharing lyzing data, entailing the retrieval of substantial data among diferent groups [ 49]. For the sake of space, we quantities to comprehend the relationships and trends settle for only describing FTLs used in our simulations. within it. Throughout the remainder of this study, we will Other works on FTLs can be found in [41, 50, 51, 14]. demonstrate through our experiments that data storage is more eficiently handled by storage medium utilizing 3.3.2. Flash Memory Simulation lfash memory. In the following section, we present some data warehouse fundamental concepts.
A data warehouse is a database dedicated to online
analysis for decision support. OLAP operators allow business
decision makers to extract data cubes corresponding to
analytical contexts [1]. Data cubes are data structures
that allow DW data to be grouped according to several
business functions. Each cube contains the relevant data
for a particular function. Data is consolidated, aggregated
and optimized for fast and eficient analysis. The cube
enables drilling down, slicing, dicing, or pivoting data
to observe it from various perspectives. OLAP cubes are
optimized for read access, generating reports for decision
making is much faster than with transactional systems
(OLTP).
4.1.1. Online Analytical Processing (OLAP)
investigate data anomalies and outliers, identify
opportunities, and make data-driven decisions promptly (
process large volumes of multidimensional data. Unlike Online Transaction Processing (OLTP), which focuses on managing day-to-day operational data, OLAP deals with complex queries and data analysis tasks. At the core of OLAP lies the fundamental concept of conducting multidimensional analysis, empowering users to glean insights from their data through various viewpoints, commonly known as "slicing and dicing".
has grown exponentially due to several factors: (
Faster processing is necessary to deliver timely results for
analysis (
Decision-Making Faster OLAP processing allows for more sophisticated
OLAP plays a vital role in data analysis and decision- data analysis, enabling organizations to uncover deeper
making processes for several reasons: (
Individual Query
Plans
Q1
......
In this section, we elaborate on our approach, which cen- Qn
ters on conducting comprehensive tests and evaluations
of a data warehousing and OLAP on various
configurations, including Hard Disk Drives (HDD), Solid-State MVPP Generation
Drives (SSD), and hybrid configurations combining both Simulation
technologies. Our approach aims to assess the perfor- HDD
mance of these storage options in the context of data
warehousing. The motivation comes from the fact that: BHaesuerdisStiecleCcotisotn Traces
(
Q2
Q3 sum(lo_extendedprice * lo_discount) lo_orderdate = d_datekey lo_discount between 1 and 3 and lo_quantity <
25 Lineorder sum(lo_extendedprice
* lo_discount) count(*) lo_orderdate = d_datekey the simulated storage system. Figure 3 illustrates the integration of MVPP with the initial seven queries from the SSB benchmark. Table 1 outlines the nodes suitable for potential materialization. Each node is characterized by its frequency, size, and estimated calculation cost, as determined by the general cost model, regardless of the storage medium type.
Nodes 1 through 5 depict the full scan of the base tables: lineorder (L), date (D), customer (C), supplier (S), and part (P). It’s important to highlight that the calculation cost of a node encompasses the costs associated with all its child nodes within the MVPP.
The hybridization problem is formalized as follows: (
The primary objective function within the hybridation problem is to minimize the weighted query processing cost, as defined by the formula 3. () represents the processing cost associ
Where, ated with , considering a set of intermediate nodes . The dual problem revolves around maximizing the overall execution time gain by optimizing the benefit gained when particular intermediate nodes are stored on one of the storage media (HDD or SSD). Formally:
2 MAX(GN) = ∑︁ ∑︁( * ) ⎧ ∑︀ ⎨ =1(‖‖ * ) <= , = 1...2 ⎩ ∑︀2
=1() <= 1, = 1...
Where:
(
= ∑︁( * ())/
=1
the workload would accumulate if the node were stored on either the HDD or SSD, subtracting the cost incurred when recomputing the node.
Here, represents the frequency of query , () denotes the execution cost of query accessing node , and represents the total number of queries sharing node . ID (ii) ‖‖ represents the size of node . (iii) is the storage capacity of storage medium
(ℎ for = 1 and for = 2). (iv) The decision variable is defined as follows: ized views, we focus solely on scenarios where the views are stored on either SSD or HDD media. In general, the problem of selecting materialized views is classified as an
NP-hard problem, and its resolution typically relies on = {︃1 if is stored on the storage media algorithm [12] for selecting nodes to be materialized on heuristic approaches. We utilize the simulated annealing 0 else the SSD disk. The core principle of the simulated annealing algorithm is derived from simulating the annealing 5.3. Resolution Approach process employed in the heat treatment of metals. The To emphasize the impact of incorporating SSD disks into key concept is to traverse the solution space by considan OLAP environment with the integration of material- ering both enhancing and non-enhancing solutions pro1 0 ... 1 ... ...
... 0 ... 1 ... 1 ... ...
#nodes 0 5.3.1. Simulated Annealing Iterative Process
of unselected nodes.
Require: Initial solution , Initial temperature , : substituted with a randomly selected node from the pool solution is inferior, it is accepted with a probability that diminishes over time, contingent upon the energy diference and the temperature parameter. The temperature gradually decreases, thereby regulating the likelihood of embracing suboptimal solutions throughout the process. This iterative cycle is repeated until the stopping criterion is met. For both an optimal final solution and a reasonable convergence time, the initial solution is not generated randomly but rather with nodes checking the following constraint which represents their degree of eligibility to be materialized: ()
‖‖ () = * > ℎ
(
In our experiments, we relied on the technique of
simulation to evaluate the performances of our proposal. This
can be justified by the following three reasons: (
DiskSim and FlashSim are two powerful tools that enable
the creation of simulation environments and facilitate the
evaluation of flash memory performance. The DiskSim
tool serves two primary purposes: first, to gain insights
and conduct in-depth analyses of storage system
performance, and second, to assess the efectiveness of new
architectural designs [37]. DiskSim operates with two
essential inputs: (
Ensure: = Optimal sub set of nodes 1: ← 2: () ←
compute the energy of 3: while ( > ) and ( > ℎ) do Generate a neighbor solution ′ (small change compute the energy of ′ to the current solution ) (′) ← if ∆ > 0 then ∆ = (′) − () 1 else
Accept the new solution ′
if < − Δ then else end if
Accept the new solution ′
18: end while 19: Return final solution end if Update temperature: ←
the algorithm generates a set of materialized views by
making incremental adjustments to the existing solution.
Subsequently, it assesses the disparity in energy (or
objective function) between the prevailing solution and the
newly generated solution. If the new solution is better, it
is adopted as the current solution. Conversely, if the new
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
be instantiated. (
ifle defines the system’s topology by interconnecting instances of the components to create a coherent system structure. The trace file contains the sequence of I/O requests that the system will execute during the simulation. Each log file entry includes the following details: (i) Device: The device involved in the I/O operation. (ii) Block: The specific block associated with the request. (iii) Size: The size of the I/O request. (iv) Flags: Additional flags or attributes related to the request (read/write, etc.).
seamlessly integrated into DiskSim. It emulates the behavior of an SSD disk and is implemented in the C programming language. One of its key functionalities is to assess the performance of flash memories with various Flash Translation Layers (FTLs) implemented. The FTLs available in FlashSim include pagemap (page mapping), fast2 [64], and DFTL [51]. Similar to DiskSim, FlashSim has undergone validation by comparing its results with measurements obtained from real SSD disks [49]. In FlashSim, flash memory pages are divided into sectors, typically with four sectors per page. Flash memory is implemented through a comprehensive set of data structures and functions that eficiently manage various operations, including reading and writing individual pages, erasing blocks, and handling data invalidation for both pages and blocks. Additionally, the implementation includes functions to initialize and terminate instances of the flash memory model. The flash memory is conceptually represented as an array of data structures; each structure is specifically associated with a physical block of memory. These data structures store essential information pertaining to the block, allowing for efective management and control of the flash memory system. Each structure contains the following information: (i) Block erase counter; (ii) Number of free pages; (iii) Number of invalid pages; (iv) Last page listed; In addition, a table that has a size equivalent to the number of pages present in the block. For each page within the block, it provides specific information, corresponding to the out of band area (OOB) of the pages. This information contains: (i) A bit to indicate if the page is valid; (ii) A bit to indicate if the page is free; (iii) A field (30 bits) containing the logical page number corresponding to the physical page in which it is written.
Additionally, many global variables are present containing various information like memory size and number of free blocks. In FlashSim, the FTL algorithms facilitate the computation of various metrics, including response times, page read and write counts, and block erasure counts, enabling comprehensive performance evaluation.
Figure 5 shows how I/O requests are generated from
SQL code and processed by the subsystem simulated by
DiskSim and FlashSim. The simulation of the execution
of a query involves: (
Our experiments were carried out using an open-source operating system and DBMS on a server equipped with a 2.60GHz CPU and 8GB of RAM. The dataset used was an expanded version of the TPC-H benchmark database, generated with a scale factor of 2. The schema comprises 8 tables with a cumulative size of approximately 2 gigabytes, hosted on the Oracle DBMS.
Blktrace
Traces
Results SGBD
Data CTuebxets HDD SSD Pgmp Fast DFTL Bmp
Parser SSD Interface
FTL Flash
Flashsim RR 4000 3500 3000
The characterization of the resultant workload access the logic of the FTL algorithm (
TPC-H Queries Execution Times queries over a duration. Queries deemed similar share a 400 HDD substantial number of intermediate results, such as ac350 SDD cessing identical tables, performing joins on the same 300 columns, or applying filters based on similar predicates. ) 250 While the TPCH benchmark facilitated a comprehensive (scee 200 examination of utilizing databases on SSD, the 22 queries iTm150 employed did not suficiently underscore the aspect of 100 similarity in the realm of multi-query optimization. To underscore the utility of employing materialized views 50 on SSD, we turned to the 30 queries provided by the SSB 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 benchmark [10]. The Star Schema Benchmark (SSB) was
Query specifically crafted to evaluate star schema optimization, Figure 9: TPC-H query execution time compared across HDD aiming to tackle the challenges identified in TPC-H. Its and SSD. primary purpose is to evaluate the eficiency of multitable JOIN queries within a star schema.
Figure 10 shows the costs calculated by our cost model most systems, large datasets are not going to be stored in for all queries without and with materialization. The continuous blocks, and will instead be fragmented across materialized views are those of the best solutions found the drive. As a result, the need to move a mechanical by the simulated annealing algorithm. This solution inhead around greatly inhibits a HDD. cludes 7 nodes 1 . . . 7, where ∈ {(1 ◁▷ 6), (6 ◁▷
For the third database operation, OLAP techniques 7), (1 ◁▷ 2 ◁▷ 4 ◁▷ 9), (1 ◁▷ 2 ◁▷ 8 ◁▷ 5), (1 ◁▷ 12 ◁▷
often use Joins to combine a fact table with smaller di- 13 ◁▷ 18), (1 ◁▷ 3 ◁▷ 13), (1 ◁▷ 3 ◁▷ 14 ◁▷ 15 ◁▷ 16)}
mension tables to obtain additional information. Since (see table 1 for node definition and property).
most OLAP Joins are between large tables and smaller Figure 11 depicts the overall performance of the
simutables, we assume that hashes are used to match the rows. lated workload of SSB using materialized views on
difThis operation relies on the speed of accessing the tables ferent SSDs configurations. Considering that the SSB
and also on the speed at which the rows can be matched. workload encompasses a significant volume of
physicalFor our work, we are less concerned with the second level reads characterized by a random pattern, all
conportion because it depends more on CPU speed and al- figurations of simulated SSDs produced superior results
gorithm strategy than storage drive. In addition, since compared to the HDD, even when materialized views
the storage drives are stored in the same computer (the were considered. Nevertheless, the variations observed
trace is extracted from the same workload execution), in the performance of each disk type (FTL) are
predomithere should be little diference in matching speed. As a nantly dictated by the expenses associated with garbage
result, the main diference in Join performance relies on collection and address translation operations.
access speed. Since we know that SSDs have much faster In Figure 12, although FAST demonstrates
commendaccess speeds than HDs, it can be expected that Joins will able performance, it falls short of achieving the
perforalso perform faster on solid state drives than hard drives. mance level of page-level FTL. This shortfall is attributed
Group-By statements are the final database operation to the impact of update operations in the TPC-H
workessential to OLAP techniques. As with Joins, the perfor- load, influencing overall performance due to merge
opmance of this operation in our situation relies heavily erations in FAST. Additionally, the incurred extra data
on access speed. Group-By operations is composed of read cost in log blocks contributes to the degradation of
reading from a dataset and aggregating the appropriate its read performance. DFTL operates on a two-tier
pageresults. Again, since we are using the same CPU, the level mapping. However, when the workload lacks high
time to aggregate common rows should be equivalent temporal locality, it becomes susceptible to additional
adbetween the SSD and HD. Access speed of the data is dress translation overhead. This inherent characteristic
again the main cause of the diference in performance is the primary reason why DFTL does not showcase a
between the two storage devices. In this case, we ex- relatively robust random read performance comparable
pect always that the SSD should greatly outperform HD to the ideal scenario of the page mapping scheme.
when it comes to grouping large datasets. In fact, we We applied the materialized view schemas provided
would expect the performance gap to increase as the by the simulated annealing algorithm in Oracle 21c,
emsize of the data warehouse increases in particular for ploying both real HDD and SSD disks. Figures 13 and 14
queries whose result cardinality depends on the size of depict the execution times for each query and the
durathe database. The final approach pertains to multi-query tions required to materialize the selected nodes on each
optimizers that rely on the similarity of a specific set of storage medium, respectively. Based on Figure 13, we
can infer the following: (
Before and after materialization
Without MV
With Mv
HDD alone SSD alone (pgmap)
Mv On HDD Mv on SSD with Page Map FTL Mv on SSD with Block Map FTL
Mv on SSD with DFTL Mv on SSD with FAST FTL
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12 Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22 Q23 Q24 Q25 Q26 Q27 Q28 Q29 Q30
selects materialized views as the optimal plan for exe- second-level cache and implement more sophisticated
cuting queries, and (
Validation on Oracle 21c using real disks HDD and SSD
Only SSD
Only HDD MVs on HDD MVs on SSD 45000 40000 35000 30000
In this paper, we showed that the SSD is able to perform at or above that of the HD for all experiments with the exception of Insert. The SSD had better performance for all other primitive database operations, such as Selects, Joins, and Group-By statements, that were vital to fast OLAP processing. We have also shown that the hybridization of HDD and SSD media using techniques ofered by modern DBMSs such as materialized views can enormously contribute to the optimization of the overall operation of the storage system. In the end, we believe that it is indeed feasible to perform OLAP operations on a solid state drive as compared with a traditional magnetic drive. A significant amount of work remains to improve the understanding of the SSD utilisation in data warehouse and OLAP environments.
We expect in future work: to study and understand the patterns of sequentially and randomly distributed writes and the behaviour of diferent FTLs in their management. In this work, we used mostly the simulator with its default options. We plan to implement more advanced FTLs of diferent types and garbage collectors to better control what happens inside an SSD disk to better address current shortcomings, particularly those of writing. Furthermore, we consider it essential to enhance the comprehensiveness of our experimental analysis by incorporating diferent DBMSs to validate our findings and gain insights into broader trends. Ultimately, we aim to implement optimization techniques tailored for flash memory within the DBMS, including strategies such as partitioning, indexing, and caching.