Order By Performance Problem on View

From: Scott <scottma_at_gmail.com>
Date: 17 Mar 2005 22:14:53 -0800
Message-ID: <1111126493.791966.123880_at_o13g2000cwo.googlegroups.com>

I have a view that contains the following SQL1 and it takes about 500ms to run with Explain Plan EXP1. However, if I add an order by clause(e.g. select * from view1 order by ID desc), it will then take about 3 mins to return. The explain plan for the query with the order by clause is as follows (EXP2).

The explain plan 2 shows that there is no changes to the index selection except adding the "Sort Order by" step. The total number of rows in proposal is 7000, owner is 7000 and routing is 7000 and the joined result should be around 7000 rows. I suspect that in order to process an order by clause, Oracle has to wait for every row to return before sorting. I tried the materialized view but since it is complex query, I cannot use on commit or fast refresh which then makes the MV useless in this scenario. (can't update every few seconds :-) ). I tried partial MV (joining two simple MVs and order by the result), but still doesn't work. I have seen someone posting similar question, but no response. Any suggestions are welcome! Thanks!

SORT_AREA_SIZE = 1MB
SORT_AREA_RETAINER_SIZE = 1MB SQL 1:
SELECT p.proposal_id AS ID, p.proposal_title, p.status AS status, o.common_name,
fcn_submit_date (p.proposal_id),
fcn_last_decision (p.proposal_id),
(CASE

           WHEN r.review_channel_list_id = 0
              THEN fcn_decode_sequence (r.segment_id_one, 'segment')
           WHEN r.review_channel_list_id = 1
              THEN 'Multi Segment'
           WHEN r.review_channel_list_id = 2
              THEN 'Research'
           WHEN r.review_channel_list_id = 3
              THEN 'Other'
        END
       ),
fcn_decode_primary_value (r.segment_id_two),
fcn_last_proposal_log_date (p.proposal_id),
fcn_calculate_slip (fcn_last_proposal_log_date (p.proposal_id)),
fcn_user_group (p.proposal_id, 0),

fcn_last_routing (p.proposal_id),
fcn_submitter_name (fcn_user_group (p.proposal_id, 0)),

p.review_level,
o.ldap_alias,
p.priority

FROM PROPOSAL p,
OWNER o,
(SELECT proposal_id, routing_id, segment_id_one,
segment_id_two,review_channel_list_id FROM ROUTING WHERE routing_id IN
(SELECT MAX (routing_id) FROM ROUTING GROUP BY proposal_id)) r
 WHERE p.status IN (1, 2) AND p.owner_id = o.owner_id(+)

       AND r.proposal_id = p.proposal_id

• EXPLAIN PLAN (EXP1) == Operation Object Name SELECT STATEMENT Optimizer Mode=CHOOSE NESTED LOOPS OUTER NESTED LOOPS HASH JOIN SEMI INDEX FAST FULL SCAN MARINER.IDX_ROUTING_MULTI1 VIEW SYS.VW_NSO_1 SORT GROUP BY INDEX FULL SCAN MARINER.IDX_ROUTING_MULTI2 TABLE ACCESS BY INDEX ROWID MARINER.PROPOSAL INDEX UNIQUE SCAN MARINER.SYS_C004829 TABLE ACCESS BY INDEX ROWID MARINER.OWNER INDEX UNIQUE SCAN MARINER.OWNER_PK11090439533640

  ---

• EXPLAIN (EXP2) == SELECT STATEMENT Optimizer Mode=CHOOSE SORT ORDER BY NESTED LOOPS OUTER NESTED LOOPS HASH JOIN SEMI INDEX FAST FULL SCAN MARINER.IDX_ROUTING_MULTI1 VIEW SYS.VW_NSO_1 SORT GROUP BY INDEX FULL SCAN MARINER.IDX_ROUTING_MULTI2 TABLE ACCESS BY INDEX ROWID MARINER.PROPOSAL INDEX UNIQUE SCAN MARINER.SYS_C004829 TABLE ACCESS BY INDEX ROWID MARINER.OWNER INDEX UNIQUE SCAN MARINER.OWNER_PK11090439533640

  ---

Received on Fri Mar 18 2005 - 07:14:53 CET