Re: joins and performances : general question

In article <81bdnb$2b0$1_at_minus.oleane.net>,   "Christian Bruno" <brunoc_at_ifrance.com> wrote:
> hello,
> it is a very general question :
>
> i have to write sql queries using lot of Joins (mainly left outer and
inner
> joins)
>
> is there rule(s) to apply to write these sql orders, having knowledge
of the
> average number of rows in my tables ? for example : which order to
use for
> joins in a particular query ? is it better to begin or to end with the
join
> that retrieves the more rows in the database ? it is better to use
INNER
> joins than LEFT OUTER JOINS ?

The answer will depend in the database server and the level of statistics and/or data distributions you are keeping for the relevant tables. Informix, for example, can maintain no stats, basic stats (index depth, index width, number of rows, rowsize, and min/max keys), or various levels of data distributions for some or all columns from sampled to actual count. The Informix optimizer uses these stats to determine the best order in which to search the listed tables and which of several join plans to use. The optimizer actually calculates the relative cost of ALL possible query paths (unless you constrain it to only test most likely candidates) and chooses the best path. Once the query path (which order to search the tables) is chosen a join method (nested loop, hashed lookup, dynamic index, indexed loop) and physical I/O method (serial, parallel, indexed, table scan, etc) are chosen.

Other database servers such as Oracle, DB2, Sybase, etc. perform similar steps with varying levels of intelligence and success. Informix's optimizer is probably the best in the industry, for now.

Art S. Kagel

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 23 1999 - 13:37:20 CST