tag:blogger.com,1999:blog-5124641802818980374.post8374209297685890859..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: Logical I/O - Evolution: Part 2 - 9i, 10g PrefetchingUnknownnoreply@blogger.comBlogger4125tag:blogger.com,1999:blog-5124641802818980374.post-79192594451979563672011-07-26T14:43:19.280+02:002011-07-26T14:43:19.280+02:00Jonathan,
I think the first time I came across a ...Jonathan,<br /><br />I think the first time I came across a description of this unique scan -> range scan conversion was when reading chapter 11 of your CBO - Fundamentals.<br /><br />Since I haven't been able to reproduce this during my tests I didn't mention it here since I wasn't sure if it is still reproducible in 10g.<br /><br />I'm not entirely sure about your comment - did you mean to post an execution plan from 10g showing such a case meaning that you could reproduce in 10g?<br /><br />If that was the case I would probably update the post.<br /><br />Note that 11g supports the "Prefetching" shape for the unique scan out of the box, so there no need for conversion any longer there.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-75780581399801700412011-07-26T00:24:55.336+02:002011-07-26T00:24:55.336+02:00Randolf,
I don't know if you've ever spott...Randolf,<br />I don't know if you've ever spotted this - but I have seen cases where Oracle switches between an index unique scan on the innner table to an index range scan so that it can take advantage of pre-fetching. <br /><br />This is the same query running against the same version of the database using small variations in the volume of data avaiable in the driving table.<br /><br />Unfortunately it seems to be very sensitive to historical behaviour, so I've never been able to produce a stable repeatable demonstration.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-26935857280936917332011-07-25T22:10:37.801+02:002011-07-25T22:10:37.801+02:00Flado,
thanks a lot for pointing out this anomaly...Flado,<br /><br />thanks a lot for pointing out this anomaly. I was so focused on the logical I/O part that I completely forgot to mention this important point.<br /><br />Rather than putting my reply here I've updated the post accordingly.<br /><br />Thanks,<br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-16906354959556969562011-07-25T15:17:25.611+02:002011-07-25T15:17:25.611+02:00Randolf,
I noticed a strange thing in both execut...Randolf,<br /><br />I noticed a strange thing in both execution plans using the non-unique index: The actual number of rows reported to flow out of the NESTED LOOPS step (ID 3) is 200K. I cannot wrap my head around what is this supposed to mean: that the join produces 200K ROWIDs, but half of them could not be found in the table? How can it be?<br />I'd appreciate it if you could throw some light.<br /><br />Cheers!<br />FladoFladohttps://www.blogger.com/profile/04901763101139511192noreply@blogger.com