0

I am new to tuning and I am trying hard learn things as Oracle is like an ocean. I have a query built on views that is running longer. I have query like:

select t2.col1,t2.col2
from vw_tab1 t1,vw_tab1 t2,tab3 t3
where t1.col3=123
and t2.col3<>123
and t2.col4=t1.col5
AND t3.col2=t2.col2;

We are using single view multiple times in the same query. Does this run the query in view twice? Suggestions much appreciated. I am running this query in Oracle 11g single instance database.

user3225011
  • 161
  • 1
  • 2
  • 12
  • 1
    You seem to be cross-joining to `t3` as there is no link to the other too. Using `join` syntax would make that mich clearer. – Alex Poole Jan 22 '14 at 21:05
  • @Alex Poole, there is one more join with t3 like t3.col2=t2.col2. Also using Join syntax would not make better performance right? – user3225011 Jan 22 '14 at 21:08
  • 2
    In the text of your statement, you say that you're using a single view multiple times. In the query you posted, you're using different views. If you've omitted joins as well, please edit your question to add those as well. Using the SQL 99 syntax shouldn't change performance unless it helps you to identify the fact that you've omitted some join conditions. For any tuning request, including the query plan will be very helpful as will information about things like how many rows are in each object, how selective the various predicates are, etc. – Justin Cave Jan 22 '14 at 21:14
  • I have changed the query. but my main question is when I run this query does it going to run the view multiple times since view is not a table that stores the data? – user3225011 Jan 22 '14 at 21:24
  • Table with alias t3 is not joined with any other table - it is cartesian product... – igr Jan 22 '14 at 21:41
  • "running longer" than what? – Jeffrey Kemp Jan 23 '14 at 06:33

1 Answers1

1

Yes, the tables that make up the view vw_tab1 will be queried twice, once for each instance ( t1 and t2 ). If you were to issue an explain plan of your statement, you should see oracle querying the tables that make up the view, not the actual view.

edub
  • 71
  • 2