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

'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?

Your query does not have a very well define where clause.
You might be getting cartesian product as a result set.

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.

THANK YOU guys to all of you who answered me.

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).

10x