| urgent ...how to make this simple query run faster [message #19985] |
Mon, 22 April 2002 16:24  |
ksr
Messages: 112
Registered: January 2002
|
Senior Member |
|
|
Hi,
How to reduce the execution time of this query
SELECT distinct trelCustomerMsgType.fiCustomer
FROM trelCustomerMsgType, trelNewsNotificationChannel,tfixMsgType
WHERE trelCustomerMsgType.fiMsgType = tfixMsgType.idMsgType
AND tfixMsgType.fiNotificationChannel = trelNewsNotificationChannel.fiNotificationChannel
AND trelCustomerMsgType.dbRegistered > 0
AND trelNewsNotificationChannel.fiNews = 37272
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=155 Card=33433 Bytes
=601794)
1 0 SORT (UNIQUE) (Cost=155 Card=33433 Bytes=601794)
2 1 HASH JOIN (Cost=17 Card=35187 Bytes=633366)
3 2 NESTED LOOPS (Cost=2 Card=2 Bytes=20)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'TRELNEWSNOTIFICATI
ONCHANNEL' (Cost=1 Card=2 Bytes=12)
5 4 INDEX (RANGE SCAN) OF 'IX_NEWS_NOTIFCHANNEL_NEWS'
(NON-UNIQUE) (Cost=1 Card=2)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'TFIXMSGTYPE' (Cost
=1 Card=3 Bytes=12)
7 6 INDEX (RANGE SCAN) OF 'IX_MSGTYPE_NOTIFICATIONCHAN
NEL' (NON-UNIQUE)
8 2 TABLE ACCESS (FULL) OF 'TRELCUSTOMERMSGTYPE' (Cost=12
Card=35187 Bytes=281496)
Statistics
----------------------------------------------------------
8 recursive calls
11 db block gets
200 consistent gets
174 physical reads
0 redo size
655925 bytes sent via SQL*Net to client
267550 bytes received via SQL*Net from client
3875 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
58098 rows processed
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
