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

javascript - Laravel datatable invalid JSON response -

-
everything work fine on code below fetch user logs: js: <script> $(document).ready(function () { var table =$('#systemlogs').datatable({ responsive: true, processing: true, serverside: true, "language": { "url": "/datatables/media/plug-in/persian.json" }, ajax: '{!! url('/admin/systemlogs/data/systemlogsdatatable') !!}', columns: [ { data: 'name', name: 'name' }, { data: 'message_text', name: 'message_text' }, { data: 'remote_addr', name: 'remote_addr' }, { data: 'log_created_at', name: 'management_logs.created_at' }, { data: 'log_updated_at', name: 'management_logs.updated_at' }, ] }); }); </script> routes: route::get('/systemlogs/data/systemlogsdatatable','systemlogscontrolle...
Read more

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

-
i getting error when run spring application: exception in thread "main" org.springframework.context.applicationcontextexception: unable start embedded container; nested exception org.springframework.context.applicationcontextexception: unable start embeddedwebapplicationcontext due missing embeddedservletcontainerfactory bean. @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.onrefresh(embeddedwebapplicationcontext.java:124) @ org.springframework.context.support.abstractapplicationcontext.refresh(abstractapplicationcontext.java:476) @ org.springframework.boot.context.embedded.embeddedwebapplicationcontext.refresh(embeddedwebapplicationcontext.java:109) @ org.springframework.boot.springapplication.refresh(springapplication.java:691) @ org.springframework.boot.springapplication.run(springapplication.java:320) @ org.springframework.boot.springapplication.run(springapplication.java:952) @ org.springframework.boot.springa...
Read more

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -

-
msg 245, level 16, state 1, line 1 conversion failed when converting varchar value '8:45 am' data type int. script declare @specimenid bigint = 0, @script varchar(max); declare @starttime int = (select convert(int,substring(start_time,0,charindex(':', start_time)) - 12) appt appt._id=112601) --select @starttime set @script = 'select distinct ptlast_name + '', '' + pt.first_name patient_name, dbo.formatdate(pt.dob,''mm/dd/yyyy'') dob, (convert(varchar, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end)) + ''/'' + convert(varchar, (datediff(m, dateadd(yy, (datediff(yy, dob, appt_date) - case when (month(dob) > month(appt_date)) or (month(dob) = month(appt_date) , day(dob) > day(appt_date)) 1 else 0 end), d...
Read more