> > On 4 Jul, 15:26, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > > On Jul 4, 8:47 am, colmkav <colmj...@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 -