tag:blogger.com,1999:blog-2954359812249072053.post3154886831994409185..comments2023-12-15T23:33:59.034-06:00Comments on Cary Millsap: Messed-Up App of the DayCary Millsaphttp://www.blogger.com/profile/16697498718050285274noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2954359812249072053.post-88366885934238087772009-03-06T16:44:00.000-06:002009-03-06T16:44:00.000-06:00Well, I'm frustrated at analytic functions. Fr...Well, I'm frustrated at analytic functions. From SQL Reference 10g Release 2 (B14200-02) and copied in SQL Language Reference 11g Release 1 (B28286-01):<BR/><BR/>"Analytic functions are the last set of operations performed in a query except for the<BR/>final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are<BR/>completed before the analytic functions are processed. Therefore, analytic functions<BR/>can appear only in the select list or ORDER BY clause."<BR/><BR/>I am here to attest to the apparent truthfullness in that quote. The 11.1.0.6 software apparently implements analytic functions that way.<BR/><BR/>The limit feature in MySQL seems far superior to the techniques found in Oracle. I say 'techniques' because Oracle provides some limit-ability via the index_asc and index_desc hints. The problem--and it's not a common one--is that if someone uses the technique and then the index is dropped or renamed or redefined with the "wrong" columns, then you're left with a query that almost certainly gives the wrong answer. But, as I said, it's a rare problem.<BR/><BR/>So, given an appropriate index, here's a query that can satisfy the query with probably less than 10 consistent reads (a.k.a. LIOs).<BR/><BR/>select to_char(departing, :dtfmt) from<BR/>(<BR/> select * from<BR/> (<BR/> select /*+ index_asc(departure departure_n1) */ *<BR/> from departure<BR/> where departing >= to_date(:my_time, :dtfmt)<BR/> and (orig,dest) = (:orig,:dest)<BR/> and rownum <= 5<BR/> union<BR/> select /*+ index_desc(departure departure_n1) */ *<BR/> from departure<BR/> where departing < to_date(:my_time, :dtfmt)<BR/> and (orig,dest) = (:orig,:dest)<BR/> and rownum <= 5<BR/> )<BR/> order by abs(departing - to_date(:my_time, :dtfmt))<BR/>)<BR/>where rownum < 5<BR/><BR/>Here's the trace data its execution generated.<BR/><BR/>PARSE #4:c=0,e=632,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1236372631765393<BR/>EXEC #4:c=0,e=367,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1236372631776514<BR/>FETCH #4:c=0,e=440,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,tim=1236372631777292<BR/>FETCH #4:c=4000,e=4928,p=0,cr=0,cu=0,mis=0,r=3,dep=0,og=1,tim=1236372631783013<BR/>STAT #4 id=1 cnt=4 pid=0 pos=1 obj=0 op='COUNT STOPKEY (cr=5 pr=0 pw=0 time=0 us)'<BR/>STAT #4 id=2 cnt=4 pid=1 pos=1 obj=0 op='VIEW (cr=5 pr=0 pw=0 time=0 us cost=378 size=90 card=10)'<BR/>STAT #4 id=3 cnt=4 pid=2 pos=1 obj=0 op='SORT ORDER BY STOPKEY (cr=5 pr=0 pw=0 time=0 us cost=378 size=90 card=10)'<BR/>STAT #4 id=4 cnt=10 pid=3 pos=1 obj=0 op='VIEW (cr=5 pr=0 pw=0 time=17 us cost=377 size=90 card=10)'<BR/>STAT #4 id=5 cnt=10 pid=4 pos=1 obj=0 op='SORT UNIQUE (cr=5 pr=0 pw=0 time=6 us cost=377 size=90 card=10)'<BR/>STAT #4 id=6 cnt=10 pid=5 pos=1 obj=0 op='UNION-ALL (cr=5 pr=0 pw=0 time=36 us)'<BR/>STAT #4 id=7 cnt=5 pid=6 pos=1 obj=0 op='COUNT STOPKEY (cr=3 pr=0 pw=0 time=16 us)'<BR/>STAT #4 id=8 cnt=5 pid=7 pos=1 obj=84410 op='INDEX RANGE SCAN DEPARTURE_N1 (cr=3 pr=0 pw=0 time=6 us cost=163 size=488619 card=54291)'<BR/>STAT #4 id=9 cnt=5 pid=6 pos=2 obj=0 op='COUNT STOPKEY (cr=2 pr=0 pw=0 time=16 us)'<BR/>STAT #4 id=10 cnt=5 pid=9 pos=1 obj=84410 op='INDEX RANGE SCAN DESCENDING DEPARTURE_N1 (cr=2 pr=0 pw=0 time=6 us cost=2 size=2952 card=328)'Jeffhttps://www.blogger.com/profile/02365994762779966623noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-55329672005095393242008-04-11T04:35:00.000-05:002008-04-11T04:35:00.000-05:00even the http://www.yorkshiretravel.net site for l...even the http://www.yorkshiretravel.net site for local bus service shows only next 3 bus times as result and then if the user click more button, it adds further 3 bus times, click more again adds 3 more bus times.<BR/><BR/>it not using AJAX but still the concept is more useful as not many user want to go beyond next 6 buses.bloggerhttps://www.blogger.com/profile/13190451796777918609noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-13739356074765553622008-04-07T20:34:00.000-05:002008-04-07T20:34:00.000-05:00See, that's why I prefer Amsterdam :-)See, that's why I prefer Amsterdam :-)jan van mourikhttps://www.blogger.com/profile/01002917626652330373noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-51409638632818733552008-04-07T14:59:00.000-05:002008-04-07T14:59:00.000-05:00Well I'm impressed. You managed to make it through...Well I'm impressed. You managed to make it through Heathrow Terminal 5 and get out the same day and the worst thing you came across was the National Express booking system. £4 billion to create luggage mountian, makes me proud to be British. My guess is that the developer liked seeing the whizzy number gadget counting up the records. It's no fun just seeing it count up to 10, make it count up to 2000 ... genius.Unknownhttps://www.blogger.com/profile/10758192805008827166noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-64373625729518162132008-04-07T04:52:00.000-05:002008-04-07T04:52:00.000-05:00If we could only get a quarter or a dime of every ...If we could only get a quarter or a dime of every application / UI out there... displaying this behavior; I guess we then would be rich men...Marco Gralikehttps://www.blogger.com/profile/12810709424310804316noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-40257164543844338582008-04-07T04:47:00.000-05:002008-04-07T04:47:00.000-05:00This comment has been removed by the author.Marco Gralikehttps://www.blogger.com/profile/12810709424310804316noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-8357583946834151472008-04-06T22:03:00.000-05:002008-04-06T22:03:00.000-05:00When I was in London back in May, I took the tubes...When I was in London back in May, I took the tubes to Victoria Street Station and then got on the Gatwick Express.Tomhttps://www.blogger.com/profile/09600876443323337792noreply@blogger.comtag:blogger.com,1999:blog-2954359812249072053.post-62363983062849957742008-04-06T10:02:00.000-05:002008-04-06T10:02:00.000-05:00Hmmm...The fact that there was a counter fed back ...Hmmm...<BR/>The fact that there was a counter fed back to the UI, strongly suggests to me that there was some Object Query Language processing going on in the 'mid-tier', scanning EJB's or whatever they are called this week. Probably zero database-involvement for you at that moment...<BR/>Brilliant indeed.Toon Koppelaarshttps://www.blogger.com/profile/18032910509106315233noreply@blogger.com