|Subject||Re: [ib-support] Why is this select not using the index???|
> and it showed that the index of the "PartPrice"I do not see any good reason for this behaviour.
> table was not being used. Does anyone have any ideal why not?????
It is a problem in optimizer. Will you report it as bug on sourceforge?
Can you replace spviewparts() procedure with ordinary table or view ? It should work then.
> 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.
> 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);