BLOOM FILTER AND ITS POSITIVE IMPACT ON HASH JOINS

HASH JOIN :

Hash Joins is one of the method of joining two tables based on the join condition mentioned in the query.

It is important to mention the smaller table  in leading position in the sql query when joining two tables. The optimizer always picks the leading table/first table as per the order mentioned in the query.If the execution plan does not pick the smaller table as the leading table then there will be more load/activity at the joining step then the plan which might impact the overall elapsed time of the query. We can explicitly ask the optimizer to pick a particular table as leading table using a oracle hint Ex: /*+ leading(table name)*/

 How Hash Join works?

 1) First Optimizer scan the smaller table and creates a hash table  by applying hash function on the column to be joined. 

 2) Now the optimizer scans the second table applies a hash function(applies on the column to be joined) to get the hash value for each record in the second table.

 3) Now the Hash value of each record of the second table is compared with Hash table of the first table per matching records. 

 4) Now all the records are matched will be joined and sent to result set.


BLOOM FILTER;

Bloom Filter is one of the filter used in the oracle query execution for the Hash joins to reduce the number of records sent from the trailing table for Hash joining. 

Normally without Bloom Filter we calculate Hash value for each record in the second table and send it for comparison with the leading table which in turn increases the user I/O/actual rows accessed from the trailing table. 

Now with Bloom Filter, we gather extra information called Bit Vector of the joined column from the both tables. 

How Bloom Filter works?

1) Optimizer scans the smaller table and creates a Hash table and collects the Bit Vector of the column to be joined.

2) Now Optimizer scans the second table, generates Hash value for each record, additionally Bit Vector information is also gathered for each column of the record.

3) During this meantime, the Hash table and Bit Vector information of the leading table will be sent for comparison.

4) With the availability of Bit Vector information, its value of leading table compared with the Bit Vector value of the trailing table and the records from the both tables whose Bit Vector values are same are sent further for Hash joining. 

5) With Bit Vector comparison the records which used to get rejected earlier at the Hash joining condition are now rejected while scanning 

the second table itself. As a result, the count of records which are sent for Hash joining will drastically decreases which in turn saves the time required to get the records of the table. 

We can enable and disable the bloom filter by using oracle hints px_join_filter(table) and no_px_join_filter

select /*+ parallel(8) */ count( * )  from  FACT, DIMENSION where dimension.col1=1 and dimension.col2=fact.col2;

EXECUTION PLAN WITH BLOOM FILTER:


EXECUTION PLAN WITHOUT BLOOM FILTER:


From above two execution plans, we can see that without Bloom Filter, Optimizer is accessing 1000K records from the fact table and is been sent for Hash joining. But with Bloom filter, only 102 records are being accessed and sent for Hash joining.

Remember in both the cases, the final result set has only 100 records. This is how bloom filter reduces the user i/o of trailing table by rejecting the records based on BIT VECTOR value before sending it for hash value comparision.

Comments

Post a Comment

Popular posts from this blog

Oracle GoldenGate Microservices Architecture - 1

Image
Data Replication techniques has gained importance in the market with the need of maintaing production data in other databases like Postgre, Mysql etc.. to avoid heavy license fee charged by top rdbms providers like Oracle, Sql Server .Goldengate is one of the most famous and popular software which can be used for data replication between homogenous and hetrogenous databases.  GoldenGate supports two types of architectures : Classic Architecture Microservice Architecture CLASSIC ARCHITECTURE It is first architecture supported by the Goldengate when it was introduced 20 years ago. Goldengate Classic architecture drawn many advantages in the market when customers are facing problems when replicating data between heterogenous databases. Classic Architecture has below components: Manager - Is the master process running in the Goldengate which takes care of all other slave                 process like Extract, Datapump, Replicat and Collector....
Read more

All About Oracle Database Block

Image
  INITTrans  Whenever a DML transaction wants to modify a block , it should inform the block by sending 'interested to modify' message to the block .Technically speaking, a dml transaction will always send message to the block , that it is interested to modify the block . This message is stored in ITL slots ,which is preallocated in the block header. Transactions whose entry is there in this ITL slots can only modify the block.This ITL slots are controlled by INITRANS and MAXTRANS values, the inittrans controls the minimum number of slots that will be allocated in the block header. Honestly speaking, the number of ITL slots is equal to number of DML transactions that can happen parallely at one time. Each ITL slot occupies 24 bytes of space in the block. Inittrans values is 1 for table and 2 for index by default. MAXTrans Maxtrans is the maximum number of ITL slots that can be allocated in the block . When the block runs out of this ITL slots and there is enough free space in ...
Read more

ORACLE CLOUD INFRASTRUCTURE(OCI) FOR ABSOLUTE BEGINNER'S

Image
CLOUD CONCEPTS   Today, we will discuss about the Oracle cloud Concepts . Like all the Cloud Technologies, Oracle cloud which is called as OCI offers variety of services with better SLA's and performance.OCI is considered as most robust cloud technology because of its high security and high availability. Lets discuss few cloud basic terms before jumping in to OCI Architecture. On Demand self service -- In cloud, the services are provisioned automatically without human interaction with service provider. Broad network access   -- The cloud services are accessed by broad network which follow standard mechanisms. Resource pooling            -- In cloud, all the services of provider are pooled at one place and this are allocated to multiple customers on their requirement. Rapid Elasticity              -- In cloud , services are scaled up and down automatically and sometimes without any impact to the running env...
Read more