Re: NOT IN

On 4 Jul, 18:04, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 4, 10:27 am, colmkav <colmj..._at_yahoo.co.uk> wrote:
>
>
>
>
>
> > On 4 Jul, 15:26, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > On Jul 4, 8:47 am, colmkav <colmj..._at_yahoo.co.uk> wrote:
>
> > > > is it possible to write an SQL Oracle query where you want to check
> > > > whether a combination of fields exist in another query
>
> > > > eg I tried the following but got an error:
>
> > > > select count(*) from tmp_risk_sum where book, strategy, exposuretypeid
> > > > not in (select book, strategy, exposuretypeid from tmp_risk_sum_ORACLE)
>
> > > SELECT
> > > COUNT(*)
> > > FROM
> > > TMP_RISK_SUM
> > > WHERE
> > > (BOOK,STRATEGY,EXPOSURETYPEID) NOT IN (
> > > SELECT
> > > BOOK,
> > > STRATEGY,
> > > EXPOSURETYPEID
> > > FROM
> > > TMP_RISK_SUM_ORACLE);
>
> > > You may find that this syntax executes more efficiently:
> > > SELECT
> > > COUNT(*)
> > > FROM
> > > TMP_RISK_SUM S,
> > > (SELECT DISTINCT
> > > BOOK,
> > > STRATEGY,
> > > EXPOSURETYPEID
> > > FROM
> > > TMP_RISK_SUM_ORACLE) SO
> > > WHERE
> > > S.BOOK=SO.BOOK(+)
> > > AND S.STRATEGY=SO.STRATEGY(+)
> > > AND S.EXPOSURETYPEID=SO.EXPOSURETYPEID(+)
> > > AND SO.BOOK IS NULL;
>
> > > Charles Hooper
> > > IT Manager/Oracle DBA
> > > K&M Machine-Fabricating, Inc.
>
> > Why does this work? Surely you would need a similar SO.STRATEGY IS
> > NULL a nd EXPOSURETYPEID IS NULL
>
> > Also why does this execute better
>
> The NOT IN syntax can be very inefficient. On Oracle 8.1.7.3, the NOT
> IN syntax for a particular SQL statement may require 5 minutes to
> execute, while the outer join syntax (the second approach that I
> provided) might execute in 15 seconds or less. Newer releases of
> Oracle may automatically perform the transformation from the NOT IN
> syntax to the outer join syntax - with the likely requirement that the
> columns be defined as NOT NULL.
>
> Why is the outer join typically more efficient? Assume that Oracle
> determines that a full tablescan of the TMP_RISK_SUM_ORACLE table is
> required to verify that a given combination of BOOK, STRATEGY, and
> EXPOSURETYPEID in a row in the TMP_RISK_SUM table does not exist in
> the TMP_RISK_SUM_ORACLE table. This full tablescan will be required
> for _each_ row in the TMP_RISK_SUM table. Such an operation, even if
> indexes are involved on the TMP_RISK_SUM_ORACLE table, could be both
> expensive and time consuming. The outer join syntax does not suffer
> the same performance penalty - Oracle could create a hash probe table,
> for example, for the results of the inline view. Odd things can
> happen if any of the columns involved may naturally contain NULL
> values.
>
> Why is it that we only need to specify that SO.BOOK IS NULL and not
> that also that SO.STRATEGY IS NULL and EXPOSURETYPEID IS NULL? It
> only takes one exception to make an ALWAYS rule false. If
> TMP_RISK_SUM_ORACLE.BOOK can never be naturally NULL, the only time
> when it would be NULL in this case is when Oracle is performing an
> outer join and the row does not exist in the TMP_RISK_SUM_ORACLE
> table.
>
> Just for the sake of curiosity, please post the output of the DBMS
> Xplan for the two methods using the 'ALLSTATS LAST' parameter as shown
> here:
> http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

thanks for this. What I meant regarding the NULLs is surely the condition should be something like

.....AND (SO.BOOK IS NULL OR SO.STRATEGY IS NULL OR SO.EXPOSURETYPE IS NULL) there could be cases where strategy isnt there but the book is. Received on Thu Jul 05 2007 - 06:31:40 CDT