Re: driving_site hint in Materialized View definition
Date: Sat, 27 Jun 2009 15:11:11 +0100
"Veeru71" <m_adavi_at_hotmail.com> wrote in message
> Is there any restrinction on using DRIVING_SITE hint in materialized
> view definition ?
> It seems to be ignoring the hint and is transferring the remote EMP
> table data (which is huge compared to the local DEPT table) to the
> local server for the join.
> The hint works perfectly fine if I run just the SELECT statement.
> CREATE MATERIALIZED VIEW EMP_MV
> REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY
> SELECT /*+DRIVING_SITE(e)*/ e.empno, e.empname , .........
> FROM emp_at_RSITE e, dept d
> WHERE e.deptno = d.deptno;
> We are using Oracle 10.2.0.4
> Thanks for your help.
The driving_site() hint is not legal in "create as select" or "insert as select". The select statement has to operate at the database where the create or insert takes place.
I think if you search metalink, you'll find a note that makes this point - but I don't have a reference to the doc ID.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Sat Jun 27 2009 - 09:11:11 CDT