Rasmus Olesen wrote:

>Hi
>
>When i submit some SPs i get these strange plan-warnings, f.x. :
>
>INFO: Warning: SubOptimal Query Line 6 Column 7 - May not use Index
>(PLAN (T_ENTRIES NATURAL))
>
>

Hi Rasmus,

It's just a hint given by Marathon. If you create the same procedure in
ISQL you will ot get any warning.

>on this example :
>
>CREATE PROCEDURE A
>returns (
>maxtid int)
>AS
>begin
>select max(tid) from T_ENTRIES into :maxtid;
>end
>
>..and it seems to be because the 'tid' is a primary key in the
>table. It also happenes with a foreign key though, and even
>though i haven't explicitly defined any indicies anywhere in the
>entire db.
>
>(1.1) I'm guessing that defining a primary key, makes FB1.5(which
>i'm using btw.) implicitly defines an automatic index on the same
>column, at least thats what Marathon 3 told me...but is that the
>case ??
>
>
>

When you create a PK, an index is automatically created (the same for FK
and for unique constraints). But FB could just use descending indices
for MAX function, ascending indices will be good form MIN, but useless
for MAX.

You have two options:
1.) Create your PK using a DESCENDING index
2.) leave the axcending index created automatically by your PK
constraint and create another index on the same column as descending.

I would go the option 1

>(1.2) Why is this warning coming up ?
>
>

Just a hint.

>(1.2.1) Because it might execute slower than it optimally can ?
>(=Performance warning)
>
>

yes.

>(1.2.2) Because it might execute a give a wrong answer ?
>(=Correctness warning)
>
>
>

no.

>(1.3) How can i modified these SPs of this form that gives this kind
>of warnings ??
>
>
>

Create an index, but is not always the sollution, you should understand
waht your query will do and think if will be good to have an index to
speed it up or not, sometimes (if the table was small enough), than the
speed diference should not be noted. And have a lot of indices could
confuse the optmizer in complex queries.

>(1.4) Should one always change a SPs just because a compilation
>generates a warning or are some of them conceptual and therefore
>execution-wise irrelevant ?
>
>

no... should be a case by case analyzes.

>(2) What are the professional guidelines for indicies anyway, when
>SHOULDN'T they(should = faster searches, got that part) be used in a
>table ?
>
>
>

You should see how your query is execute without the index, then create
the index and see how it performs, look on the generated plan for the
both cases, indices could improve the select speed but will slowdown the
insert/update. One should take this into consideration.

Make your tests with more realistic data you can, you can't expect the
same plan/speed when you run your query with very different data.

Indices are good, but useless indices are bad, very bad !

>tia.
>/Rasmus Olesen
>
>

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br