| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> ORDER BY and impact on query performance
Oracle 8i & 9i on Solaris 2.6 and 9 respectively. Sun SPARC hardware.
Having got my hints sorted out (thanks guys) I ended up with this query:
select
/*+ index(wc wd_candidates_pk) index(wwp wd_web_pages_pk) index(wct
wd_cand_track_n8)*/
last_name||', '||first_name name, wct.track_date, wc.candidate_id, headline, wwp.web_page_id,wtt2.user_track_type status,wct2.track_date status_date, wct.form_id, wct.response_data_id,
wct.track_id
from wd_candidates wc,
wd_live_candidates wlc,
wd_web_pages wwp,
wd_cand_track wct,
wd_track_types wtt,
wd_users wu,
wd_cand_track wct2,
wd_track_types wtt2
where wlc.web_site_id = 204
and wlc.candidate_id = wc.candidate_id
and wc.candidate_id = wct.candidate_id
and wct.applied_web_page_id = wwp.web_page_id
and wct.track_type_id = wtt.track_type_id
AND wtt.system_track_type = 'APPLIC'
and wlc.web_page_id = wwp.web_page_id
and wu.username = user
and wc.candidate_id = wct2.candidate_id
and wct2.track_id =
(select max(track_id)
from wd_cand_track
where candidate_id = wc.candidate_id
and web_page_id = wwp.web_page_id)
and wct2.track_type_id = wtt2.track_type_id
and (wwp.owner_user_id = wu.user_id
or
wwp.owner_user_id in (select user_id_grantor
from wd_user_grants
where user_id_grantee = wu.user_id
and nvl(grant_active_flag,'N') = 'Y')
)
Now this query will run in approx 1 second - this is good (as it was well over a minute before). However, if I add ORDER BY (anything) as the last line, the query time goes up to 9 seconds.
The result set was 257 rows. I am not sure when the sorting is done but an 8 second overhead for the sort of 257 rows is clearly not too good!
I guess there must be a trick to make the ORDER BY not make such an impact - any clues?
I have also pasted the explain plans below should they relevant (which I guess they are) - the fist is without the ORDER BY, second is ORDER BY 1.
NO ORDER BY
SELECT STATEMENT Optimizer=CHOOSE (Cost=278 Card=1 Bytes=177)
FILTER
NESTED LOOPS (Cost=278 Card=1 Bytes=177)
NESTED LOOPS (Cost=277 Card=1 Bytes=154)
NESTED LOOPS (Cost=276 Card=1 Bytes=141)
NESTED LOOPS (Cost=274 Card=1 Bytes=111)
NESTED LOOPS (Cost=254 Card=20 Bytes=1780)
HASH JOIN (Cost=234 Card=20 Bytes=1420)
NESTED LOOPS (Cost=229 Card=179 Bytes=10203)
TABLE ACCESS (BY INDEX ROWID) OF WD_USERS (Cost=2
Card=1 Bytes=22)
INDEX (UNIQUE SCAN) OF WD_USERS_U1 (UNIQUE) (Cost=1
Card=1)
TABLE ACCESS (BY INDEX ROWID) OF WD_WEB_PAGES (Cost=
227 Card=179 Bytes=6265)
INDEX (FULL SCAN) OF WD_WEB_PAGES_PK (UNIQUE) (Cost=
9 Card=3483)
TABLE ACCESS (BY INDEX ROWID) OF WD_LIVE_CANDIDATES
(Cost=4 Card=380 Bytes=5320)
INDEX (RANGE SCAN) OF WD_LIVE_CANDIDATES_N1 (NON-
UNIQUE) (Cost=2 Card=380)
TABLE ACCESS (BY INDEX ROWID) OF WD_CANDIDATES (Cost=1
Card=1 Bytes=18)
INDEX (UNIQUE SCAN) OF WD_CANDIDATES_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK (Cost=1 Card=
1 Bytes=22)
INDEX (UNIQUE SCAN) OF WD_CAND_TRACK_PK (UNIQUE)
SORT (AGGREGATE)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK (Cost=3
Card=1 Bytes=13)
INDEX (RANGE SCAN) OF WD_CAND_TRACK_N1 (NON-UNIQUE)
(Cost=2 Card=2)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK (Cost=2 Card=1
Bytes=30)
INDEX (RANGE SCAN) OF WD_CAND_TRACK_N8 (NON-UNIQUE) (Cost=1
Card=1)
TABLE ACCESS (BY INDEX ROWID) OF WD_TRACK_TYPES (Cost=1 Card=1
Bytes=13)
INDEX (UNIQUE SCAN) OF WD_TRACK_TYPES_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF WD_TRACK_TYPES (Cost=1 Card=1
Bytes=23)
INDEX (UNIQUE SCAN) OF WD_TRACK_TYPES_PK (UNIQUE)
TABLE ACCESS (FULL) OF WD_USER_GRANTS (Cost=2 Card=1 Bytes=10)
"WITH ORDER BY 1"
SELECT STATEMENT Optimizer=CHOOSE (Cost=280 Card=1 Bytes=177) SORT (ORDER BY) (Cost=280 Card=1 Bytes=177) FILTER
NESTED LOOPS (Cost=278 Card=1 Bytes=177)
NESTED LOOPS (Cost=277 Card=1 Bytes=154)
NESTED LOOPS (Cost=276 Card=1 Bytes=141)
NESTED LOOPS (Cost=274 Card=1 Bytes=111)
NESTED LOOPS (Cost=254 Card=20 Bytes=1780)
HASH JOIN (Cost=234 Card=20 Bytes=1420)
NESTED LOOPS (Cost=229 Card=179 Bytes=10203)
TABLE ACCESS (BY INDEX ROWID) OF WD_USERS (Cost=2
Card=1 Bytes=22)
INDEX (UNIQUE SCAN) OF WD_USERS_U1 (UNIQUE) (Cost=
1 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF WD_WEB_PAGES (Cost=
227 Card=179 Bytes=6265)
INDEX (FULL SCAN) OF WD_WEB_PAGES_PK (UNIQUE)
(Cost=9 Card=3483)
TABLE ACCESS (BY INDEX ROWID) OF WD_LIVE_CANDIDATES
(Cost=4 Card=380 Bytes=5320)
INDEX (RANGE SCAN) OF WD_LIVE_CANDIDATES_N1 (NON-
UNIQUE) (Cost=2 Card=380)
TABLE ACCESS (BY INDEX ROWID) OF WD_CANDIDATES (Cost=1
Card=1 Bytes=18)
INDEX (UNIQUE SCAN) OF WD_CANDIDATES_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK (Cost=1
Card=1 Bytes=22)
INDEX (UNIQUE SCAN) OF WD_CAND_TRACK_PK (UNIQUE)
SORT (AGGREGATE)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK
(Cost=3 Card=1 Bytes=13)
INDEX (RANGE SCAN) OF WD_CAND_TRACK_N1 (NON-
UNIQUE) (Cost=2 Card=2)
TABLE ACCESS (BY INDEX ROWID) OF WD_CAND_TRACK (Cost=2 Card=
1 Bytes=30)
INDEX (RANGE SCAN) OF WD_CAND_TRACK_N8 (NON-UNIQUE) (Cost=
1 Card=1)
TABLE ACCESS (BY INDEX ROWID) OF WD_TRACK_TYPES (Cost=1 Card=1
Bytes=13)
INDEX (UNIQUE SCAN) OF WD_TRACK_TYPES_PK (UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF WD_TRACK_TYPES (Cost=1 Card=1
Bytes=23)
INDEX (UNIQUE SCAN) OF WD_TRACK_TYPES_PK (UNIQUE)
TABLE ACCESS (FULL) OF WD_USER_GRANTS (Cost=2 Card=1 Bytes=10)
Data volumes:
wd_candidates 30,000
wd_cand_track 59,000
wd_live_candidates 8,300
wd_web_pages 4,800
-- jeremyReceived on Fri Sep 10 2004 - 09:24:11 CDT
 |
 |