Results 1 to 5 of 5

Thread: Slow SQL

  1. #1
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41

    Unhappy Unanswered: Slow SQL

    Hi ppl

    I have a problem with a slow SQL statement.
    I have 2 tables that I want to select data from.

    Table "A" has 9000 Records
    Table "B" has 8750
    These 2 tables are linked by there "ID" Fields

    This SQL statement takes bout 1-2 mins to run. All I want is the records from "A" that doesnt exist in "B".

    my SQL looks like this
    "SELECT A.TITLE FROM A WHERE A.ID NOT IN (SELECT B.ID FROM B)"

    Any Help Please. The tables are indexed properly and everthing. Is theere a better way of searching?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    this may or may not use a better execution plan dependent on your stats (make sure both tables are analyzed recently) and your oracle db version

    SELECT A.TITLE FROM A, B WHERE A.ID = B.ID(+) AND B.ID IS NULL

    Using in and not in is quite inefficient if you have a large set of numbers in the list though later version of oracle are much better at dealing with this. Anyway I always try to use join or outer join instead if possible

    Alan

  3. #3
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    41
    Originally posted by AlanP
    SELECT A.TITLE FROM A, B WHERE A.ID = B.ID(+) AND B.ID IS NULL
    It worked like a bomb. Takes like 3 seconds and gets all the records.

    Thanks

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Use the link for writing efficient SQLs

    Use this link as a starting point and paste it on your desktop.

    http://www.wsl.ch/relics/rauminf/riv...inalondon.html

    Thanx and Regards
    Aruneesh

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You might also try NOT EXISTS

    select A.TITLE FROM A WHERE NOT EXISTS (SELECT 'X' FROM B
    WHERE A.ID = B.ID AND B.ID IS NULL);

    Gregg

Posting Permissions

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