?yvind Gjerstad wrote:
>
> Has anyone tried this combination?
>
> We upgraded some of our servers from ODS 7.14 to IDS 7.30 this weekend.
> We then discovered that one front-end running 4gl 6.04 (and esql/c 7.14)
[SNIP]
> We got sequential scan, whilst this query used the unique index:
>
> sprintf(s,"select d from tab1 where a = '%s' and b = '%s' and c = '%s'",
> p_a,p_b,p_c);
> $prepare sel from $s;
> $declare c_sel cursor for sel;
> $open c_sel;
>
> When the same program was compiled with esql/c 7.30 it used the index.
> This was on HP-UX 10.20.
> One more strange thing was that one front-end running 4gl 4.12
> was OK!
This usually happens with fragmented tables and has nothing to do with
4GL itself. If the table is fragmented, and there are replaceable
parameters equated to columns that are part of the fragmentation
expression, the optimizer does not know at PREPARE time which fragments
will be hit and so must develop a plan to search all fragments. Once
this happens it may also decide to scan the table especially if the
index is attached.
You might try detaching the index unfragmented then the optimizer will
have the option of using the unfragmented index. This sometimes works.
This solution depends heavily on proper statistics and distributions.
Art S. Kagel