tag:blogger.com,1999:blog-12459003.post2722425966690118081..comments2023-08-03T09:19:28.438-04:00Comments on So What Co-operative: What is the optimizer doing?Jeff Hunterhttp://www.blogger.com/profile/15701602600662849251noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-12459003.post-17107593091581344032006-12-21T10:30:00.000-05:002006-12-21T10:30:00.000-05:00I'm glad I don't bet with you. Indeed, it was a co...I'm glad I don't bet with you. Indeed, it was a constraint on date_matched that was causing this slowdown:<br />CONSTRAINT_NAME C SEARCH_CONDITION<br />------------------------------ - ------------------------------------------------------------<br />ACTIVITY_DATE_MATCHED C date_matched = TRUNC( date_matched )<br /><br />SQL> select count(*) from activity_rtab where date_matched = '31-dec-4712';<Jeff Hunterhttps://www.blogger.com/profile/15701602600662849251noreply@blogger.comtag:blogger.com,1999:blog-12459003.post-37713016841840686862006-12-21T02:38:00.000-05:002006-12-21T02:38:00.000-05:00Jeff,
I'll take a bet that your production system ...Jeff,<br />I'll take a bet that your production system has a constraint on that column:<br /> date_matched = trunc(date_matched).<br /><br />This is extracted by the optimizer and used as a predicate in the query, and (incorrectly) introduces a 1% factor to the selectivity (hence 9876 -> 99).<br /><br />BUT - the predicate gets checked for every row, and checking date = trunc(date) 355,000 timesAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-12459003.post-16559011647461646242006-12-16T19:39:00.000-05:002006-12-16T19:39:00.000-05:00Just some idle curiosity - did you run the num_dis...Just some idle curiosity - did you run the num_distinct query on p. 42 of Jonathan's book for your two tables?Anonymousnoreply@blogger.com