Forcing Ingres to Use an Index

Author: Keith Moodie 
Subject: Forcing Ingres to use an index

>Hi,
>
>I want to force ingres to use a particular index, what is the best way
>of doing it?
>Is it safe to use the tidp attribute in the index ?


Hi Keith.

The option 2 is shown in the Database Administrator's Guide (OI1.2 p.12-37,
OI2.0 p.12-45, II p.13-40).
This text was extracted from Ingres II DBA's Guide:

You can force a secondary index to be used by referencing it in the query,
but the optimizer should ensure that this is never necessary.
To force use of a secondary index:

select * from emp where emp.name='Shigio';

becomes:

select * from emp, xname where xname.tidp=emp.tid
            and xname.name='Shigio';

I already used that way, and it worked fine and safe.
Regards,
________________________
Leandro Fava
DBA Ingres
UNISC



From: Chip Nickolett 
Subject: Re: Forcing Ingres to use an index

Hello Keith,

Keith Moodie  wrote in message
news:37CDE433.167E@dnr.qld.gov.au...
> Hi,
>
> I want to force ingres to use a particular index, what is the best way
> of doing it?

The best thing to do is evaluate the query execution plan and make
a determination as to why a valid index is not being used?  Things
to consider are statistics, not using "leading edge" of the key (i.e.,
the initial key / index columns), not having a compatible storage
structure (e.g., using hash when there is a set of values to be
retrieved / merged will cause a table / index scan), not having the
key / index columns in the order that Ingres wants (usually due to
granularity, but sometimes due to sort order (although Ingres will
usually just throw in a sort)).

> Is it safe to use the tidp attribute in the index ?

You never want to save a TID / TIDp in a table or query but it
should be safe (although not desirable) to explicitly join a table
and index via TID / TIDp.  This does place an unnecessary
dependency on the query that could cause a failure or
performance issue at a later time (if the index disappears or
if it is given different characteristics).  So this is definitely
something that should only be done as a last resort.

> Thanks in anticipation
> --
> Keith Moodie,   moodiek@dnr.qld.gov.au
> Queensland Centre for Climate Applications,
> Queensland Department of Natural Resources,
> Australia

Chip Nickolett           ChipN@Comp-Soln.com
Comprehensive Consulting Solutions, Inc.   (www.Comp-Soln.com)
Phone:  414-544-9954     Fax:  414-544-1236

President - North American Ingres Users Association (NAIUA)
Ingres Q & A
Back to William's Home Page
Email William