Re: driving_site hint in Materialized View definition

"Veeru71" <m_adavi_at_hotmail.com> wrote in message news:b7d2085b-1330-45d0-bdfa-8b037814dff6_at_p18g2000pra.googlegroups.com...
> 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
> AS
> 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.html