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
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

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

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 messageGo to next message
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.

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

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 messageGo to next message
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.

Report message to a moderator

Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #399763 is a reply to message #399651] Thu, 23 April 2009 22:27 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So did you try the other suggestion?

Report message to a moderator

Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #400008 is a reply to message #399763] Sat, 25 April 2009 00:09 Go to previous messageGo to next message
ramabhupal
Messages: 10
Registered: April 2009
Location: hyd
Junior Member
Yes, As i have no other go for now.

Thanks,
Ram.

Report message to a moderator

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 messageGo to next message
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

Report message to a moderator

Re: Query Rewrite Failed After RLS policy applied(QSM-01284) [message #400083 is a reply to message #400022] Sat, 25 April 2009 21:31 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just can't bring yourself to use those CODE tags can you?

I can't follow what you've posted because you did not format it.
Ross Leishman

Report message to a moderator

Previous Topic: PL SQL performance tuning
Next Topic: SQL PERFORMANCE ANALYZER
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 17:56:14 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.