sql - Why does oracle optimiser treat join by JOIN and WHERE differently? -

-

i have query on used query optimiser:

  select res.studentid,         res.examid,         r.percentcorrect,         max(attempt) attempt    tbl res    join (select studentid,               examid,               max(percentcorrect) percentcorrect          tbl         group studentid, examid) r    on r.studentid = res.studentid       , r.examid = res.examid       , r.percentcorrect = res.percentcorrect   group res.studentid, res.examid, r.percentcorrect   order res.examid

what surprised me optimiser returned following on 40% faster:

select /*+ no_cpu_costing */ res.studentid,         res.examid,         r.percentcorrect,         max(attempt) attempt    tbl res,         (select studentid,                 examid,                 max(percentcorrect) percentcorrect           tbl           group studentid, examid) r   r.studentid = res.studentid     , r.examid = res.examid     , r.percentcorrect = res.percentcorrect   group res.studentid, res.examid, r.percentcorrect   order res.examid

here execution plans both:

execution plans

how possible? thought optimiser treats join clause in optimised query...

from here:

in general should find cost of table scan increase when enable cpu costing (also known "system statistics"). means improved run time due changes in execution path have started favour execution plans. there few articles system statistics on blog might give more background, , couple of links there other relevant articles: http://jonathanlewis.wordpress.com/category/oracle/statistics/system-stats/

in other words, statistics might stale, since have "turned them off" query, avoid using inefficient path: hence (temporary?) improvement.


Comments

Post a Comment

Popular posts from this blog

wireshark - USB mapping with python -

-
while reading ctf write-ups came across script #!/usr/bin/env python import struct import image import dpkt init_x, init_y = 100, 400 def print_map(pcap, device): picture = image.new("rgb", (1200, 500), "white") pixels = picture.load() x, y = init_x, init_y ts, buf in pcap: device_id, = struct.unpack("b", buf[0x0b]) if device_id != device: continue data = struct.unpack("bbbb", buf[-4:]) status = data[0] x = x + data[1] y = y + data[2] if (status == 1): in range(-5, 5): j in range(-5, 5): pixels[x + , y + j] = (0, 0, 0, 0) else: pixels[x, y] = (255, 0, 0, 0) picture.save("riverside-map.png", "png") if __name__ == "__main__": f = open("usb.pcap", "rb") pcap = dpkt.pcap.reader(f) print_map(pcap, 5) f.close() and ...
Read more

c++ - nodejs socket.io closes connection before upgrading to websocket -

-
i'm trying write c++ client talk websocket server written using nodejs , socket.io. can't c++ client establish websocket connection; server drops connection right after client requests http connection upgraded websocket. i used tcpdump watch packets going , forth. when use websocket server browser, works great , tcpdump output shows packets going , forth. when try connect server using c++ client, websocket connection never gets established. output tcpdump shows upgrade request being sent server followed server dropping connection. i tried replicating sequence of events using nc . connected server (running on port 8080) , copy-pasted exact set of headers sent browser, captured tcpdump. duplicate of working headers, connection dropped. there no output on server end indicating why connection gets dropped. here headers sent: get /socket.io/?type=player&eio=3&transport=websocket&sid=7mdclht_8ctsd6f4aaac http/1.1 host: localhost:8080 connection: upgrade prag...
Read more

Deploying Qt Application on Android is really slow? -

-
as may know there 3 ways deploy qt application on android : use ministro service install qt deploy local qt libraries temporary directory bundle qt libraries in apk the first method takes 30 seconds , needs install apk . ministro . second takes 1 minute me ! , anytime try run program qt creator pushes qt libraries device. third 1 makes .apk file big , again takes 1 minute me. think situation that's not reasonable develop android application using qt. there way make deploying process faster? almost full year since op , things have not changed @ all. deployment of 7 mb apk takes on minute , half project compiles in 5 seconds. reason answering not problem got resolved, offer alternative solution. i've implemented "workaround" consisting of 2 applications work in tandem - 1 on pc , 1 on device - created compile files remotely, turned out faster alternative deployment well. on host create application launches compilation in separate process, when do...
Read more