Re: ideas for tuning a strange query?

This might sound harsh, but isn't intended to be.

Is it too late to re-engineer the ARTICLE table? You seem to have a composite UID made up of artno||articleno etc and this will always cause problems. e.g. if you know the articleno, how will you access the table via the index? The part you know is half way down the identifier ...

Better to break the ARTIKEY column into separate columns to form the primary key. You can then access the table by all or (leading) part of the primary key, or (if appropriate) have separate indexes on articleno, dimension and so on.

Not much help to you up-front, but the design looks distinctly fragile to me.

regards

Glenn

In article <905pm1$39b$1_at_nnrp1.deja.com>,   tjmxyz_at_my-deja.com wrote:
> I have a strange query scenario.
> Maybe someone would care to comment.
>
> The query looks like
>
> SELECT
> A Bunch of field
> FROM
> SYSADM.SPECIFIC B
> , SYSADM.ARTICLE A
> WHERE
> RTRIM(B.ARTNO)||'*'||RTRIM(B.ARTICLENO)||'*'||RTRIM(B.DIMENSION)
> ||'*'||RTRIM(B.INTCOLOR)=A.ARTIKEY(+);
>
> There is a unique index on ARTIKEY.
>
> any ideas for optimizing this query?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 09:43:11 CST