Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    Unanswered: How to improve performance of the following query?

    How to improve performance of the following query?
    Any recommendations?

    SELECT distinct
    A.ROWID ,
    A.BAN ,
    A.BILL_SEQ_NO ,
    A.PRODUCT_TYPE ,
    A.BILLING_NO ,
    A.FEATURE_CODE ,
    A.SERVICE_FTR_SEQ_NO ,
    A.PRICE_PLAN_SEQ_NO ,
    A.PRICE_PLAN_CODE ,
    TO_CHAR (A.PRICE_PLAN_EFF_DATE, 'YYYYMMDD') ,
    A.PRICE_PLAN_LEVEL_CD ,
    TO_CHAR (A.RATING_EFF_DATE, 'YYYYMMDD') ,
    A.REF_IND ,
    A.SOURCE_FEATURE_CODE ,
    A.PRORATION_FACTOR ,
    A.RATING_METHOD ,
    A.COMMON_TIER_PERIOD ,
    DECODE(LTRIM(RTRIM(A.FEATURE_CODE)),
    'GLBF',
    '1',
    A.RERATE_REQUEST_CD),
    A.RERATE_RESULT_CD ,
    A.STEP_TIER_QTY_PRD_1 ,
    A.STEP_TIER_QTY_PRD_2 ,
    A.STEP_TIER_QTY_PRD_3 ,
    A.STEP_TIER_QTY_PRD_4 ,
    A.STEP_TIER_QTY_PRD_5 ,
    A.STEP_TIER_QTY_PRD_6 ,
    A.STEP_TIER_QTY_COMBD ,
    A.STEP_TIER_NO_PRD_1 ,
    A.STEP_TIER_NO_PRD_2 ,
    A.STEP_TIER_NO_PRD_3 ,
    A.STEP_TIER_NO_PRD_4 ,
    A.STEP_TIER_NO_PRD_5 ,
    A.STEP_TIER_NO_PRD_6 ,
    A.STEP_TIER_NO_COMBD ,
    A.IU_ALLOWED_COMBD ,
    A.IU_METHOD ,
    A.IU_USED_COMBD ,
    A.PP_TYPE ,
    A.PP_AU_TYPE ,
    A.BASIC_PP_AMT
    FROM
    AU_MODEL B,
    AU_MODEL A
    WHERE
    A.BAN = 111111111
    AND A.BILL_SEQ_NO = 22
    AND A.RERATE_RESULT_CD = 0
    AND
    ( A.RERATE_REQUEST_CD != 0
    OR A.RATING_METHOD = 'X'
    OR A.RATING_METHOD = 'E'
    OR
    B.BAN = A.BAN
    AND B.BILL_SEQ_NO = A.BILL_SEQ_NO
    AND B.PRODUCT_TYPE = A.PRODUCT_TYPE
    AND B.PRICE_PLAN_CODE = A.PRICE_PLAN_CODE
    AND B.FEATURE_CODE = A.FEATURE_CODE
    AND B.RERATE_REQUEST_CD = 0
    AND B.RERATE_RESULT_CD = 0
    AND B.FEATURE_CODE = 'GLBF' )
    ORDER BY
    A.PRICE_PLAN_LEVEL_CD ,
    A.PRODUCT_TYPE ,
    A.PRICE_PLAN_CODE ,
    A.PRICE_PLAN_EFF_DATE ,
    A.REF_IND,
    A.PP_TYPE ,
    A.FEATURE_CODE ,
    A.SERVICE_FTR_SEQ_NO ,
    A.PRICE_PLAN_SEQ_NO


    John Smith

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    'order by' and 'distinct' is killing you.

    I imagine it runs a lot faster without the distinct. Do you really get duplicate rows with that HUGE query???

    regarding this line: DECODE(LTRIM(RTRIM(A.FEATURE_CODE)),
    Just use: TRIM(A.FEATURE_CODE)

    where are you linking the two tables together?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Posts
    30
    Your query does not have a very well define where clause.
    You might be getting cartesian product as a result set.

  4. #4
    Join Date
    Jul 2003
    Location
    Denver
    Posts
    2
    in addition to the other replies....

    Do you have the columns referenced in the WHERE clause indexed? It looks like several columns might also be candidates for bitmap indexes which would help in performance (A.RATING_METHOD,A.RERATE_RESULT_CD,A.RERATE_REQUE ST_CD)

    Let me know if you want to know the deal about bitmap indexes.

    As noted before, DISTINCT and ORDER BY usually detract from performance as they require alot of sort space.

    Have you used EXPLAIN PLAN to evaluate the execution plan for this query to see what it is doing?

  5. #5
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36
    Originally posted by elevenpast
    in addition to the other replies....

    Do you have the columns referenced in the WHERE clause indexed? It looks like several columns might also be candidates for bitmap indexes which would help in performance (A.RATING_METHOD,A.RERATE_RESULT_CD,A.RERATE_REQUE ST_CD)

    Let me know if you want to know the deal about bitmap indexes.

    As noted before, DISTINCT and ORDER BY usually detract from performance as they require alot of sort space.

    Have you used EXPLAIN PLAN to evaluate the execution plan for this query to see what it is doing?
    Thanks for you reply.

    How i can get this EXPLAIN PLAN?
    Please provide more info about bitmap indexes. How and When it make sense to use it?

    Thanks a lot in advance.
    John Smith

  6. #6
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    THANK YOU guys to all of you who answered me.

    THANK YOU guys to all of you who answered me.
    John Smith

  7. #7
    Join Date
    Jul 2003
    Location
    Denver
    Posts
    2

    Cool ExplainPlan & Bitmap index tidbits

    Originally posted by _John Smith
    Thanks for you reply.

    How i can get this EXPLAIN PLAN?
    Please provide more info about bitmap indexes. How and When it make sense to use it?

    Thanks a lot in advance.
    Explain Plan: The simplest way would be to use a third product like TOAD by Quest Software (free download). You can run a script called toadprep.sql in your development database (must have dba privs) and use lots of helpful tools. You can type a query in SQLBrowser and choose "Explain Plan Current SQL" from the SQL-Window menu. This will display graphically the same information you would get going the old-fashioned way (which I am not dissing, I have used both alot). For the old fashioned method, you can find those steps in any DBA Handbook. I would suggest using TOAD, it makes everything very simple plus you have other beneficial tools at your disposal. www.quest.com

    Bitmap Indexes: These are different indexing structures than normal b-tree indexes. They are more appropriate when indexing data with relatively few distinct values. For example, if you have a table called PERSON with a column called GENDER, there are obviously only two distinct values for this column ('M','F', you get the point). If you do queries that include GENDER in the where clause ('WHERE GENDER = 'M') you would want to have this column bitmap indexed. You would see performance gains querying this column with this type of index. Take into account there is a trade-off with indexes that must be considered, it will take longer to do inserts and updates on these indexes. There is no hard rule here, each situation needs to be evaluated separately. Oh yeah, bitmap indexes are not available in Oracle Standard Edition (to my dismay).

  8. #8
    Join Date
    Apr 2002
    Location
    USA-CA
    Posts
    36

    10x

    10x
    John Smith

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •