colmkav 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)
Did you try it? What happened?
For examples of similar queries go to Morgan's Library at www.psoug.org and scroll down to SELECT statements. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
On Jul 4, 2:47 pm, 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)
the correct syntax is select count(*) from tmp_risk_sum where (book, strategy, exposuretypeid) not in (select book, strategy, exposuretypeid from tmp_risk_sum_ORACLE)
Note the extra ()
Syntax errors are preferably resolved by using the documentation.
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.
> On Jul 4, 2:47 pm, 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)
> the correct syntax is > select count(*) > from tmp_risk_sum > where (book, strategy, exposuretypeid) > not in (select book, strategy, exposuretypeid from > tmp_risk_sum_ORACLE)
> Note the extra ()
> Syntax errors are preferably resolved by using the documentation.
> 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
> 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.
> On Jul 4, 10:27 am, colmkav <colmj...@yahoo.co.uk> wrote:
> > 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)
> > > 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.
