Home » RDBMS Server » Performance Tuning » Query Rewrite Failed After RLS policy applied(QSM-01284)
Query Rewrite Failed After RLS policy applied(QSM-01284) [message #398634] |
Fri, 17 April 2009 00:51 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ramabhupal
Messages: 10 Registered: April 2009 Location: hyd
|
Junior Member |
|
|
Hi All,
I am working on Query Rewrite option . If i use the predicate in the query it is rewriting with materialized view. If i use the same predicate as RLS policy it is getting failed . I am getting the following error QSM-01284: materialized view EMPMV has an anchor table EMP not found in query.
Please provide your ideas how to solved this.
This is my workaround on this. and my database 10.2.0.3.
SQL> set long 1000
SQL> select query from dba_mviews where mview_name='EMPMV';
QUERY
--------------------------------------------------------------------------------
select a.deptno,a.job,sum(a.sal) sal from
machrabh.emp a,machrabh.dept b ,machrabh.job c where a.deptno=b.deptno and a.job
=c.job group by a.deptno,a.job
SQL> set autotrace on explain;
SQL> select * from machrabh.empmv;
DEPTNO JOB SAL
---------- --------- ----------
30 CLERK 950
40 ANALYST 2000
50 MANAGER 2850
50 CLERK 1300
20 ANALYST 6000
30 SALESMAN 5600
20 CLERK 21900
10 MANAGER 2450
10 PRESIDENT 5000
20 MANAGER 2975
10 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=10 Bytes=15
0)
1 0 MAT_VIEW ACCESS (FULL) OF 'EMPMV' (MAT_VIEW) (Cost=3 Card=
10 Bytes=150)
SQL> select a.deptno,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by a.deptno;
DEPTNO SUM(SAL)
---------- ----------
30 6550
50 4150
40 2000
20 30875
10 7450
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=5 Bytes=35)
1 0 HASH (GROUP BY) (Cost=4 Card=5 Bytes=35)
2 1 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMPMV' (MAT_VIEW REWR
ITE) (Cost=3 Card=10 Bytes=70)
Here i am using the predicate as deptno in (10,20)
SQL> select a.deptno,sum(sal) from (select * from machrabh.emp where deptno in (10,20)) a, machrabh.dept b where a.deptno=b.deptno group by a.deptno;
DEPTNO SUM(SAL)
---------- ----------
20 30875
10 7450
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=14)
1 0 HASH (GROUP BY) (Cost=4 Card=2 Bytes=14)
2 1 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMPMV' (MAT_VIEW REWR
ITE) (Cost=3 Card=4 Bytes=28)
Here created policy function
SQL> select text from user_source where name='F_RLS_DEPTNO' ORDER BY LINE;
TEXT
----------------------------------------------------------------------------------------------------------------------------------
FUNCTION F_RLS_DEPTNO(
p_schema IN VARCHAR2,
p_object IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
RETURN ' DEPTNO IN (10,20) ';
END;
9 rows selected.
Here i am adding RLS Policy
SQL> EXEC DBMS_RLS.ADD_POLICY('MACHRABH','EMP',POLICY_FUNCTION=>'F_RLS_DEPTNO',POLICY_NAME=>'DEPTNO_TEST');
PL/SQL procedure successfully completed.
SQL> select a.deptno,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by a.deptno;
DEPTNO SUM(SAL)
---------- ----------
20 30875
10 7450
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=14)
1 0 HASH (GROUP BY) (Cost=4 Card=2 Bytes=14)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=9 Byte
s=63)
SQL> DELETE FROM REWRITE_TABLE;
4 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=4)
1 0 DELETE OF 'REWRITE_TABLE'
2 1 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 C
ard=4)
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_MVIEW.EXPLAIN_REWRITE('select a.deptno,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by a.deptno','MACHRABH.EMPMV','test');
PL/SQL procedure successfully completed.
SQL> select message from rewrite_table;
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
QSM-01284: materialized view EMPMV has an anchor table EMP not found in query
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=4 Bytes=404
)
1 0 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 Car
d=4 Bytes=404)
Thanks,
Ram
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #398876 is a reply to message #398634] |
Sat, 18 April 2009 23:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
That's an interesting one. You have an RLS policy on the base table, and you are expecting Oracle to apply the RLS policy and THEN rewrite the transformed query.
Well, it seems reasonable, but I wonder what the query looks like AFTER the RLS policy is applied. If it looks like your manual example - an inline view - then I very much doubt that it will work because the query no longer resembles the materialized view (the MV does not contain an inline view).
Run a trace and TKPROF to see the final executed query. Perhaps you can restructure your MV to be more similar to the transformed query - this might help with the rewrite.
I wonder if Rewrite Equivalence would help?
Ross Leishman
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #398944 is a reply to message #398876] |
Sun, 19 April 2009 23:31 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ramabhupal
Messages: 10 Registered: April 2009 Location: hyd
|
Junior Member |
|
|
Hi Ross,
Thanks for your quick response .
Exactly what you are expecting me the same.
But, I tried using tkprof, it is showing query without rewrite. I am suspecting this is because of join conditions in the materialized view query.
But if i recreate the materialized view as
create materialized view machrabh.empmv
build immediate
enable query rewrite as
select deptno,job,sum(sal),grouping_id(deptno,job)
from machrabh.emp /*** Here i have removed the joins between dept,job ***/group by cube (deptno,job);
and ran RLS policy on machrabh.emp then the following query is rewriting with materialized view.
select a.deptno,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by a.deptno;
But to make the joins correct, I have created primary keys on dept(deptno) , job(job) and foreign key on emp.
Thanks,
Ram.
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #399380 is a reply to message #398944] |
Tue, 21 April 2009 22:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I understand that TKPROF does not show the MV query rewrite, but that is because the query is not using the MV - as explained in EXPLAIN_REWRITE()
But surely it shows the syntax of the query on the base table INCLUDING the RLS predicate.... Doesn't it?
What is the syntax of the transformed query once the RLS policy has been applied?
Ross Leishman
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #399406 is a reply to message #399380] |
Wed, 22 April 2009 02:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ramabhupal
Messages: 10 Registered: April 2009 Location: hyd
|
Junior Member |
|
|
Hi Ross,
if i create materialized view only on single fact table it is working and also you can see the rewritten text in Bold.
But the problem is after creating materialized view with joins of dimension tables.
Please look at the below workaround
SQL> drop materialized view machrabh.empmv;
Materialized view dropped.
SQL> create materialized view machrabh.empmv build immediate enable query rewrite as
2 select deptno,job,sum(sal) sal,grouping_id(deptno,job) from emp group by deptno,job;
Materialized view created.
SQL> select text from user_source where name='F_RLS_DEPTNO';
TEXT
----------------------------------------------------------------------------------------------------------------------------------
FUNCTION F_RLS_DEPTNO(
p_schema IN VARCHAR2,
p_object IN VARCHAR2
)
RETURN VARCHAR2
AS
BEGIN
RETURN ' DEPTNO IN (10,20) ';
END;
9 rows selected.
SQL> exec dbms_rls.add_policy('MACHRABH','EMP',POLICY_FUNCTION=>'F_RLS_DEPTNO',POLICY_NAME=>'EMP_TEST');
PL/SQL procedure successfully completed.
SQL> select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname;
DNAME SUM(SAL)
-------------- ----------
RESEARCH 3015.5
ACCOUNTING 745
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=76)
1 0 HASH (GROUP BY) (Cost=7 Card=2 Bytes=76)
2 1 HASH JOIN (Cost=6 Card=3 Bytes=114)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cos
t=2 Card=2 Bytes=24)
5 4 INDEX (RANGE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (
Cost=1 Card=2)
6 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMPMV' (MAT_VIEW RE
WRITE) (Cost=3 Card=5 Bytes=130)
SQL> delete from rewrite_table;
3 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3)
1 0 DELETE OF 'REWRITE_TABLE'
2 1 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 C
ard=3)
SQL> exec dbms_mview.explain_rewrite('select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname','MACHRABH.EMPMV','test');
PL/SQL procedure successfully completed.
SQL> select message , query,rewritten_txt from rewrite_table;
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
QSM-01151: query was rewritten
select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname
QSM-01033: query rewritten with materialized view, EMPMV
SELECT B.DNAME DNAME,SUM(EMPMV.SAL) SUM(SAL) FROM MACHRABH.EMPMV EMPMV,MACHRABH.DEPT B WHERE EMPMV.DEPTNO=B.DEPTNO AND (EMPMV.DEPT
NO=10 OR EMPMV.DEPTNO=20) GROUP BY B.DNAME
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
SELECT B.DNAME DNAME,SUM(EMPMV.SAL) SUM(SAL) FROM MACHRABH.EMPMV EMPMV,MACHRABH.DEPT B WHERE EMPMV.DEPTNO=B.DEPTNO AND (EMPMV.DEPT
NO=10 OR EMPMV.DEPTNO=20) GROUP BY B.DNAME
QSM-01082: Joining materialized view, EMPMV, with table, EMP, not possible
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
O=10 OR DEPTNO=20
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, COMM
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, SAL
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, HIREDATE
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, MGR
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, ENAME
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
O=10 OR DEPTNO=20
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
QSM-01102: materialized view, EMPMV, requires join back to table, EMP, on column, EMPNO
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
SELECT EMPNO EMPNO,ENAME ENAME,JOB JOB,MGR MGR,HIREDATE HIREDATE,SAL SAL,COMM COMM,DEPTNO DEPTNO FROM MACHRABH.EMP EMP WHERE DEPTN
O=10 OR DEPTNO=20
9 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=834
)
1 0 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 Car
d=3 Bytes=834)
Recreating the Materialized view with joins of dimension tables. on that RLS is getting failed
SQL> drop materialized view machrabh.empmv;
Materialized view dropped.
SQL> exec dbms_rls.drop_policy('MACHRABH','EMP','EMP_TEST');
PL/SQL procedure successfully completed.
SQL> create materialized view machrabh.empmv build immediate enable query rewrite as
2 select a.deptno,a.job ,sum(sal),grouping_id(a.deptno,a.job) from machrabh.emp a, machrabh.dept b,machrabh.job c
3 where a.deptno=b.deptno and a.job=c.job
4 group by a.deptno,a.job;
Materialized view created.
SQL> exec dbms_rls.add_policy('MACHRABH','EMP',POLICY_FUNCTION=>'F_RLS_DEPTNO',POLICY_NAME=>'EMP_TEST');
PL/SQL procedure successfully completed.
SQL> set autotrace on explain;
SQL> select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname;
DNAME SUM(SAL)
-------------- ----------
RESEARCH 3015.5
ACCOUNTING 745
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=38)
1 0 HASH (GROUP BY) (Cost=7 Card=2 Bytes=38)
2 1 HASH JOIN (Cost=6 Card=4 Bytes=76)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cos
t=2 Card=2 Bytes=24)
5 4 INDEX (RANGE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (
Cost=1 Card=2)
6 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=9 By
tes=63)
SQL> delete from rewrite_table;
9 rows deleted.
Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3)
1 0 DELETE OF 'REWRITE_TABLE'
2 1 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 C
ard=3)
SQL> exec dbms_mview.explain_rewrite('select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname','MACHRABH.EMPMV','test');
PL/SQL procedure successfully completed.
SQL> select message , query,rewritten_txt from rewrite_table;
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
QSM-01150: query did not rewrite
select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname
select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname
QSM-01284: materialized view EMPMV has an anchor table EMP not found in query
select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname
select b.dname ,sum(sal) from machrabh.emp a, machrabh.dept b where a.deptno=b.deptno group by b.dname
MESSAGE
----------------------------------------------------------------------------------------------------------------------------------
QUERY
----------------------------------------------------------------------------------------------------------------------------------
REWRITTEN_TXT
----------------------------------------------------------------------------------------------------------------------------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=834
)
1 0 TABLE ACCESS (FULL) OF 'REWRITE_TABLE' (TABLE) (Cost=3 Car
d=3 Bytes=834)
Thanks Inadvance...
Thanks,
Ram
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #399547 is a reply to message #399406] |
Wed, 22 April 2009 21:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
This is your MV (note how I have used CODE tags to make it easier to read):
create materialized view machrabh.empmv
build immediate enable query rewrite as
select a.deptno,a.job
, sum(sal)
, grouping_id(a.deptno,a.job)
from machrabh.emp a
, machrabh.dept b
, machrabh.job c
where a.deptno=b.deptno
and a.job=c.job
group by a.deptno,a.job;
and this is your query
select b.dname
, sum(sal)
from machrabh.emp a
, machrabh.dept b
where a.deptno=b.deptno
group by b.dname;
Your MV joins machrabh.job, but your query does not. It is not functionally equivalent to use the MV.
What would happen if a row in machrabh.emp had a JOB that did not exist in machrabh.job? Such a row would be filtered by the MV, but NOT by your query. They would return different results.
I would be surprised if REWRITE worked even without the RLS policy.
Try dropping the RLS and see if you can get REWRITE working.
For the MV and the query to be functionally equivalent, you would need to either:
- Create a Foreign Key constraint on EMP.JOB referencing JOB.JOB and make that constraint ENABLED and VALIDATED, or
- Add a (redundant) join to JOB into your query.
Ross Leishman
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #399651 is a reply to message #399547] |
Thu, 23 April 2009 05:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ramabhupal
Messages: 10 Registered: April 2009 Location: hyd
|
Junior Member |
|
|
Hi ,
Yes it has already all the foreign keys and also not null constraints to the corresponding tables.
SQL> select owner,table_name,constraint_name,r_owner,r_constraint_name from dba_constraints where table_name='EMP' and owner='MACHRABH' ;
OWNER TABLE_NAME CONSTRAINT_NAME R_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------
R_CONSTRAINT_NAME
------------------------------
MACHRABH EMP SYS_C00382870
MACHRABH EMP SYS_C00382871
MACHRABH EMP FK_DEPTNO MACHRABH
PK_DEPT
OWNER TABLE_NAME CONSTRAINT_NAME R_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------
R_CONSTRAINT_NAME
------------------------------
MACHRABH EMP C_EMP_2_JOB_F MACHRABH
C_JOB_P
MACHRABH EMP PK_EMP
Thanks,
Ram.
|
|
|
|
|
Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #400022 is a reply to message #400008] |
Sat, 25 April 2009 01:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
ramabhupal
Messages: 10 Registered: April 2009 Location: hyd
|
Junior Member |
|
|
Hi Ross,
do you mean add a job to a query? No , it is not wokring for me.
Just i have created materialized view without joins.
SQL> select policy_name from dba_policies where object_name='EMP';
POLICY_NAME
------------------------------
EMP_TEST
SQL> set autotrace on explain;
SQL> select b.dname,sum(sal) from machrabh.emp a, machrabh.dept b,machrabh.job c
2 where a.deptno=b.deptno and a.job=c.job
3 group by b.dname;
DNAME SUM(SAL)
-------------- ----------
RESEARCH 3015.5
ACCOUNTING 745
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=54)
1 0 HASH (GROUP BY) (Cost=7 Card=2 Bytes=54)
2 1 HASH JOIN (Cost=6 Card=4 Bytes=108)
3 2 INLIST ITERATOR
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cos
t=2 Card=2 Bytes=24)
5 4 INDEX (RANGE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (
Cost=1 Card=2)
6 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=9 By
tes=135)
SQL> exec dbms_rls.drop_policy('MACHRABH','EMP','EMP_TEST');
PL/SQL procedure successfully completed.
SQL> select b.dname,sum(sal) from machrabh.emp a, machrabh.dept b,machrabh.job c
2 where a.deptno=b.deptno and a.job=c.job
3 group by b.dname;
DNAME SUM(SAL)
-------------- ----------
RESEARCH 3015.5
SALES 655
OPERATIONS 200
ACCOUNTING 1160
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=5 Bytes=95)
1 0 HASH (GROUP BY) (Cost=8 Card=5 Bytes=95)
2 1 HASH JOIN (Cost=7 Card=10 Bytes=190)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=6 B
ytes=72)
4 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'EMPMV' (MAT_VIEW RE
WRITE) (Cost=3 Card=10 Bytes=70)
Thanks,
Ram
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 23:49:40 CDT 2024
|