I have the follwing query it takes approx 1 minute to return which is not acceptable to the business. Any ideas of how to make it faster, I suspect the distinct is casuing problems. It is doing FTS of Tibex_bestexrel with have 2000 rows within it.
CREATE OR REPLACE VIEW TIBEX_BESTEXRELVIEW ( PRICEIMPROVEMENT,
PARTICIPANTIDMM, INSTRUMENTID, MAXQTY, PARTICIPANTID,
OBMID, BESTEXECSTATUS, TIMESTAMP, PAYMENTBYMM,
LASTINSTID, LASTINSTACKID, LASTREJECTCODE, LASTREJECTREASON,
MEID, COUNTERPARTYOBMID, BOARDID )
AS SELECT DISTINCT a.PriceImprovement, a.ParticipantIDMM, a.InstrumentID, a.MaxQty, a.ParticipantID, a.OBMID, a.BestExecStatus, a.Timestamp, a.PaymentByMM, a.LastInstID, a.LastInstAckID, a.LastRejectCode, a.LastRejectReason, a.MEID, a.CounterpartyOBMID, a.BoardID
FROM tibex_BestExRel a
WHERE Timestamp = (
FROM tibex_BestExRel b
WHERE a.ParticipantID = b.ParticipantID AND
a.ParticipantIDMM = b.ParticipantIDMM AND
a.InstrumentID = b.InstrumentID
Originally posted by slimdave A composite index on ParticipantID, ParticipantIDMM, InstrumentID, and maybe timesamp sounds like a good idea. Although 200 rows is so little that the index might possibly get ignored.
Are you sure that the distinct is also required?
To Add, If Card of these Cols is pretty less, go for BIT Map Index.
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"