ADVANCED DATABASES
Unit 3 ( Module 1 )
Advanced Query Optimization
Advanced Query Optimization in an advanced database refers to techniques and
strategies used to improve the performance of queries, particularly when working
with large and complex datasets. Query optimization is crucial for speeding up
response times, reducing resource consumption, and ensuring the scalability of the
database system.
Here are some key advanced query optimization techniques :
1. Indexing: Improve search speed.
2. Query Rewrite: Optimize query logic.
3. Join Optimization: Optimize join conditions and order.
4. Aggregation Optimization: Use pre-aggregation and efficient group-by.
5. Materialized Views: Cache complex query results.
6. Partitioning: Split large tables for better query performance.
7. Parallel Execution: Speed up by using multiple CPU cores.
8. Execution Plan Analysis: Identify and fix inefficiencies in the query plan.
9. Optimize I/O: Minimize unnecessary data retrieval.
By combining these techniques, you can dramatically improve the performance of
your queries, even when working with large datasets in advanced databases.
Volcano/Cascades Framework For Query Optimization
The Volcano and Cascades frameworks are advanced query optimization techniques
used by databases to improve the efficiency of executing queries. They focus on
choosing the best way to process a query, considering various execution strategies,
and ensuring that the query runs as quickly and efficiently as possible.
1. Volcano Framework
The Volcano framework is based on the idea of using iterators to process queries in
a systematic way. It works like a pipeline, where different operators (like JOIN,
FILTER, or GROUP BY) work together to process a query step by step.
Key idea: The Volcano model treats operators as reusable building blocks that can
be arranged in different ways to form an optimized execution plan.
Example:
o A query might first filter the rows based on a condition, then join them with
another table, and finally aggregate the results. The Volcano framework will
explore the most efficient way to do each of these operations.
2. Cascades Framework
The Cascades framework is an extension of the Volcano framework and adds more
sophisticated cost-based optimization. The idea behind Cascades is to search for the
most efficient execution plan by trying multiple alternatives and choosing the one
with the least cost (in terms of time and resources).
Key idea: Cost-based optimization is the heart of Cascades. It tries to find the best
execution plan by transforming the query and calculating the cost of each
transformation. The one with the least cost is chosen.
Example:
A simple query with a JOIN can be rewritten in multiple ways (e.g., using different
join algorithms like nested loops, hash joins, or merge joins). Cascades would
explore these alternatives and pick the most cost-effective one.
Multi Query Optimization
Multi-Query Optimization is a technique used in advanced databases to optimize
multiple queries that are executed together or in close sequence, instead of
optimizing each query individually. The idea is to improve performance by sharing
resources and reusing results across queries to reduce redundancy and save time.
In simple words, multi-query optimization is about making multiple queries run
faster by finding ways to combine their work or avoid repeating operations.
Simple Example:
Imagine you have two queries:
1. Query A: Get the list of customers who made a purchase in the last month.
2. Query B: Get the total amount spent by those customers.
Without multi-query optimization, the database would run separate operations: first
to find the customers (Query A) and then to find their spending (Query B).
With multi-query optimization, the database might recognize that both queries
require the same data (the list of customers who made purchases). Instead of
running the customer-fetching part twice, the system can fetch the customer data
once and then reuse it to calculate the total spending in Query B.
Materialized Views & View Maintenance
1. Materialized Views
A materialized view is a precomputed view of data that is stored in the database.
Instead of recalculating complex queries every time you run them, a materialized
view stores the result of the query like a table.
Querying a materialized view is much faster than recalculating the query every time.
By storing the result of complex calculations, you save resources like CPU and
memory, as the database doesn't have to do the work repeatedly.
Example:
Imagine you have a query that sums sales for each region and you use that result in
many reports. Instead of calculating the sum each time the report is run, you create
a materialized view to store that sum. Whenever you need the sum, you can directly
access the materialized view.
How does it work?
1. The first time you run the query, the database computes and stores the result as a
materialized view.
2. Later, whenever you need that same data, the database just retrieves the
precomputed result from the materialized view instead of recomputing everything.
2. View Maintenance
Since the data in a materialized view is precomputed, it needs to be updated
whenever the underlying data changes (e.g., new sales are added or old data is
modified). This process of keeping the materialized view in sync with the underlying
data is called view maintenance.
There are two main ways to handle view maintenance:
a) Complete Refresh
How it works: The database completely recomputes the materialized view from
scratch.
b) Incremental Refresh (Fast Refresh)
How it works: The database only updates the parts of the materialized view that are
affected by changes in the underlying data. This is faster because it avoids
recalculating everything.
Benefits of Materialized Views & View Maintenance:
1. Speed
2. Efficiency
3. Consistency
Example Scenario:
Let’s say you run a store and have a materialized view that calculates the total sales
for each region. Over time, your sales data updates with new transactions. When a
customer buys something, the materialized view needs to be updated to reflect the
new total sales for the region.
If you use Complete Refresh, every time there’s an update, the system completely
recomputes the total sales from scratch, which could take time if the data is large.
If you use Incremental Refresh, the system just adds the new sales to the existing
total, which is much faster.
In Simple Words:
Materialized Views store the result of a query as if it were a table, so that when you
need the data again, the database can quickly give it to you without recalculating the
entire query.
View Maintenance makes sure the materialized view stays updated when the
underlying data changes, either by recalculating it entirely (complete refresh) or just
updating the parts that changed (incremental refresh).
Unit 3 ( Module 2 )
Index & View Selection
In an advanced database, index selection and view selection are techniques used to
improve the speed and efficiency of queries. Both help databases find and retrieve
data more quickly and effectively, which is especially important when dealing with
large amounts of data.
1. Index Selection
An index is a special data structure (like a table of contents in a book) that helps the
database quickly find specific rows without scanning the entire table. Indexes are
used to speed up queries that search for data based on certain columns.
Instead of searching every row in a table, the database can use the index to directly
jump to the relevant rows, which makes searching much faster.
The database chooses the right index to use for each query, improving the speed of
retrieving data.
How Index Selection Works:
Which Columns to Index: When creating an index, you choose columns that are
often used in search conditions (like in WHERE clauses or JOIN conditions).
o Example: If you frequently query a table for customers by their last_name, it
would be useful to create an index on the last_name column. This will make
searches by last name much faster.
Types of Indexes:
o Single-column Index: An index on a single column (e.g., last_name).
o Composite Index: An index on multiple columns (e.g., last_name and
first_name).
o Unique Index: Ensures that all values in the indexed column are unique (e.g.,
indexing customer_id ensures no two customers have the same ID).
o Full-Text Index: For searching large text fields efficiently.
Example:
If you often query a database for employees who work in a specific department,
creating an index on the department_id column will make the search much faster, as
the database doesn't have to scan the whole table to find matching rows.
2. View Selection
A view is a virtual table based on the result of a query. It doesn’t store data itself but
allows you to create a query that can be treated like a table. Views are helpful for
simplifying complex queries and organizing frequently used data.
Views allow you to create complex queries that you can reuse, instead of writing the
same query multiple times.
Views can help organize data and provide a simplified view of it, especially when
working with multiple tables.
Improve Security: You can use views to give users access to only certain columns or
rows of data, without exposing the entire table.
How View Selection Works:
Choosing Which Queries to Make into Views: A view is selected based on frequently
used queries. If there’s a complex query that gets run often, turning it into a view
can make it easier to use and faster to access.
o Example: If a query joins several tables to show the list of employees with
their department details, you can create a view that does this for you, so
every time you need that data, you don’t have to run the complex join.
Materialized Views: A special kind of view called a materialized view stores the
result of the query like a table, so it doesn't need to be recomputed every time. This
can speed up queries but requires maintenance when the underlying data changes.
Example:
Let’s say you often need a report showing the total sales for each region. Instead of
writing the complex SQL query for this report every time, you can create a view that
stores the logic, so each time you need the report, you just query the view instead.
In Simple Words:
Index Selection: Choosing the right columns to index (like creating a table of
contents) to make queries faster by avoiding full table scans.
View Selection: Creating virtual tables (views) to simplify complex queries and
organize frequently used data.
Database Tuning
Database Tuning is the process of improving the performance of a database system
to make it faster, more efficient, and more resource-friendly. The goal of database
tuning is to ensure that the database can handle large amounts of data and complex
queries without slowing down or using too many resources like CPU, memory, and
disk space.
In simple words, database tuning is about fine-tuning your database to ensure it
runs smoothly and performs as fast as possible.
Database Tuning is about making sure your database is fast and efficient.
It involves improving queries, indexes, schema design, configuration settings, and
using caching and partitioning to improve performance.
Regularly monitoring your database helps you spot and fix performance issues
before they become bigger problems.
In short, database tuning helps your database run more smoothly and handle large
amounts of data or many users without slowing down.
Adaptive Query Processing & Optimization
Adaptive Query Processing :
o Adaptive Query Processing is a smart way for the database to change how it
executes queries based on real-time conditions. If something goes wrong or if
things change during execution (like available resources or data size), the
database can adjust its plan to make sure the query runs as efficiently as
possible. This makes the system faster, more efficient, and better at handling
unexpected issues during query execution.
o Adaptive Query Processing (AQP) in advanced databases uses runtime
statistics to dynamically adjust query execution plans for improved
performance, addressing issues like missing statistics, unexpected
correlations, and dynamic data.
Adaptive query optimization :
o Adaptive query optimization allows the optimizer to make run-time changes to
execution plans and uncover new information that can lead to improved
statistics.
o Adaptive Query Optimization in an advanced database refers to a dynamic
approach to query optimization, where the database can adjust its query
execution plan during runtime based on real-time information,
o The goal is to improve performance by making the system more flexible and
responsive to changes in the environment while the query is being processed.
o Adaptive Query Optimization in an advanced database is a smart system that
allows the database to change how it processes a query while it’s running based
on what’s happening at the moment. Instead of sticking to one fixed plan for
executing a query, the database can adjust its strategy during execution if it
detects that things aren’t going as expected.
