Stewart,
This is NOT an IBO question but I'm going to comment on your query because
it has errors which affect the way IBO works...

GOTO END

At 07:29 PM 7/07/2003 +0000, you wrote:

>I have 3 tables, and have created a join which seems to necessitate
>an outer join, but for some reason the outer join seems to ignore any
>indices.
>
>select group_products.gpr_product, product.prd_code,
>product.prd_text, product_info.pri_dado,product_info.pri_size,
>product_info.pri_light,
> product_info.pri_decor, product_info.pri_text,
>product_info.pri_dark,product_info.pri_border, product_info.pri_pencil
>
>from group_products,products
> left outer join product_info on product_info.pri_owner =
>products.prd_sin
>
>where
>gpr_product = prd_sin
>and gpr_item = 749
>
>order by prd_code
>
>The plan in ibsql shows me:
>
>PLAN SORT (MERGE (SORT (GROUP_PRODUCTS INDEX (GPR_IDX_1)),SORT (JOIN
>(PRODUCTS NATURAL,PRODUCT_INFO INDEX (PRI_IDX_1)))))
>
>The product table has several thousand records, and the product_info
>table might only have 50-100, and I want every record from products,
>even the ones where there are no corresponding record on
>product_info. The query takes anything up to 10-15 seconds to run,
>because of the natural plan on products
>
>If I try to add a plan join using an index that exists on
>products.prd_sin, the application reports that the index cannot be
>used.

First, you are committing the cardinal sin of mixing implicit (SQL-89) and
explicit (SQL-92) syntaxes. This is probably what is causing the index on
Products to be ignored. Worse, it messes up IBO (you need to use JoinLinks
for the inner join and this query will make searching clumsy as well) and -
if you are planning to use Firebird 1.5 and onward, the mixed join syntaxes
will be rejected.

I'm assuming you are using InterBase currently, because Firebird won't
accept your unqualified where and order by criteria.

Something like the following should use indexes if it would be useful to do
so, though the IB/Fb optimizer is not friendly towards tables that have
indexes stepping on one another...

select
gp.gpr_product,
p.prd_code,
p.prd_text,
pi.pri_dado,
pi.pri_size,
pi.pri_light,
pi.pri_decor,
pi.pri_text,
pi.pri_dark,
pi.pri_border,
pi.pri_pencil
from group_products gp
join products p /* See note */
on p.somekey = gp.somekey (?? p.prd_sin = gp.gpr_product??)
left join product_info pi
on pi.pri_owner = /* p.prd_sin */ gp.gpr_product /* See note */
where
/* gpr_product = prd_sin this is not a WHERE criterion */
gp.gpr_item = 749
order by p.prd_code

Note about the joining. If you want a set that returns a row containing
the product_info data, regardless of whether a product record exists, then
use a left (outer) join for product as well and link product_info from
group_products rather than from product.

If you want only matching products in the set, then keep the inner join and
(you can) make the left join using p.prd_sin.

Make sure all your join criteria have indexes both sides that the optimizer
can use.

Helen