A customer having a DWH has a View with a huge number of Left Outer Joins to a dim table. The Reports using this view will only using a small number of columns of some dim tables. But the the optimizer will read always all DIM-Tables. Therefore the runtime of the query even if you read a column from the fact table is too high.
The idea was to increase the performance of the reports by reducing the number of joins – Join Elimination (JE). based on the blogs of JL
and some other blogs I found together with Dani Schniders Blogs
we set up a testcase on the requirements:
- You must have a FK on every Child table in order to let the optimizer do his work for JE (Join Elimination)
- To reduce the impact while writing to the Child tables we define the FK as RELY DISABLE NOVALIDATE
- To be able to define the FK with the option RELY you need to define every PK as RELY too
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | CREATE TABLE dim_1( dim_id NUMBER (*,0), department_id NUMBER (*,0), CONSTRAINT pk_dim_1 PRIMARY KEY (dim_id) RELY ); CREATE TABLE dim_2( dim_id NUMBER (*,0), department_id NUMBER (*,0), CONSTRAINT pk_dim_2 PRIMARY KEY (dim_id) RELY ); CREATE TABLE fact( fact_id NUMBER (*,0), type_id NUMBER (*,0) NOT NULL, CONSTRAINT pk_fact PRIMARY KEY (fact_id) RELY ); CREATE OR REPLACE VIEW V_fact AS SELECT p.fact_id, p.type_id , p.type_id AS p_dim1_id, p.type_id AS p_dim2_id FROM fact p WHERE (MOD(p.fact_id,2) = 0) ; CREATE OR REPLACE VIEW V_V_FACT_DIM AS SELECT /*+ opt_param('_optimizer_join_elimination_enabled','TRUE') */ /*+ opt_param('query_rewrite_integrity','TRUSTED') */ p.fact_id, p.type_id , p.p_dim1_id, p.p_dim2_id, e1.dim_id AS e1_dim_id, e1.department_id AS e1_dep, e2.dim_id AS e2_dim_id, e2.department_id AS e2_dep FROM v_fact p LEFT OUTER JOIN dim_1 e1 ON p.p_dim1_id = e1.dim_id LEFT OUTER JOIN dim_2 e2 ON p.p_dim2_id = e2.dim_id ; ALTER TABLE fact ADD CONSTRAINT fk_fact_dim_1 FOREIGN KEY (type_id) REFERENCES dim_1(dim_id) RELY DISABLE NOVALIDATE; ALTER TABLE fact ADD CONSTRAINT fk_fact_dim_2 FOREIGN KEY (type_id) REFERENCES dim_2(dim_id) RELY DISABLE NOVALIDATE; |
Now, you can test your sql queries …
EXPLAIN PLAN FOR SELECT fact_id, p_dim1_id,p_dim1_id FROM V_V_FACT_DIM; SELECT * FROM TABLE(dbms_xplan.display); -------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| FACT | 1 | 26 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- |
And now, JE will work – when you do not specify any column of any child.
If you specify one or more columns of e.g. the first dim , the second dim table will not be read.
Remember:
In Oracle 12.1 you can define only one columne in the FK as well in the PK that JE will work. With Oracle 12.2 this restriction is removed.
Have fun
Rainer