Jonathan,
Thank you for the input. Learning a lot about DW data model configurations
and enjoying it (odd as that sounds).
I know some of my questions come from a lot of ignorance of using DIM &
FACT tables and star transformations and appreciate your thoughtful
response.
Chris
On Tue, Aug 3, 2021 at 12:53 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
table.But then we have an additional FILTER being applied to the DIMENSION
execution time (6 secs out of 24) and I'm concerned this will get worse asCurrently this doesn't kill performance, but definitely adds about 25%
data volumes grow.
the FACT table and replicate that column (or some version of it) to theSo the question is , should CASE statement filter really get applied to
FACT table instead? (Generally as a rule of thumb I mean)
The fragment you've shown looks perfectly normal for a bitmap star
transformation with joinback to dimension when reporting any dimension
columns that aren't key columns.
(I'm assuming the filter predicate ends with " = 'something}' ". That's
just the generic way that Oracle deals with the star transformation when a
joinback is needed.
Adding "FIELD1" to the index supporting the PK is the sensible option if
you want to improve performance - just as carrying columns with any primary
key index is for any type of query.
It shouldn't be "unusual" to have non-key predicates against dimension
tables, and I'd expect most queries to be doing that type of thing anyway.
The only thing that would make your plan odd (for a bitmap star
transformation) would be if that predicate with the case experession hadn't
also been used in the first pass against the dimension table that genrated
the primary keys used to select the bitmaps from the fact table indexes.
Regards
Jonathan Lewis
On Wed, 28 Jul 2021 at 14:41, Chris Taylor <
christopherdtaylor1994@xxxxxxxxx> 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
