Using Join Elimination to increase performance on Views

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:

  1. You must have a FK on every Child table in order to let the optimizer do his work for JE (Join Elimination)
  2. To reduce the impact while writing to the Child tables we define the FK as RELY DISABLE NOVALIDATE
  3. 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

Kommentar verfassen

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.