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.

Would anybody have any suggestions?

Thanks,

Stewart Bourke