The cardinalities of the joins ------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------|* 1 | FILTER | | 1 | | 21489 |00:04:00.06 | 148K| 13345 | | 2 | NESTED LOOPS | | 1 | 218 | 21489 |00:04:00.04 | 148K| 13345 | | 3 | NESTED LOOPS OUTER | | 1 | 218 | 21489 |00:02:48.10 | 82575 | 9516 | | 4 | NESTED LOOPS | | 1 | 218 | 21489 |00:02:47.88 | 79708 | 9516 |
are off by a factor of about 100 ( 218 estimated vs 21489 actual ). Check if the join criteria between SC and SH are correlated.
At 04:34 AM 3/6/2009, hrishy wrote:
Hi BhavikThanks for quick response,and correcting my understanding on the starts column.Having understood the starts column and the cardinality calculation my question then would be why oracle is not using Hash Join and prefers a Nested Loop join It takes almost 8 minutes to complete the query with nested loops.If i use a use_hash hint it completes in 2 minutesregards Hrishy -- //www.freelists.org/webpage/oracle-l
Regards Wolfgang Breitling Centrex Consulting Corporationwww.centrexcc.com
-- //www.freelists.org/webpage/oracle-l