hello,
i am modeling a parent-child dimensional table (DIM_CONTR_SBI) that is joined to the fact table using a parent-child relationship table (DIM_CONTR_SBI_HIER) as a bridge, according to documentation:
the fact foreign key is joined to the MEMBER_KEY and the ANCESTOR_KEY is joined to the dimension primary key.
The logical dimension table source (unique for the logical table), is modeled as follows:
This is done following documentation: https://docs.oracle.com/en/middleware/bi/analytics-server/metadata-oas/create-and-manage-dimensions-parent-child-hierarchies.html#GUID-C3880597-7977-419B-99ED-AEABE6E9BE69
Now, the fact table has a partition on the column joined to the parent-child relationship table. When applying a filter to the dimension, the partition is not used, I suspect because the filter value is set on the parent-child table (DIM_CONTR_SBI) that is not directly joined to the fact table. Here's a sample query that is generated by OAC:
WITH
SAWITH0 AS (select sum(T332079.VALUE) as c1
from
DIM_CONTR_SBI T332071,
DIM_CONTR_SBI_HIER T333655,
FACT_SBI T332079
where ( T332071.ALIAS_ALIAS2 = 'ANAS' and T332071.CODE = T333655.ANCESTOR_KEY and T332079.CONTR = T333655.MEMBER_KEY ) )
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
D1.c1 as c2
from
SAWITH0 D1 ) D1 where rownum <= 500001
And this is the explain plan for this query:
Plan hash value: 2084202946
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 15357 (4)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 1 | 15 | 15357 (4)| 00:00:01 | | |
| 3 | VIEW | | 1 | 13 | 15356 (4)| 00:00:01 | | |
| 4 | SORT AGGREGATE | | 1 | 54 | | | | |
|* 5 | HASH JOIN | | 604K| 31M| 15356 (4)| 00:00:01 | | |
| 6 | JOIN FILTER CREATE | :BF0000 | 9 | 369 | 13 (0)| 00:00:01 | | |
|* 7 | HASH JOIN | | 9 | 369 | 13 (0)| 00:00:01 | | |
| 8 | JOIN FILTER CREATE | :BF0001 | 2 | 52 | 3 (0)| 00:00:01 | | |
| 9 | PARTITION LIST SINGLE | | 2 | 52 | 3 (0)| 00:00:01 | 1 | 1 |
|* 10 | TABLE ACCESS STORAGE FULL| DIM_CONTR_SBI | 2 | 52 | 3 (0)| 00:00:01 | 1 | 1 |
| 11 | JOIN FILTER USE | :BF0001 | 1256 | 18840 | 10 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS STORAGE FULL | DIM_CONTR_SBI_HIER | 1256 | 18840 | 10 (0)| 00:00:01 | | |
| 13 | JOIN FILTER USE | :BF0000 | 15M| 195M| 15278 (3)| 00:00:01 | | |
| 14 | PARTITION LIST ALL | | 15M| 195M| 15278 (3)| 00:00:01 | 1 | 1031 |
|* 15 | TABLE ACCESS STORAGE FULL | FACT_SBI | 15M| 195M| 15278 (3)| 00:00:01 | 1 | 1031 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=500001)
5 - access("T332079"."CONTR"="T333655"."MEMBER_KEY")
7 - access("T332071"."CODE"="T333655"."ANCESTOR_KEY")
10 - storage("T332071"."ALIAS_ALIAS2"='ANAS')
filter("T332071"."ALIAS_ALIAS2"='ANAS')
12 - storage(SYS_OP_BLOOM_FILTER(:BF0001,"T333655"."ANCESTOR_KEY"))
filter(SYS_OP_BLOOM_FILTER(:BF0001,"T333655"."ANCESTOR_KEY"))
15 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T332079"."CONTR"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T332079"."CONTR"))
Does anyone have a workaound for this, that makes sure the partitioning is used in the fact table? Does anyone know of different modeling techniques I can apply?