This discussion is archived
0 Replies Latest reply: Sep 2, 2010 3:16 AM by 675455 RSS

QUERY REWRITE ISSUE

675455 Explorer
Currently Being Moderated
Hi Gurus,
I have an issue using the Materialized Views for query rewrite.
I will try to explain it below as clear as I can, hoping to not miss any important information/detail.

In our DWH we have built some MVs which are aggregation on fact tables (with SUM and COUNT).
In the MVs we have included columns which have foreign keys to some of the most popular dimensions, the foreing keys are disabled but rely,
so the database parameter QUERY_REWRITE_INTEGRITY has been set to TRUSTED to allow the optimizer to use the constraints to rewrite the query.
On one of the most important dimension there is a FGAC rule applied on, which adds a predicate to the query.
This predicate is something like 1=1 or 1=2 depending on the user issuing the query.

As I will show below, if I write a query joining the fact table and the dimension, the query rewrite will work and the query will be rewritten using the MV,
but if in the query I use a view on the dimension table rather than the dimension table itself the query rewrite won't be working.
Note that the view on the dimension table selects all the column from the table without any particular filter.
When I disable the policy on the table the query rewrite works for both the queries (no matter if I use the dimension table or the views on it).

Here you can see what happes when I run a query joining fact table and dimension table (DWSS.DIM_ACCOUNT)
SQL> SELECT
  2    DWSS.DIM_ACCOUNT.USERNAME,
  3    SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP)
  4  FROM
  5    DWSS.DIM_ACCOUNT,
  6    BI_REP.V_FACT_POKER_TOURNAMENT_PLAY
  7  WHERE BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.ACCOUNT_DIM_ID=DWSS.DIM_ACCOUNT.ACCOUNT_DIM_ID
  8  GROUP BY
  9    DWSS.DIM_ACCOUNT.USERNAME
 10  ;

USERNAME                                           SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP)
-------------------------------------------------- -------------------------------------------------
U2256741                                                                                       2.578

SQL> SELECT *
  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  cqpk23y7q66py, child number 0
-------------------------------------
SELECT   DWSS.DIM_ACCOUNT.USERNAME,   SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP) FROM   DWSS.DIM_ACCOUNT,
BI_REP.V_FACT_POKER_TOURNAMENT_PLAY WHERE BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.ACCOUNT_DIM_ID=DWSS.DIM_ACCOUNT.ACCOUNT_D
IM_ID GROUP BY   DWSS.DIM_ACCOUNT.USERNAME

Plan hash value: 3643351249

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                            |       |       |     5 (100)|          |       |       |
|   1 |  HASH GROUP BY                   |                            |     1 |    41 |     5  (20)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID    | DIM_ACCOUNT                |     1 |    15 |     2   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS                  |                            |     1 |    41 |     4   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ALL          |                            |     1 |    26 |     2   (0)| 00:00:01 |     1 |   121 |
|   5 |      MAT_VIEW REWRITE ACCESS FULL| FACT_POKER_TOUR_PLAY_DD_MV |     1 |    26 |     2   (0)| 00:00:01 |     1 |   121 |
|*  6 |     INDEX RANGE SCAN             | PK_DIM_ACCOUNT_IDX         |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("FACT_POKER_TOUR_PLAY_DD_MV"."ACCOUNT_DIM_ID"="ACCOUNT_DIM_ID")


25 rows selected.

SQL> SELECT sql_id, object_owner, object_name, predicate
  2  FROM v$vpd_policy
  3  WHERE sql_id = 'cqpk23y7q66py'
  4    AND policy = 'RLS_DIM_ACCOUNT_PERSONAL_COLS'
  5  ;

SQL_ID        OBJECT_OWNER                   OBJECT_NAME                    PREDICATE
------------- ------------------------------ ------------------------------ ----------------------------------------------------------------------
cqpk23y7q66py DWSS                           DIM_ACCOUNT                    'PERSONAL DATA'= sys_context('RLS_CONTEXT', 'VIEW_PERSONAL_DATA')
and here you can see what happens when I run a query joining the fact table and the view on the dimension table (BI_REP.V_DIM_ACCOUNT)
SQL> SELECT
  2    BI_REP.V_DIM_ACCOUNT.USERNAME,
  3    SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP)
  4  FROM
  5    BI_REP.V_DIM_ACCOUNT,
  6    BI_REP.V_FACT_POKER_TOURNAMENT_PLAY
  7  WHERE BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.ACCOUNT_DIM_ID=BI_REP.V_DIM_ACCOUNT.ACCOUNT_DIM_ID
  8  GROUP BY
  9    BI_REP.V_DIM_ACCOUNT.USERNAME
 10  ;

USERNAME                                           SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP)
-------------------------------------------------- -------------------------------------------------
U2256741                                                                                       2.578

SQL> SELECT *
  2  FROM table(DBMS_XPLAN.DISPLAY_CURSOR);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2hc3qp505rk14, child number 0
-------------------------------------
SELECT   BI_REP.V_DIM_ACCOUNT.USERNAME,   SUM(BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.RAKE_GBP) FROM
BI_REP.V_DIM_ACCOUNT,   BI_REP.V_FACT_POKER_TOURNAMENT_PLAY WHERE
BI_REP.V_FACT_POKER_TOURNAMENT_PLAY.ACCOUNT_DIM_ID=BI_REP.V_DIM_ACCOUNT.ACCOUNT_DIM_ID GROUP BY
BI_REP.V_DIM_ACCOUNT.USERNAME

Plan hash value: 464580478

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |       |       |     5 (100)|          |       |       |
|   1 |  HASH GROUP BY               |                            |     1 |    41 |     5  (20)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DIM_ACCOUNT                |     1 |    15 |     2   (0)| 00:00:01 |       |       |
|   3 |    NESTED LOOPS              |                            |     1 |    41 |     4   (0)| 00:00:01 |       |       |
|   4 |     PARTITION RANGE ALL      |                            |     1 |    26 |     2   (0)| 00:00:01 |     1 |   125 |
|   5 |      TABLE ACCESS FULL       | FACT_POKER_TOURNAMENT_PLAY |     1 |    26 |     2   (0)| 00:00:01 |     1 |   125 |
|*  6 |     INDEX RANGE SCAN         | PK_DIM_ACCOUNT_IDX         |     1 |       |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("ACCOUNT_DIM_ID"="ACCOUNT_DIM_ID")

Note
-----
   - dynamic sampling used for this statement


30 rows selected.

SQL> SELECT sql_id, object_owner, object_name, predicate
  2  FROM v$vpd_policy
  3  WHERE sql_id = '2hc3qp505rk14'
  4    AND policy = 'RLS_DIM_ACCOUNT_PERSONAL_COLS'
  5  ;

SQL_ID        OBJECT_OWNER                   OBJECT_NAME                    PREDICATE
------------- ------------------------------ ------------------------------ -------------------------------------------------------------------
2hc3qp505rk14 DWSS                           DIM_ACCOUNT                    'PERSONAL DATA'= sys_context('RLS_CONTEXT', 'VIEW_PERSONAL_DATA')
I don't report the examples after disabling the policy, but trust me, the query rewrite will be working fine on both the queries.

I have done researching on the WEB and in the metalink, but couldn't find anything.
Can anyone give some advice or address me to some documentation which can help?
Hope someone experienced the same issue.

Many Thanks,
Davide

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points