query re-write
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: query re-write

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  4. #4
    Join Date
    Jul 2001
    Posts
    181
    not sure if distinct is reqd, by comp ind has sped it up!

    Cheers..

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    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"

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
  •  



Click Here to Expand Forum to Full Width