OraFAQ Forum: Streams & AQ » Can we use joins, where & subquery using streams

Home » Server Options » Streams & AQ » Can we use joins, where & subquery using streams

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can we use joins, where & subquery using streams [message #268702]

Wed, 19 September 2007 07:35

carthik7
Messages: 5
Registered: September 2007

Junior Member

SELECT
MAX(t2.id) AS ids,
t1.id,
t1.no
FROM
table1 t1
INNER JOIN table2 t2
ON t1.no = t2.no
AND t1.id > 0
AND t2.loc IN
(SELECT value FROM table3 WHERE
no=220)
AND t1.ind = 1
GROUP BY t1.id, t1.no

Can we optimize this query using streams. i.e can we use the subquery,join and where condition of the above query in the source tables and insert the result set into a target table using streams

Report message to a moderator

Re: Can we use joins, where & subquery using streams [message #268723 is a reply to message #268702]

Wed, 19 September 2007 08:16

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no relation between Streams and optimisation.
Streams is for replication.

If you want a table with the result of your query, a materialized view seems to be the feature you need.

Regards
Michel

Report message to a moderator

Re: Can we use joins, where & subquery using streams [message #268733 is a reply to message #268723]

Wed, 19 September 2007 09:01

carthik7
Messages: 5
Registered: September 2007

Junior Member

Thanks for your reply Michel.

Can we use simple where or join condition in streams.I heard that we can use user procedure in streams, but not sure how we can use it.

If we can use materialized view, which refresh method is recommended like fast, complete.

Report message to a moderator