⭐ INTER AND INTRA QUERY PARALLELISM
Parallel databases use parallel processing to speed up query execution.
Two fundamental types of parallelism in DBMS are:
- Inter-Query Parallelism
- Intra-Query Parallelism
These determine how multiple queries or a single query can utilize multiple processors at the same time.
⭐ 1. INTER-QUERY PARALLELISM
✔ Definition
Inter-Query Parallelism means executing multiple queries simultaneously, where each query runs on a separate processor or a set of processors.
Here, each full SQL query is treated as a separate task.
✔ Explanation
If many users submit queries at the same time, the DBMS assigns each query to a different CPU, disk, or node.
Each query runs independently → increasing system throughput.
⭐ Advantages
✔ 1. Improved Throughput
More users can be served at the same time.
✔ 2. Independent Execution
Queries do not depend on each other.
✔ 3. Best for OLTP
Online Transaction Processing (banking, ticketing, etc.) has many small queries → ideal for inter-query parallelism.
✔ 4. Good Load Balancing
Queries are distributed evenly across processors.
⭐ Disadvantages
✘ Helps throughput but NOT the response time of a single query
✘ Not effective for very large analytical queries
✘ Works poorly when all queries are heavy and long
⭐ Example
Suppose three users run three separate queries:
Q1: SELECT * FROM Customers;
Q2: SELECT * FROM Orders WHERE amount > 10000;
Q3: UPDATE Products SET price = price * 1.10;
DBMS may run Q1 on CPU1, Q2 on CPU2, Q3 on CPU3 simultaneously.
Thus, execution of multiple queries in parallel = Inter-Query Parallelism.
⭐ 2. INTRA-QUERY PARALLELISM
✔ Definition
Intra-Query Parallelism means parallelizing a single SQL query by breaking it into multiple smaller tasks that are executed on different processors at the same time.
It improves the speed of a single large query.
⭐ Types of Intra-Query Parallelism
Intra-query parallelism has two major types:
- Intra-Operation Parallelism
- Inter-Operation Parallelism (Pipeline Parallelism)
Let’s discuss both.
⭐ A. Intra-Operation Parallelism
A single operation inside a query (scan, join, sort) is parallelized.
✔ Example: Parallel Table Scan
A table is partitioned across 4 disks; each CPU scans its partition:
CPU1 scans Partition 1
CPU2 scans Partition 2
CPU3 scans Partition 3
CPU4 scans Partition 4
Result → 4× faster scan
✔ Common Intra-Operation parallel tasks:
- Parallel Selection
- Parallel Projection
- Parallel Join (hash join, merge join)
- Parallel Sorting
- Parallel Aggregation
✔ Benefits:
- Greatly speeds up large queries
- Ideal for OLAP/data warehousing
⭐ B. Inter-Operation Parallelism (Pipeline Parallelism)
Different operations of a query tree execute simultaneously.
✔ Example:
A query plan:
σ ( Salary > 50000 )
|
Hash Join
|
Table Scan
Pipeline parallelism allows:
- Table Scan → outputs tuples while
- Hash Join → starts processing them, and
- Selection → begins filtering simultaneously
This reduces overall query time.
⭐ Comparison: Intra-Operation vs Inter-Operation
| Type | What is parallelized? | Benefit |
|---|---|---|
| Intra-Operation | A single operation | High speed for large operations |
| Inter-Operation (Pipeline) | Multiple operators | Reduced total query time |
⭐ Advantages of Intra-Query Parallelism
✔ 1. Improves response time of large queries
✔ 2. Essential for OLAP, Data Warehouse, Big Data
✔ 3. Efficient for operations like JOIN, SORT, GROUP BY
✔ 4. Uses multiple processors to share workload
⭐ Disadvantages
✘ More complex than inter-query parallelism
✘ Synchronization overhead
✘ Required balanced data partitioning (data skew slows down processors)
✘ Requires advanced optimizer and parallel query engine
⭐ INTER-QUERY vs INTRA-QUERY PARALLELISM
| Feature | Inter-Query | Intra-Query |
|---|---|---|
| What is parallel? | Multiple queries | One large query |
| Goal | Increase throughput | Improve query response time |
| Use case | OLTP | OLAP, Big queries |
| Complexity | Low | High |
| Speedup | For many users | For individual large queries |
⭐ Combined Approach in Modern DBMS
Modern parallel DBMS (Oracle, PostgreSQL, SQL Server, Teradata) use both:
- Inter-query parallelism for handling many users
- Intra-query parallelism for accelerating analytical queries
⭐ Perfect 5–6 Mark Short Answer
Inter-Query Parallelism executes multiple individual queries simultaneously on different processors, improving system throughput and supporting large multi-user workloads.
Intra-Query Parallelism divides a single large query into sub-tasks executed in parallel, improving the response time of complex queries. It includes intra-operation parallelism (parallel scanning, joins, sorting) and inter-operation or pipeline parallelism. Both forms increase database performance but target different types of workloads.