Re: Oracle performance

From: Mark Bowler <m.bowler_at_ukonline.co.uk>
Date: 27 Apr 2001 20:15:31 +0100
Message-ID: <3AE9D363.MD-1.4.4.m.bowler@ukonline.co.uk>

> Andrew,
>
> thanks for your quick response. I analyzed my statement and found the
> mistake.
> The problem was that " (select .... from t1 ....where ..)" should read
> "select ....from t1,t2...where..."). I do not know much about Oracles
> internals, but it seems that the missing table t2 in the 'from' clause
> causes a lot of overhead. Much more than on MSSQL or Access.
> Now it takes less than 1 sec. to produce the result.

I'm not 100% certain about this, but I've been told that by joining the sub-select to the outer select (where t2.id = t1.id) basically renders the indexes useless and the optimiser decides to perform full table scans instead. Does anyone know if there is any truth to this? Or have I been fed a pack of lies?

Thanks,

Mark Bowler Received on Fri Apr 27 2001 - 14:15:31 CDT