Forcing the use of an index
David Reed
Hi
If I have more than one index on a table, how do I force the use of a
specific index, on a select statment?
Can I do it in the creation of a view?
Is there any documentation on this issue that I can look at?
David Reed
david...@compuwin.co.za
sending to informix-list
June C. Hunt
> If I have more than one index on a table, how do I force the use of a
> specific index, on a select statment?
If you are using a version of Informix that supports optimizer directives,
you could use something similar to the following example:
SELECT {+INDEX(customers cust_index1)}
cust_num, cust_name FROM customers
WHERE cust_active = "Y";
The specific syntax of the optimizer directive, shown after the SELECT, is
just one of several possibilities. The two arguments given are table name
and index name. There is also an +AVOID_INDEX optimizer directive that you
can use.
> Can I do it in the creation of a view?
Yes.
> Is there any documentation on this issue that I can look at?
See the IBM Informix Guide to SQL: Syntax manual. Look up 'Optimizer
Directives'. I've just checked the IBM site and the server where the
documentation is stored is temporarily down. I was going to try to find out
which versions of Informix allow the optimizer directives (I don't recall -
would have to look it up), but you'll have to determine that for yourself
either by trying it or looking at the proper version of the documentation
once the server comes back up.
--
June Hunt
Bill Dare
See the Performance Guide for use of optimizer directives.
Bill
http://www-306.ibm.com/software/data/informix/pubs/library/ids_94.html
> -----Original Message-----
> From: David Reed [SMTP:david...@compuwin.co.za]
> Sent: Friday, July 09, 2004 9:07 AM
> To: inform...@iiug.org
> Subject: Forcing the use of an index
>
>
> Hi
>
> If I have more than one index on a table, how do I force the use of a
> specific index, on a select statment?
> Can I do it in the creation of a view?
>
> Is there any documentation on this issue that I can look at?
>
> David Reed
> david...@compuwin.co.za
>
>
>
>
> sending to informix-list
sending to informix-list
Dirk Moolman
I found this in my documents, but I haven't tested it yet:
AVOID_FULL Directive
The AVOID_FULL directive does not perform a full table scan on a listed table. The optimizer considers the various indexes that have been created that it can scan. If no index exists, the optimizer will perform a full table scan.
In the example above, the optimizer is forced to consider the indexes created for this table emp. If no indexes exist, the optimizer performs a full table scan.
Note Multiple directives can be used as long as they are in the same comment block.
For example:
SELECT --+AVOID_FULL(e),INDEX (e salary_indx)
name, salary
FROM emp e
WHERE e.dno = 1
AND e.salary > 5000;
Note You must refer to the tables alias in the directives if you have used an alias in the SQL statement. The example above shows that the emp table is referred to with the alias in the optimizer directives.
-----Original Message-----
From: owner-inf...@iiug.org [mailto:owner-inf...@iiug.org] On Behalf Of David Reed
Sent: Friday, July 09, 2004 3:07 PM
Subject: Forcing the use of an index
Hi
If I have more than one index on a table, how do I force the use of a
specific index, on a select statment?
Can I do it in the creation of a view?
Is there any documentation on this issue that I can look at?
David Reed
sending to informix-list
________________________________
<< ella for Spam Control >> has removed Spam messages and set aside Newsletters for me
You can use it too - and it's FREE! www.ellaforspam.com
sending to informix-list
Richard Harnden
> David Reed wrote:
>
>>If I have more than one index on a table, how do I force the use of a
>>specific index, on a select statment?
>
>
> If you are using a version of Informix that supports optimizer directives,
> you could use something similar to the following example:
>
> SELECT {+INDEX(customers cust_index1)}
> cust_num, cust_name FROM customers
> WHERE cust_active = "Y";
>
> The specific syntax of the optimizer directive, shown after the SELECT, is
> just one of several possibilities. The two arguments given are table name
> and index name. There is also an +AVOID_INDEX optimizer directive that you
> can use.
You're almost always better off leaving the choice of index to the
optimiser. The important thing is to have *decent* statistics.
--
rh
Mike Dunham-Wilkie
At 08:30 AM 7/9/04, Richard Harnden wrote:
>You're almost always better off leaving the choice of index to the
>optimiser. The important thing is to have *decent* statistics.
>
>--
>rh
The one place where I'm still left resorting to directives is in the case
of functional indices (e.g. indices built on a user-defined routine). If I
have a table
mytab (mycol integer, othercols, ...)
and an index built on myfunc(mycol),
how do I calculate statistics on the myfunc(mycol) values so that the
optimizer will be able to judge whether to use this index?
Mike
sending to informix-list
rh
> At 08:30 AM 7/9/04, Richard Harnden wrote:
>
>>You're almost always better off leaving the choice of index to the
>>optimiser. The important thing is to have *decent* statistics.
>>
>
> of functional indices (e.g. indices built on a user-defined routine). If I
> have a table
>
> mytab (mycol integer, othercols, ...)
>
> and an index built on myfunc(mycol),
>
> how do I calculate statistics on the myfunc(mycol) values so that the
> optimizer will be able to judge whether to use this index?
>
Good point. You can't, not really.
You can give a per-call/selectivity cost in the with-clause, to nudge
the optimiser in the right direction, but that's about it.
--
rh
Neil Truby
"Bill Dare" <da...@jevic.com> wrote in message
news:ccmamf$gjd$1...@news.xmission.com...
>
> See the Performance Guide for use of optimizer directives.
Be mindful that "directive" is a euphemism.
"Polite request" might be more accurate.
Bill Dare
[Bill Dare]
True. But he might not have found it in the manual if I said look for
optimizer "polite requests":-)
Bill
sending to informix-list
Tsutomu Ogiwara
Hi.
Optimizer directive is an available on 7.30.
HTH.
--
Tsutomu Ogiwara from Tokyo Japan.
ICQ#:168106592
>From: "June C. Hunt" <june_...@hotmail.com>
>Reply-To: "June C. Hunt" <june_...@hotmail.com>
>To: inform...@iiug.org
>Subject: Re: Forcing the use of an index
>Date: Fri, 9 Jul 2004 10:33:40 -0400
>
>David Reed wrote:
> > If I have more than one index on a table, how do I force the use of a
> > specific index, on a select statment?
>
>If you are using a version of Informix that supports optimizer directives,
>you could use something similar to the following example:
>
>SELECT {+INDEX(customers cust_index1)}
> cust_num, cust_name FROM customers
> WHERE cust_active = "Y";
>
>The specific syntax of the optimizer directive, shown after the SELECT, is
>just one of several possibilities. The two arguments given are table name
>and index name. There is also an +AVOID_INDEX optimizer directive that you
>can use.
>
> > Can I do it in the creation of a view?
>
>Yes.
>
> > Is there any documentation on this issue that I can look at?
>
>See the IBM Informix Guide to SQL: Syntax manual. Look up 'Optimizer
>Directives'. I've just checked the IBM site and the server where the
>documentation is stored is temporarily down. I was going to try to find
>out
>which versions of Informix allow the optimizer directives (I don't recall -
>would have to look it up), but you'll have to determine that for yourself
>either by trying it or looking at the proper version of the documentation
>once the server comes back up.
>
>--
>June Hunt
>
>
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
sending to informix-list