---
title: "Oracle parallel query hints - part 3: PX_JOIN_FILTER"
description: In part three of his series on Oracle Parallel Query Hints, Christo Kutrovsky looks at PX_JOIN_FILTER.
---

[Blog | Pythian ](https://www.pythian.com/blog)

# [Oracle parallel query hints - part 3: PX_JOIN_FILTER](https://www.pythian.com/blog/oracle-parallel-query-hints-reference-series-part-3-px_join_filter)

 Written by [Christo Kutrovsky](https://www.pythian.com/blog/author/christo-kutrovsky) | Apr 19, 2018 4:00:00 AM

In part one of this series on **Oracle Parallel Query Hints**, I looked at `PQ_DISTRIBUTE`, and part two was about `PQ_REPLICATE`. Now, for the third part in the series, I will take a look at `PX_JOIN_FILTER`.

The `PX_JOIN_FILTER` is a very interesting hint. It has been around since Oracle 10.2, yet it remains undocumented even after more than 10 years. While its behavior is generally understood, the introduction of Oracle Exadata has made this hint significantly more relevant due to specific storage efficiencies.

## Understanding the purpose of join filters

The **Join Filter** is a feature used during data re-shuffling between parallel query (PX) processes. It allows each parallel query process to "pre-filter" data before sending it over by using a **Bloom filter**.

Typically, this occurs with **HASH** distributions. However, in Exadata environments, this functionality also extends to the **BROADCAST** feature by leveraging storage indexes. This optimization reduces the volume of rows exchanged between processes, leading to better performance across different configurations:

- **Single Server (Non-RAC):** Reduces CPU usage and elapsed time.
- **Oracle RAC:** Reduces interconnect network traffic and CPU time.
- **Oracle Exadata:** Pushes the Bloom filter down to the storage nodes (Smart Scan). Filtering happens before data even reaches the compute nodes, utilizing the storage cells' CPUs.

## Comparing execution plans

To illustrate the impact, let's look at an example. First, we examine a plan where join filters are explicitly disabled using `NO_PX_JOIN_FILTER`

```
select /*+PARALLEL(8) NO_PX_JOIN_FILTER(T2) */ * from tlarge t1  join tlarge_two t2 on t2.id=t1.id  where t1.mod5_id=1; 
```

In the resulting plan, notice that `TLARGE_TWO` (Id 11) is scanned and all 100K rows are sent via `PX SEND HASH` (Id 9) to the join operator.

### Enabling the join filter

Now, let's enable the filter with the `PX_JOIN_FILTER` hint

```
select /*+PARALLEL(8) PX_JOIN_FILTER(T2) */ * from tlarge t1  join tlarge_two t2 on t2.id=t1.id  where t1.mod5_id=1; 
```

In this optimized plan, the following steps occur:

1. **PQ Set A** reads `TLARGE`, applies the `mod5_Id=1` predicate, and creates a Bloom filter (`:BF0000`) at step 4 (**JOIN FILTER CREATE**).
2. The Bloom filter is shared among the parallel processes.
3. When reading `TLARGE_TWO`, the processes apply the **JOIN FILTER USE** at step 11. This checks for "presence" in the Bloom filter before even attempting to send rows to the next set of slaves.

## Bloom filters vs. partition elimination

It is important not to confuse **Join Filters** with **Bloom Filter Partition Elimination**. While both rely on the same mathematical structure, they serve different roles.

In the example below, you can see both `JOIN FILTER` and `PART JOIN FILTER` (Id 5) acting simultaneously. The `PART JOIN FILTER` is used specifically to skip reading certain partitions entirely, which is a massive win for large delta loads.

SQL

```
select /*+PARALLEL(8) PX_JOIN_FILTER(t2) */ * from tlarge t1  join tlarge_p t2 on t2.id=t1.id  where t1.mod5_id=1; 
```

## Conclusion

`PX_JOIN_FILTER` is a powerful tool for improving parallel query performance. While the optimizer is supposed to use it automatically based on cost, manually hinting it can be vital when the optimizer doesn't recognize the potential for reduction, especially in complex ETL designs.

## Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?

 

[View full post](https://www.pythian.com/blog/oracle-parallel-query-hints-reference-series-part-3-px_join_filter)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Christo Kutrovsky"
  },
  "dateModified" : "2026-02-24T14:23:13.272Z",
  "datePublished" : "2018-04-19T04:00:00Z",
  "headline" : "Oracle parallel query hints - part 3: PX_JOIN_FILTER",
  "image" : {
    "@type" : "ImageObject",
    "height" : 60,
    "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
    "width" : 60
  },
  "mainEntityOfPage" : "https://www.pythian.com/blog/oracle-parallel-query-hints-reference-series-part-3-px_join_filter",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Pythian Blog"
  }
}
```