Hello all, I have a table that contains columns that describe an item. E.g.
ITEM table
ID bigint not null
COLOUR bigint not null (indexed)
FINISH bigint (indexed)
Then another table that contains a qty of items that is reserved. I.e. the
specification and qty of items reserved. Colour is mandatory and Finish is
optional
RESERVATION table
ID bigint not null
COLOUR bigint not null (indexed)
FINISH bigint (indexed)
QTY integer not null
Now to determine available stock (item count - reservations) I issue the
following query:
select
STOCK.COLOUR,
STOCK.FINISH,
STOCK. STOCK_QTY,
R.QTY as RESERVED_QTY,
STOCK. STOCK_QTY - R.QTY as RESERVED_QTY as AVAILABLE_QTY
from
(
/* Get summary of item stock */
select COLOUR,FINISH,count(*) as STOCK_QTY
from ITEM
group by COLOUR,FINISH
) STOCK
left outer join RESERVATION R /* Get Reservations */
on R.COLOUR = STOCK.COLOUR -- Match mandatory field
and coalesce(R.FINISH,-1) = coalesce(STOCK.FINISH,-1) -- Match optional
field
The reason for using the coalesce in the join is to force NULL Finishes to
match. The problem is that the query plan does not use the indices on FINISH
because of the coalesce. This results in HUGE number of reads on the
RESERVATION table. Think it table scans the RESERVATION table for each ITEM
row. I tried a computed index, but the query did not use that index. The only
(non FB) way to optimise the query is to actually refactor the tables and
extract a SPECIFICATION table. In my case this will mean a HUGE amount of work
and not feasible at this stage.
So my question is: How to optimise this query given that I use FB 2.5.3…?
Thanks
