query tunning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: query tunning

  1. #1
    Join Date
    Aug 2001
    Posts
    390
    any one had suggestion on this query ??



    [Edited by mike73 on 11-07-2001 at 11:50 AM]

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Current:

    A.BA_NO = QCTRL_BA_USAGE.BA_NO AND
    A.BA_SUF = QCTRL_BA_USAGE.BA_SUF AND
    QCTRL_BA_USAGE.BA_USAGE_CD = 'P' AND
    A.INACTIVE_IND = 0 AND

    ( A.BA_NO = '1' AND A.BA_SUF = 0)
    OR (A.BA_NO = '1' AND A.BA_SUF = 0)
    OR (A.BA_NO = '1' AND A.BA_SUF = 0)
    OR (A.BA_NO = '2' AND A.BA_SUF = 0)
    OR (A.BA_NO = '2' AND A.BA_SUF = 0)
    OR (A.BA_NO = '2' AND A.BA_SUF = 0)

    The condition

    A.BA_SUF = 0

    must be satisfied for all members of the result set. Perhaps it should appear just once.

    Hope that helps.
    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Aug 2001
    Posts
    111
    Check to see the distribution of values within each column in the query.

    If the distribution is well spread a normal index will help in most case. But you may need to add a histogram for the CBO to use the index. Look up histrograms in the doco for more info.

    Also if a lot of those columns are flag type columns, i.e they have a low number of distinct values compared to the no of rows in the table (less than 1%)
    eg
    QCTRL_BA_USAGE.BA_USAGE_CD = 'P'
    A.INACTIVE_IND = 0
    A.BA_NO = '1'
    A.BA_SUF = 0

    These columns might benefit from adding a bitmap index, especially considering you are using the OR function.

    To understand bitmap indexes more, there is a very good explanation in the concepts part of the manual.

    Have Fun


    Performance... Push the envelope!

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mike73
    any one had suggestion on this query ??


    [Edited by mike73 on 11-07-2001 at 11:50 AM]
    Nope....

    It should fly on any mainframe or large cluster or even tiny PC.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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