Home » Server Options » Streams & AQ » Can we use joins, where & subquery using streams
Can we use joins, where & subquery using streams [message #268702] Wed, 19 September 2007 07:35 Go to next message
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
Re: Can we use joins, where & subquery using streams [message #268723 is a reply to message #268702] Wed, 19 September 2007 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
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
Re: Can we use joins, where & subquery using streams [message #268733 is a reply to message #268723] Wed, 19 September 2007 09:01 Go to previous messageGo to next message
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.
Re: Can we use joins, where & subquery using streams [message #268748 is a reply to message #268733] Wed, 19 September 2007 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FAST should work.

Regards
Michel
Re: Can we use joins, where & subquery using streams [message #268954 is a reply to message #268702] Thu, 20 September 2007 03:36 Go to previous messageGo to next message
carthik7
Messages: 5
Registered: September 2007
Junior Member
can you give the reasons, why using join, where conditions in streams is not possible.
Re: Can we use joins, where & subquery using streams [message #268958 is a reply to message #268954] Thu, 20 September 2007 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you give me how you do this.

Regards
Michel
Re: Can we use joins, where & subquery using streams [message #268966 is a reply to message #268702] Thu, 20 September 2007 04:30 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

http://www.orafaq.com/forum/images/message_icons/icon4.gif

[Updated on: Thu, 20 September 2007 04:31]

Report message to a moderator

Previous Topic: need good document / pdf on streems
Next Topic: update errors overwrite
Goto Forum:
  


Current Time: Fri Aug 29 12:04:20 CDT 2014

Total time taken to generate the page: 0.12299 seconds