Usually when I change column selections to joins and allow no
ambiquities indexes are used, something like:

select a.SOURCE, a.PART, a.WAREHOUSE, a.CONDITION, a.NOTESFLAG,
a.DESCR, a.QTY, a.ONORDER, a.AVG_COST, a.PRICE, a.ORGIN,
a.WHOLEPRICE, a.RETAILPRICE, a.WEIGHT, a.LOC1, a.LOC2, a.SLOW,
b.PP_PRICECODE as pp_code

from spviewparts('902090', 'T', 'F', 'F') a
join partprice b where b.pp_part = a.part
order by a.SortOrder, a.qty desc, a.part, a.warehouse, a.condition

==================================================================

Michael L. Horne wrote:

>
> Hello
>
> I ran the query below and noticed that it was taking longer
> than I expected to run. So I checked the Performance Analysis
> in QuickDesk and it showed that the index of the "PartPrice"
> table was not being used. Does anyone have any ideal why
> not????? It looks good to me.
>
> Thanks
> Michael L. Horne
>
> ------ The Query ------
> select SOURCE, PART, WAREHOUSE, CONDITION, NOTESFLAG,
> DESCR, QTY, ONORDER, AVG_COST, PRICE, ORGIN,
> WHOLEPRICE, RETAILPRICE, WEIGHT, LOC1, LOC2, SLOW,
> (select b.PP_PRICECODE from partprice b
> where (b.pp_part = a.part)) as pp_code
> from spviewparts('902090', 'T', 'F', 'F') a
> order by SortOrder, qty desc, part, warehouse, condition
>
> ------ The Plan - reported used ------
>
> PLAN (B NATURAL)
> PLAN SORT ((CAT INDEX (CATBYPART))JOIN (INV INDEX (INVBYPART),WAREHOUSE
> INDEX (WAREHOUSEBYID)))
>
> ------ The DLL of the table ------
>
> CREATE TABLE PARTPRICE (
> PP_ID T_ID,
> PP_PART T_PART,
> PP_PRICECODE T_INTEGER,
> PP_DESCR T_DESCR12,
> PP_LIST T_CURRENCY,
> PP_WHOLE T_CURRENCY,
> PP_RETAIL T_CURRENCY,
> PP_COST T_CURRENCY);
>
> ALTER TABLE PARTPRICE ADD CONSTRAINT PARTPRICEBYID PRIMARY KEY (PP_ID);
> CREATE INDEX PARTPRICEBYPART ON PARTPRICE (PP_PART, PP_ID);
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/