db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jefffro <jfe...@us.ibm.com>
Subject How does WHERE condition in a VIEW with UNIONs get applied
Date Tue, 10 Sep 2013 14:03:35 GMT

Suppose I have a view defined as the union of multiple table queries e.g. 

create view v1 as 
 SELECT col1 as a , col2 as b FROM table1
 UNION
 SELECT col2 as a , col3 as b FROM table2
 UNION 
 SELECT col4 as a , col5 as b from table 3;

If I use this view in a query ... select * from v1 where a=5 ... what is the
behavior of applying the where condition into the view.  From what I can
tell, it seems to be running each unconditioned SELECT without a where
clause and then filtering the actual results that than applying the where
clause into the individual SELECTs of the union ... e.g. 

 SELECT col1 as a , col2 as b FROM table1 where col1=5
 UNION
 SELECT col2 as a , col3 as b FROM table2 where col2=5
 UNION 
 SELECT col4 as a , col5 as b from table3 where col4=5

I need to verify that this is not due to out of data statistics (I do not
think that is the case) but I felt it was probably worth finding out whether
I am being a little too optimistic on what the planner can do. 

Thanks, Jeff 





--
View this message in context: http://apache-database.10148.n7.nabble.com/How-does-WHERE-condition-in-a-VIEW-with-UNIONs-get-applied-tp133834.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Mime
View raw message