Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2003
    Posts
    50

    Unanswered: SQL: Replace OR-Operator with IN-Operator

    Hi

    I have the following sql:
    Select count(*) from ADU.UTADU_ADRESSE where (wvrd_wlgs, wvagentur) = (1,1) or (wvrd_wlgs, wvagentur) = (2,2)

    Can the OR-Operator be replaced with the IN-Operator for this query ?

    Marc

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    Which DB2 version supports this type of syntax?
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Jul 2003
    Posts
    50
    I work with udb 7.1 on Windows / Unix

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: SQL: Replace OR-Operator with IN-Operator

    You can do something like (or use with to generate "temp" table)

    Select count(*) from ADU.UTADU_ADRESSE where
    (wvrd_wlgs, wvagentur) in ( values (1,1), (2,2))

  5. #5
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Re: SQL: Replace OR-Operator with IN-Operator

    Originally posted by chuzhoi
    You can do something like (or use with to generate "temp" table)

    Select count(*) from ADU.UTADU_ADRESSE where
    (wvrd_wlgs, wvagentur) in ( values (1,1), (2,2))
    Could you please let us know in performance point of view, which one is better?

    Thanks

  6. #6
    Join Date
    Jul 2003
    Posts
    50

    Re: SQL: Replace OR-Operator with IN-Operator

    Performance:

    Test-Statements:
    - OR:
    Select count(*)
    from ADU.UTADU_ADRESSE
    where (wvrd_wlgs, wvagentur) = (94,640) or (wvrd_wlgs, wvagentur) = (88,137)

    IN:
    Select count(*)
    from ADU.UTADU_ADRESSE
    where (wvrd_wlgs, wvagentur) in ( values (94,640), (88,137))

    Table ADU.UTADU_ADRESSE : 5'281'216 records
    Index on columns: wvrd_wlgs, wvagentur

    --------------------------------------------------------------

    I explained these statements by visual explain and got:
    Costs:
    - OR: 58'000
    - IN: 150

    I issued the SQL-statement and got time in seconds:
    - OR: 10s
    - IN: 0,5s

    I knew that IN is better than OR, but I thought that UDB does SQL-Rewrite in this case, this difference really surprises me !!!
    (if I have only 1 column, then I get the same costs for OR and IN, but with 2 columns like above results a big difference)


    Marc

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would make sure you have the latest fixpak installed. If that does not fix the problem, I would change the query optimization for this query:
    SET CURRENT QUERY OPTIMIZATION 7 (the default is usually set to 5).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •