query re-write

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 = (
SELECT MAX(Timestamp)
FROM tibex_BestExRel b
WHERE a.ParticipantID = b.ParticipantID AND
a.ParticipantIDMM = b.ParticipantIDMM AND
a.InstrumentID = b.InstrumentID
)

Thanks

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?

Quote:

---

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.

Else Normal.

not sure if distinct is reqd, by comp ind has sped it up!

Cheers..

Quote:

---

Originally posted by abhaysk
To Add, If Card of these Cols is pretty less, go for BIT Map Index.

Else Normal.

---

Whoa there tiger. I don't think that's a good idea.

Quote:

---

Originally posted by slimdave
Whoa there tiger. I don't think that's a good idea.

---

:confused: :confused:

If card is too low...if u build a index ( normal btree )...oracle will just process as if there were no index.

In our project, IMHO, the PK index built on 9 columns, among which 8 cols were low Card cloumns..( about 5 distinct recs )...and total number of recs in table were around 200 million.

The Most significant Col was PIN with high Card of about 50000 distinct recs.

When our APPL..query with PIN being joined with other table (REF TABLE) and rest Cols of the PK with some hard coded values..oracle choosed FTS ( not even Range scan on PK ).

When i built BIT map index on those rest 8 Cols..query went for BIT MAP range scan ( obivious ).

So why not build BIT MAP for low Card vauled Cols?

Abhay.

Because we don't know what type of database we are talking about. Bitmap indexes are great for reporting databases, but spell concurrency death to OLTP databases.

- Chris

BTW, when asking for help on the execution plan of a query... PLEASE supply the actual execution plan.

Of course it is doing a table-scan, there is no WHERE clause. What would you like it to do?

- Chris

Quote:

---

Originally posted by chrisrlong
Because we don't know what type of database we are talking about. Bitmap indexes are great for reporting databases, but spell concurrency death to OLTP databases.

- Chris

---

Chris :

Yes If, OLTP, then BIT MAP is not recomemded.

He didnt mention wat type of DB..assumed DW.

Abhay.