Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Bloom filters and view using UNION ALL

Ahmed AANGOURSep 7 2020 — edited Sep 9 2020

Hi,

I have a query with the following plan. The database version is 12.1.0.2 and it's running in an Exadata X6

----------------------------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 17625 | 87749 (61)| 00:00:14 | | | | | |

|* 1 | PX COORDINATOR | | | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 17625 | 87749 (61)| 00:00:14 | | | Q1,01 | P->S | QC (RAND) |

|* 3 | FILTER | | | | | | | | Q1,01 | PCWC | |

|* 4 | HASH JOIN | | 1 | 17625 | 87749 (61)| 00:00:14 | | | Q1,01 | PCWP | |

| 5 | PX RECEIVE | | 15468 | 5135K| 59 (6)| 00:00:01 | | | Q1,01 | PCWP | |

| 6 | PX SEND BROADCAST | :TQ10000 | 15468 | 5135K| 59 (6)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |

|* 7 | HASH JOIN | | 15468 | 5135K| 59 (6)| 00:00:01 | | | Q1,00 | PCWP | |

| 8 | PART JOIN FILTER CREATE | :BF0000 | 14614 | 1141K| 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |

| 9 | PARTITION RANGE ITERATOR | | 14614 | 1141K| 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWC | |

|* 10 | TABLE ACCESS STORAGE FULL| T_INSTANCES | 14614 | 1141K| 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |

| 11 | PX PARTITION RANGE AND | | 40352 | 10M| 57 (6)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,00 | PCWC | |

|* 12 | TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART | 40352 | 10M| 57 (6)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,00 | PCWP | |

| 13 | VIEW | | 16M| 258G| 87681 (62)| 00:00:14 | | | Q1,01 | PCWP | |

| 14 | UNION-ALL | | | | | | | | Q1,01 | PCWP | |

|* 15 | FILTER | | | | | | | | Q1,01 | PCWC | |

| 16 | PX PARTITION RANGE ITERATOR| | 1 | 341 | 26894 (43)| 00:00:05 | KEY | KEY | Q1,01 | PCWC | |

|* 17 | TABLE ACCESS STORAGE FULL | T_EXECS_PART | 1 | 341 | 26894 (43)| 00:00:05 | KEY | KEY | Q1,01 | PCWP | |

|* 18 | FILTER | | | | | | | | Q1,01 | PCWC | |

| 19 | PX PARTITION RANGE ITERATOR| | 5540K| 877M| 19929 (71)| 00:00:04 | KEY | KEY | Q1,01 | PCWC | |

|* 20 | TABLE ACCESS STORAGE FULL | T_MARKET_MESSAGES_PART | 5540K| 877M| 19929 (71)| 00:00:04 | KEY | KEY | Q1,01 | PCWP | |

|* 21 | FILTER | | | | | | | | Q1,01 | PCWC | |

| 22 | PX PARTITION RANGE ITERATOR| | 5327K| 1498M| 22054 (75)| 00:00:04 | KEY | KEY | Q1,01 | PCWC | |

|* 23 | TABLE ACCESS STORAGE FULL | T_ORDER_DETAILS_PART | 5327K| 1498M| 22054 (75)| 00:00:04 | KEY | KEY | Q1,01 | PCWP | |

|* 24 | FILTER | | | | | | | | Q1,01 | PCWC | |

| 25 | PX PARTITION RANGE ITERATOR| | 5209K| 1336M| 18804 (62)| 00:00:03 | KEY | KEY | Q1,01 | PCWC | |

|* 26 | TABLE ACCESS STORAGE FULL | T_SEND_MESSAGES_PART | 5209K| 1336M| 18804 (62)| 00:00:03 | KEY | KEY | Q1,01 | PCWP | |

----------------------------------------------------------------------------------------------------------------------------------------------------------

For some reasons, I would to use Bloom Filters (BF) for joining the 4 tables of the view (Operation 13). The join is performed on 3 columns and these 3 columns exist in the 4 tables involved in the view.

I tried to force the use of BF by using PX_JOIN_FILTER hint but it didn't work. I had the feeling that it was due to the UNION ALL operation so I tried the same query but with only one table in the view and the BF are used (the view is merged):

------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1381 | 864K| 4467 (75)| 00:00:01 | | | | | |

| 1 | PX COORDINATOR | | | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10003 | 1381 | 864K| 4467 (75)| 00:00:01 | | | Q1,03 | P->S | QC (RAND) |

|* 3 | HASH JOIN | | 1381 | 864K| 4467 (75)| 00:00:01 | | | Q1,03 | PCWP | |

| 4 | JOIN FILTER CREATE | :BF0001 | 9278 | 3116K| 14 (8)| 00:00:01 | | | Q1,03 | PCWP | |

| 5 | PART JOIN FILTER CREATE | :BF0000 | 9278 | 3116K| 14 (8)| 00:00:01 | | | Q1,03 | PCWP | |

| 6 | PX RECEIVE | | 9278 | 3116K| 14 (8)| 00:00:01 | | | Q1,03 | PCWP | |

| 7 | PX SEND BROADCAST | :TQ10002 | 9278 | 3116K| 14 (8)| 00:00:01 | | | Q1,02 | P->P | BROADCAST |

|* 8 | HASH JOIN BUFFERED | | 9278 | 3116K| 14 (8)| 00:00:01 | | | Q1,02 | PCWP | |

| 9 | JOIN FILTER CREATE | :BF0003 | 2896 | 243K| 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |

| 10 | PART JOIN FILTER CREATE | :BF0002 | 2896 | 243K| 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |

| 11 | PX RECEIVE | | 2896 | 243K| 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |

| 12 | PX SEND HASH | :TQ10000 | 2896 | 243K| 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |

| 13 | PX BLOCK ITERATOR | | 2896 | 243K| 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWC | |

|* 14 | TABLE ACCESS STORAGE FULL| T_INSTANCES | 2896 | 243K| 2 (0)| 00:00:01 | KEY | KEY | Q1,00 | PCWP | |

| 15 | PX RECEIVE | | 8176 | 2059K| 12 (9)| 00:00:01 | | | Q1,02 | PCWP | |

| 16 | PX SEND HASH | :TQ10001 | 8176 | 2059K| 12 (9)| 00:00:01 | | | Q1,01 | P->P | HASH |

| 17 | JOIN FILTER USE | :BF0003 | 8176 | 2059K| 12 (9)| 00:00:01 | | | Q1,01 | PCWP | |

| 18 | PX BLOCK ITERATOR | | 8176 | 2059K| 12 (9)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,01 | PCWC | |

|* 19 | TABLE ACCESS STORAGE FULL | T_PRODUCTS_PART | 8176 | 2059K| 12 (9)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,01 | PCWP | |

| 20 | JOIN FILTER USE | :BF0001 | 1080K| 306M| 4453 (75)| 00:00:01 | | | Q1,03 | PCWP | |

| 21 | PX BLOCK ITERATOR | | 1080K| 306M| 4453 (75)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,03 | PCWC | |

|* 22 | TABLE ACCESS STORAGE FULL | T_ORDER_DETAILS_PART | 1080K| 306M| 4453 (75)| 00:00:01 |KEY(AP)|KEY(AP)| Q1,03 | PCWP | |

------------------------------------------------------------------------------------------------------------------------------------------------------------

So I would like to know if my assumption is correct: does UNION ALL prevents from using Bloom Filters? Is there any way to force them in my query?

The query looks like this:

SELECT

...........

FROM (SELECT /*+ parallel(8) */

..............

FROM force.t_execs_part e

UNION ALL

SELECT /*+ parallel(8) */

..............

FROM force.t_market_messages_part

UNION ALL

SELECT /*+ parallel(8) */

..........

FROM force.t_order_details_part

UNION ALL

SELECT /*+ parallel(8) */

...........

FROM force.t_send_messages_part

) t

,force.t_instances i

,force.t_products_part p

WHERE i.t_dt = t.t_dt

AND i.i_id = t.i_id

AND i.t_dt = p.t_dt

AND i.i_id = p.i_id

AND t.productid = p.productid

and i.t_dt = trunc(sysdate-5)

and p.isin like '%XXXXXX%'

and TO_CHAR(t.pm_timestamp,'HH24:MI:SS') between '09:00:00' and '18:00:00'

;

For the PX_JOIN_FILTER hint I tried both:

- using the alias of the view: PX_JOIN_FILTER(@"SEL$1" "T"@"SEL$1")

- using the alias of the tables: PX_JOIN_FILTER(@"SEL$4" "T_ORDER_DETAILS_PART"@"SEL$4")

Thanks for helpling

Cheers,

Ahmed

This post has been answered by Jonathan Lewis on Sep 7 2020

Jump to Answer

Added on Sep 7 2020

#general-database-discussions

13 comments

1,007 views

Oracle Database Discussions

Bloom filters and view using UNION ALL

Comments

Post Details