|Subject||Re: [ib-support] view on a table to speed up queries|
|Author||Svein Erling Tysvær|
>does multicolumn indexes make sense if you use them in a non multicolumnYes, because duplicates in an index make things slow down. When running the
query, IB will only use those parts of the index that are useful.
>Do I need to make use of a explicit PLAN statement? I'm not sureMost likely you don't need an explicit plan. Just prepare your statement
>I understand how and when to use PLAN. Do you know of a good description
>on this feature?
and take a look at the resulting plan (e.g. use IB_SQL). Starting from the
beginning of it, see if it makes sense to obtain the desired info the way
IB wants to do it. If it doesn't look optimal (IB can occationally use too
many indexes) start by introducing stupid criteria to avoid certain indexes
(e.g. with "SELECT * FROM PERSON WHERE NAME = 'JAN AGERMOSE' AND COUNTRY =
'DANMARK'" you would probably avoid using an index for the country and
could rewrite your SQL to "SELECT * FROM PERSON WHERE NAME = 'JAN AGERMOSE'
AND (COUNTRY = 'DANMARK' OR 2=0)" to avoid this).
If you cannot make any sense of it, ask on this list citing the SQL in
question, any indexes used in the plan and a little bit about your data
(e.g. tell us if one criteria is fulfilled by 90% of the records).