Inter-query Parallelism
It is a form
of parallelism where many different Queries or Transactions are executed in
parallel with one another on many processors.
Advantages
It increases
Transaction Throughput. That is, number of transactions executed in a given
time can be increased.
It scales up the
Transaction processing system. Hence, best suited for On-Line Transaction
Processing (OLTP) systems.
Supported Parallel Database Architectures
It is easy to
implement in Shared Memory Parallel System. Lock tables and Log information are
maintained in the same memory. Hence, it is easy to handle those transactions
which shares locks with other transactions. Locking and logging can be done
efficiently.
In other
parallel architectures like Shared Disk and Shared Nothing, the locking and
logging must be done through message passing between processors, which is
considered as costly operation when compared Shared Memory Parallel
architecture. Cache coherency
problem would occur.
Example Database systems which support Inter-query Parallelism
Oracle 8 and
Oracle Rdb
Intra-Query Parallelism
It is the
form of parallelism where Single Query is executed in parallel on many
processors.
Advantages
To speed up a
single complex long running queries.
Best suited
for complex scientific calculations (queries).
Supported Parallel Database Architectures
SharedMemory, Shared Disk and Shared Nothing parallel architectures are supported. We
need not worry about locking and logging as because it involves parallelizing
single query.
Types
Intra-operation
parallelism – the process of speeding up a query through parallelizing the execution
of individual operations. The operations which can be parallelized are Sort,
Join, Projection, Selection and so on.
Inter-operation
parallelism – the process of speeding up a query through parallelizing various operations which are part of the query. For example,
a query which involves join of 4 tables can be executed in parallel in two
processors in such a way that each processor shall join two relations locally
and the result1 and result2 can be joined further to produce the final result.
Example Database systems which support Intra-query Parallelism
Informix,
Terradata
Sir any example for Inter-query parallelism
ReplyDeleteInter-query parallelism is about executing different queries in different processors. For example, if we have two processors, execute one query in one processor and the other query in the other processor concurrently.
DeleteIts really good sir
DeleteSir, what is the justification that can be given to the fact that the overhead cost of performing non-parallel operations will be outvalued by performing operations in intra-operation parallelism? What are the possible conditions?
ReplyDeleteAbhishek, we use parallelism for increasing the speed of execution of the query. Normally it would be required when we have to deal with huge data. Intra-operation parallelism is about processing a single operation (like sorting, joining, etc) in parallel. When we would try to execute these operations on huge amount of data in a single machine, we need to batch process the data. that is, for example, first 10000 records and second 10000 records, and so on. At the end, operation can be successfully performed but at the cost of time. When we perform the same in parallel, we do like batch processing but simultaneously at the same time.
DeleteWe would prefer parallelism when we need to work on huge amount of data, or we need to perform the query faster, or the data were already partitioned into different machines etc.
what practical examples can we use to show inter query and intra query
ReplyDeleteOLTP systems, in general use inter-query parallelism (more small queries)
DeleteDecision support systems that use large complex queries would use intra-query parallelism.
You cannot insist the type of parallelism hence no practical example. However, you can use COMMENT in SQL to decide the degree of parallelism as given below;
SELECT /*+ PARALLEL 4*/ * FROM courses ORDERBY credit;
In this query PARALLEL 4 means the degree of parallelism.
To view the output of parallel execution you would use EXPLAIN PLAN FOR tool in ORACLE for example.
Hii,sir,the two parallism process is wanted in comparable taable
ReplyDelete