Message-Id: <10574.113319@fatcity.com> From: Alex Hillman Date: Sun, 30 Jul 2000 13:55:23 -0400 Subject: Joining result set with another result set This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BFFA4F.58BD8C70 Content-Type: text/plain; charset="iso-8859-1" Let's assume I have join of several table that produce result set and another join of different table that produce another result set. I need to join these result sets or I need to use second result set in a subselect for the first join. I see several ways to do it. First is to create views for these result sets and then join them. How CBO will resolve this? I remember something that depending on the complexity of views and one of parameters CBO will execute first view selects, create temporary result sets and then will work with them to resolve query. Or combine selects in views into one select. Could somebody please remind me criterias for optimizer to decide one way or another. Second - create temporary tables where to put result sets and then use them. One advantage of these approach is that we can create indexes on these tables. Any advice would be appreciated. Alex Hillman ------_=_NextPart_001_01BFFA4F.58BD8C70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Joining result set with another result set

Let's assume I have join of several = table that produce result set and another join of different table that = produce another result set. I need to join these result sets or I need = to use second result set in a subselect for the first join. I see = several ways to do it.

First is to create views for these = result sets and then join them. How CBO will resolve this? I remember = something that depending on the complexity of views and one of = parameters CBO will execute first view selects, create temporary result = sets and then will work with them to resolve query. Or combine selects = in views into one select. Could somebody please remind me criterias for = optimizer to decide one way or another.

Second - create temporary tables where = to put result sets and then use them. One advantage of these approach = is that we can create indexes on these tables.

Any advice would be = appreciated.

Alex Hillman