Hi,
Oracle EE 11.2.0.4, Exadata
Given below is a very simple test query:
explain plan for
select * from t1
where (c1, c2) in
(select c1, c2 from gtt_t2);
We have two very similar schemas, holding these two objects. We copy the stats across the schemas, so that all the objects have the same stats.
However, for partitioned tables we copy the same stats on each partition. Because of this, the global stats for partitioned tables and indexes may differ.
T1 is a table with sub-partitions and GTT_T2 is a global temp table.
The plan on one schema is:
------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 42M | | | |
| 1 | HASH JOIN RIGHT SEMI | | 502K | 457M | 42M | 12:21:34 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 1 | 119 | 2 | 00:00:01 | | |
| 3 | TABLE ACCESS STORAGE FULL | GTT_T2 | 1 | 119 | 2 | 00:00:01 | | |
| 4 | PARTITION RANGE ALL | | 1421M | 1128G | 42M | 12:19:26 | 1 | 247 |
| 5 | PARTITION HASH JOIN-FILTER | | 1421M | 1128G | 42M | 12:19:26 | :BF0000| :BF0000|
| 6 | TABLE ACCESS STORAGE FULL | T1 | 1421M | 1128G | 42M | 12:19:26 | 1 | 1976 |
------------------------------------------------------------+-----------------------------------+---------------+
Whereas the plan on the other schema is:
------------------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
------------------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 268M | | | |
| 1 | HASH JOIN RIGHT SEMI | | 3174K | 2889M | 268M | 78:02:36 | | |
| 2 | TABLE ACCESS STORAGE FULL | GTT_T2 | 1 | 119 | 2 | 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 8980M | 7129G | 267M | 78:54:28 | 1 | 1561 |
| 4 | PARTITION HASH ALL | | 8980M | 7129G | 267M | 78:54:28 | 1 | LAST |
| 5 | TABLE ACCESS STORAGE FULL | T1 | 8980M | 7129G | 267M | 78:54:28 | 1 | 7924 |
------------------------------------------------------------+-----------------------------------+---------------+
As you can see bloom filter based partition pruning appears in one plan, but it doesn't appear in the other plan.
I couldn't make out anything from 10053 trace. To me it is not clear in the trace where CBO decides to use bloom filter.
Hence two questions:
-- How does CBO decide to use bloom filter based partition pruning?
-- How to force bloom filter based partition pruning?
Many thanks.