Order By Performance Problem on View

[Scott]

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
================================================== ==============

[Mark.Powell]

I notice that the two plans appear to be the same expect for the second
plan having the sort operation. When you add an order by you force
Oracle to find all the data and sort it prior to returning any data to
the application. The first query can start returning data as soon as
it finds any even though the query is still running on the db.

So how are you measuring the run time? You should be measuring it for
the last row being returned, not the first to get the true difference.
The difference is the cost of the sort.

Tuning the query or eliminating the sort are your only performance
options. What is the hash area size set to? If less than what you
calculate for the size of the hash set then you could try to bump it
up. You could also force the use of nested loops instead of a hash and
see it that is better or worse.

That is the only general advice I can think of without really trying to
figure out your query but without more knowledge of the data, the
available indexes etc... I do not want to make any specific query
rewrite recommendations.

HTH -- Mark D Powell --