Tim,
Thank you for the detailed response.
There are a lot of dimensions involved in the query, this one jumps out
though because this dimension is very large and the secondary pass on
the "*TABLE
ACCESS BY LOCAL INDEX ROWID" *with a filter condition is a bit annoying.
So two MORE questions if you don't mind if I pick your brain a bit.
1.) *So if we were filtering on a lot of conditions on all the dimensions,
it would be normal to see the filter condition applied after the TABLE
ACCESS BY LOCAL INDEX ROWID on all the dimensions where we filter for some
condition then?* (That is, is it normal/expected to get a filter condition
against the table access for the rows we are retrieving? That's annoying
because effectively a second pass/sanitation of the data but it sounds like
it's expected.) Some of these dimensions are really, really large.
2.) *Is it NORMAL (or a no-no) to join dimensions to dimensions as part of
a data warehouse query?* We've got the following two situations:
a.) A join between 2 dimension tables on an ID field and also joined to
the FACT table on the same ID field. I think the join between the DIMs is
redundant here and causes an extra condition.
b.) We have a situation where partition pruning isn't happening because a
separate dimension table has the partition key but isn't being brought into
the joins. So to get to the partition key of the FACT table, we have to
join 2 other DIMENSIONS together (Dim to Dim Join) and then join that
result back to the FACT table to have the appropriate partition_key for the
FACT table. I've tested doing that and the performance didn't really
improve much. What's crazy is, if I do an IN statement instead and do a
sub query against the other DIMENSION to pull out the partitions I want
(SITE_IDs) , the query returns in 4 seconds (vs over a minute).
For example of item 2.b): (beware SQL text ahead!) - two examples
*Example 1 of a DIM to DIM join to get the SITE partition key on the FACT
table:*
**performance bad*
WITH
SAWITH0
AS
(SELECT SUM (T1586.UOM_QNTY_CNT) AS c1,
COUNT (DISTINCT T1526.CE_INTRNL_DRUG_CD) AS c2
FROM DM_CHARGE_DIM T1526 /* Dim_CHARGE
*/
,
DM_DATE_DAY_DIM T1568 /* Dim_DISP_SRV_DT
*/
,
DM_COVERED_ENTITY_DIM T1032 /* Dim_COVERED_ENTITY
*/
,
DM_DISPENSING_FACT T1586 * /*
Fact_DISPENSING partitioned by SITE_KEY_NB */,*
, DM_SITE_DIM T1 */* Added Table with SITE_KEY_NB
partition */*
WHERE ( T1032.CE_KEY_NB = T1586.CE_KEY_NB
AND T1526.CHG_PRICE_KEY_NB = T1586.CHG_PRICE_KEY_NB
AND T1568.DT_KEY_NB = T1586.SRV_DT_KEY_NB
AND CASE WHEN T1526.HID_MAP_FLG = 1 THEN 'Yes' ELSE 'No'
END =
'No'
AND CASE WHEN T1526.NON_RX_FLG = 1 THEN 'Yes' ELSE 'No'
END =
'No'
AND CONCAT (
CONCAT (TRIM (BOTH ' ' FROM T1568.MO_DESC), ' '),
CAST (T1568.YR_NB AS CHARACTER (4))) =
'December 2020'
AND (T1032.CE_GRP_ID IN (29.0,
30.0,
31.0,
32.0,
741.0,
4223.0,
7091.0,
10552.0))
AND (T1526.CE_GRP_ID IN (29.0,
30.0,
31.0,
32.0,
741.0,
4223.0,
7091.0,
10552.0)))
*AND T1586.SITE_KEY_NB = T1.SITE_KEY_NB /* Added Join to get
SITE_KEY_NB */*
*AND T1032.CE_ID = T1.CE_ID /* Added Join on CE_IDs to find
appropriate SITE_KEY_NBs from DM_SITE_DIM , DIM-DIM join */*
)
SELECT D1.c1 AS c1, D1.c2 AS c2, D1.c3 AS c3
FROM (SELECT DISTINCT 0 AS c1, D1.c2 AS c2, D1.c1 AS c3
FROM SAWITH0 D1) D1
WHERE ROWNUM <= 125001
*Example 2 of adding an IN statement for the FACT table instead:*
**performance great!*
WITH
SAWITH0
AS
(SELECT SUM (T1586.UOM_QNTY_CNT) AS c1,
COUNT (DISTINCT T1526.CE_INTRNL_DRUG_CD) AS c2
FROM DM_CHARGE_DIM T1526 /* Dim_CHARGE
*/
,
DM_DATE_DAY_DIM T1568 /* Dim_DISP_SRV_DT
*/
,
DM_COVERED_ENTITY_DIM T1032 /* Dim_COVERED_ENTITY
*/
,
DM_DISPENSING_FACT T1586 * /*
Fact_DISPENSING partitioned by SITE_KEY_NB */,*
WHERE ( T1032.CE_KEY_NB = T1586.CE_KEY_NB
AND T1526.CHG_PRICE_KEY_NB = T1586.CHG_PRICE_KEY_NB
AND T1568.DT_KEY_NB = T1586.SRV_DT_KEY_NB
AND CASE WHEN T1526.HID_MAP_FLG = 1 THEN 'Yes' ELSE 'No'
END =
'No'
AND CASE WHEN T1526.NON_RX_FLG = 1 THEN 'Yes' ELSE 'No'
END =
'No'
AND CONCAT (
CONCAT (TRIM (BOTH ' ' FROM T1568.MO_DESC), ' '),
CAST (T1568.YR_NB AS CHARACTER (4))) =
'December 2020'
AND (T1032.CE_GRP_ID IN (29.0,
30.0,
31.0,
32.0,
741.0,
4223.0,
7091.0,
10552.0))
AND (T1526.CE_GRP_ID IN (29.0,
30.0,
31.0,
32.0,
741.0,
4223.0,
7091.0,
10552.0)))
* AND ** T1586.SITE_KEY_NB IN (SELECT T1.SITE_KEY_NB /* Find the Site Key
Numbers that belong to our CE_GRP_IDs */*
* from DIM_SITE_DIM T1*
* where T1.CE_ID IN*
* (*
* SELECT CE_ID*
* FROM DM_COVERED_ENTITY_DIM T2 /*
This table is our main join above as well */*
* WHERE T2.CE_GRP_ID IN (29.0,*
* 30.0,
31.0, 32.0,
741.0,
4223.0, 7091.0,
10552.0*
*
)*
* )*
* )*
)
SELECT D1.c1 AS c1, D1.c2 AS c2, D1.c3 AS c3
FROM (SELECT DISTINCT 0 AS c1, D1.c2 AS c2, D1.c1 AS c3
FROM SAWITH0 D1) D1
WHERE ROWNUM <= 125001
I also tried creating a mapping View between DM_SITE_DIM and
DM_COVERED_ENTITY_DIM that has SITE_KEY_NB, CE_ID, CE_GRP_ID and add that
to the join and performance is also equally bad.
Also we have the PKs and enabled FKs (novalidate) between the DIMs and FACT.
Finally, I will look CREATE HIERARCHY stuff you mentioned along with the
BITMAP join indexes. Those concern me a bit because we do a lot of
parallel DDL when running the ETL process and there seems to be a lot of
Oracle caveats/warnings with using BITMAP join indexes.
Chris
On Thu, Jul 29, 2021 at 6:43 PM Tim Gorman <tim.evdbt@xxxxxxxxx> wrote:
Chris,
It is just the two tables (i.e. FACT and DIM) involved in the query?
There are no additional dimension tables?
If so, that is likely much of the problem, because the concept of a
dimensional data model (a.k.a. star schema) as well as Oracle's "star
transformation" mechanism is built around the idea of two or more
dimensions being involved in any query on the fact table. As many
dimensions as possible, in fact. The more, the merrier.
The basic idea is this...
1. user specifies search criteria on columns on the dimensions
- if no search predicates are specified for a dimension table, then
that dimension is not included in steps 2-4 below.
- instead it will be used in step 5 as a post-processing filter
or join step
2. database uses search criteria to filter down to a result set
from each dimension
3. database uses all the result sets from all the dimensions to create
a consolidated set (a.k.a. star transformation)
4. database uses all of the dimension key values to join to the bitmap
keys in the fact table (a.k.a. star join)
5. database uses additional filter predicates or join predicates
against the fact table to produce final results
The entire reason for having bitmap indexes on all of the foreign keys of
the fact table is for that big operation on step 4 to work. That operation
in step 4 works well if the consolidated set created in step 3 is small,
and that's only going to happen if filtering happens on the dimensions.
The filtering in step 5 is merely an after-effect, ideally most of the
work of joining all the tables was already performed in step 4, and step 5
is running against a really small final result set.
The upshot is the more well-filtered dimensions that are involved in the
star transformation, the more efficient is the star join. Most (if not
all) filtering predicates in star transformations should be found on
columns in the dimensions. Ideally there are no filter predicates against
the fact table, but if there are, then we have to consider whether they
should be converted into dimensions or left as "conformed dimensions".
Are star transformations important? Yes, if you are attempting to query a
dimensional data model.
There is more about star transformations HERE
<https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/query-transformations.html#GUID-76B6B58B-24B0-4DFE-AC1B-CFAC4D93C55A>.
Also, the STAR_TRANSFORMATION_ENABLED
<https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/STAR_TRANSFORMATION_ENABLED.html#GUID-B2E6145D-164A-4453-9839-0F6E6442A922>
parameter should not be FALSE, and there are additional guidelines to post
for the Oracle optimizer to initiate star transformations more easily, such
as hierarchies specified with the CREATE HIERARCHY
<https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-HIERARCHY.html#GUID-73925877-992B-4624-AA28-8F565E9C3F0D>
command, enabled PK constraints on the dimension tables, and ENABLED or
RELY enforced foreign-key constraints on the fact table.
Finally, one fun feature for star transformations is when Oracle database
materializes the consolidated set mentioned in step 3 above, which is
called a bitmap-join index. Unfortunately, there are no specific
documentation pages in the Oracle doc-set devoted to bitmap-join indexes,
but there is a good discussion of DW optimization HERE
<https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/data-warehouse-optimizations-techniques.html#GUID-79C29A60-3477-448D-835D-2940D060D050>as
well Tim Hall's more detailed blog post HERE
<https://oracle-base.com/articles/9i/bitmap-join-indexes>. If it is
possible to create a bitmap-join index on a fact table, then it is almost
certain that any query using those dimensional search predicates will
achieve a star transformation.
Hope this helps,
-Tim
On 7/28/2021 6:41 AM, Chris Taylor wrote:
This is a sanity check question as I'm helping out with a data model
design and indexing strategy with our ETL team. None of us are what I'd
call real strong on data warehouse design.
Here's the setup and the question:
Setup:
We have a DIMENSION table partitioned by SITE (Client) and we have a FACT
table partitioned by SITE (Client).
The PK on the DIMENSION is a combo of SITE+KEY_FIELD
The join between the DIMENSION and the FACT is on SITE+KEY_FIELD
All that is fine and works great with bitmap indexes on the FACT etc.
*HOWEVER* here is the problem:
In one particular query, we're applying an *additional FILTER *condition
to the DIMENSION table (instead of the FACT) after the join which causes a
bit of performance penalty as the rows are found between the DIMENSION and
FACT but then the filter is then applied to the rows from the DIMENSION
table (which is very large table also).
My theory is that this filter clause (the columns used by the filter)
should actually also be in the FACT and apply the filter there with a
BITMAP index instead of applying it to the DIMENSION. RIght now the
condition (columns) in the DIMENSION that are checked for a value do not
exist in the FACT table..
The join & condition is something like this:
*| 35 | PARTITION LIST ITERATOR |
| 0 | 1 | | 1 (0)| 00:00:01
| KEY | KEY | Q1,01 | PCWP |* 36 | INDEX UNIQUE SCAN
| DIMENSION_TABLE_NDX2 | 0 | 1 |
| 1 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP |* 37 |
TABLE ACCESS BY LOCAL INDEX ROWID | DIMENSION_TABLE 36 -
access("FACT_TABLE"."SITE_ID"="DIMENSION_TABLE"."SITE_ID" AND
"FACT_TABLE"."KEY_FIELD"="DIMENSION_TABLE"."KEY_FIELD") 37 - filter((CASE
"FIELD1" WHEN 'I' THEN 'Inpatient' WHEN 'O' THEN 'Outpatient' END))*
So we have the join between the FACT and DIMENSION on SITE_ID & KEY_FIELD
But then we have an additional FILTER being applied to the DIMENSION table.
Currently this doesn't kill performance, but definitely adds about 25%
execution time (6 secs out of 24) and I'm concerned this will get worse as
data volumes grow.
So the question is , should CASE statement filter really get applied to
the FACT table and replicate that column (or some version of it) to the
FACT table instead? (Generally as a rule of thumb I mean)
We can add the CASE statement to the PK index on the DIMENSION_TABLE as
well, and thats what the ETL team is suggesting, but that seems like the
wrong way to go.
Chris
