Database Tuning (MOSC)

MOSC Banner

Complete different plans (and execution) for very similar queries

edited Oct 21, 2011 3:51AM in Database Tuning (MOSC) 16 commentsAnswered
HelloI have been struggling to understand the behaviour of a query which is very slow (40 sec) but becomes really fast if I slightly modify it:

Original version

select *
from
( select this_.id as y0_, this_.UPDATEDDATE as y1_
from Document this_ inner join DOCUMENT_STATUS status1_ on this_.STATUS_ID=status1_.id
left outer join DOCUMENT_TYPE type2_ on this_.TYPE_ID=type2_.id
where
this_.VERSION = (select max(doc2_.VERSION) as y0_
from Document doc2_ where doc2_.PROJECT_ID=this_.PROJECT_ID and doc2_.REFERENCE=this_.REFERENCE )
and
 this_.PROJECT_ID=203
and
        (   this_.CREATEDBY=664
            or
            exists
            (select doc_folder.document_id from DOCUMENT_FOLDER doc_folder

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center