Good afternoon!

I have 2 tables stkmaster (50k records) and stkgrnchild (180k records) where I want to get total quantity received particular stock id.

The sql statement is :

select coalesce(sum(gc.qty_nu),0)
from stkgrnchild gc
join stkmaster sm
on gc.stkmasterrowid_bi = sm.rowid_bi
where sm.stkid_vc = '38338'

I am running the statement in Ibexpert and the performance analysis show that it loops through full table of stkgrnchild to get the result.

The plan of the executiion :
PLAN JOIN (GC NATURAL, SM INDEX (PK_STKMASTER))


The ddl for both table as follow :

ALTER TABLE STKGRNCHILD ADD CONSTRAINT PK_STKGRNCHILD PRIMARY KEY (ROWID_BI);

ALTER TABLE STKGRNCHILD ADD CONSTRAINT FK_STKGRNCHILD_GRN FOREIGN KEY (STKGRNROWID_BI) REFERENCES STKGRN (ROWID_BI);
ALTER TABLE STKGRNCHILD ADD CONSTRAINT FK_STKGRNCHILD_STKMAS FOREIGN KEY (STKMASTERROWID_BI) REFERENCES STKMASTER (ROWID_BI);


CREATE INDEX STKGRNCHILDIDX_SOURCE ON STKGRNCHILD (SOURCEREFROWID_BI);


ALTER TABLE STKMASTER ADD CONSTRAINT UNQ1_STKMASTER UNIQUE (STKID_VC, COMPANYROWID_BI);


ALTER TABLE STKMASTER ADD CONSTRAINT PK_STKMASTER PRIMARY KEY (ROWID_BI);


ALTER TABLE STKMASTER ADD CONSTRAINT FK_STKMASTER_BRAND FOREIGN KEY (STKBRANDROWID_BI) REFERENCES STKBRAND (ROWID_BI);
ALTER TABLE STKMASTER ADD CONSTRAINT FK_STKMASTER_CAT FOREIGN KEY (STKCATEGORYROWID_BI) REFERENCES STKCATEGORY (ROWID_BI);
ALTER TABLE STKMASTER ADD CONSTRAINT FK_STKMASTER_CATSUB FOREIGN KEY (STKCATEGORYSUBROWID_BI) REFERENCES STKCATEGORYSUB (ROWID_BI);
ALTER TABLE STKMASTER ADD CONSTRAINT FK_STKMASTER_COMP FOREIGN KEY (COMPANYROWID_BI) REFERENCES COMPANY (ROWID_BI);
ALTER TABLE STKMASTER ADD CONSTRAINT FK_STKMASTER_UOM FOREIGN KEY (STKUOMROWID_BI) REFERENCES STKUOM (ROWID_BI);


Any help to improve the performance is greatly appreciated.

Thank you very much and have a great day!

Wesly