Re: ideas for tuning a strange query?

<gbaron_at_colt-telecom.com> wrote in message news:905sie$5rb$1_at_nnrp1.deja.com...
> 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.

This was exactly the reason why I refrained from replying. Fragile definitely is an apt description.

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Nov 30 2000 - 10:39:13 CST