Joining result set with another result set

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Sun, 30 Jul 2000 13:55:23 -0400
Message-Id: <10574.113319@fatcity.com>

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>Joining result set with another result set</TITLE>
</HEAD>
<BODY>

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

Received on Sun Jul 30 2000 - 12:55:23 CDT