-
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
-
Re: query re-write
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?
-
Re: Re: query re-write
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.
funky...
"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"
-
not sure if distinct is reqd, by comp ind has sped it up!
Cheers..
-
Re: Re: Re: query re-write
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.
-
Re: Re: Re: Re: query re-write
Originally posted by slimdave
Whoa there tiger. I don't think that's a good idea.
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.
funky...
"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"
-
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
-
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.
funky...
"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"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|