1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

General problem in sql task

Discussion in 'SQL PL/SQL' started by Tobi, Sep 23, 2010.

  1. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    hi there,

    I'm defining a sql-statement as follows:
    Code (SQL):
    SELECT a_number, a_name FROM Tablename WHERE a_name NOT IN (SELECT a_name FROM Tablename2 WHERE Conditions...)
    So the subquery defines a set of numbers which elements should not appear in the result of the main-sql-select.

    This statement does its jobs, but to be honest very slowly (duration is up to 12 seconds!!).
    How can I increase the performance with some other sql statements?

    (We'are using Oracle 9i)

    Thanks, Tobi
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    NOT IN with a sub query can be slow if your subquery is resulting a large no. of records. It's very difficult to suggest any thing from what you have written. I suggest to rewrite the query using joins if you can. Else, give us the table structures and your entire query for us to suggest something.

    Cheers!
     
  3. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for your answer.

    My subquery results at least 80 datarecords. Here is the full code of this subquery:
    Code (SQL):

         
          SELECT
            pfak.rueckmelde_nr
          FROM pfak
            CONNECT BY PRIOR pfak.rueckmelde_nr = pfak.basis_rueckmelde_nr
            START WITH pfak.rueckmelde_nr=10056940  

     
    And these numbers should be filtered out of my whole resultset.
    Can you give me any advises how I can reproduce it with JOINs?
     
  4. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    You could also use
    Code (SQL):
    NOT EXISTS  
    instead of
    Code (SQL):
    NOT IN
    if Tablename2 has many records.
     
  5. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Sorry, I can not use your suggestion instead.
    I also tried NOT EXISTS, but, unfortunately, I got another resultset. So I don't think that NOT EXISTS is a pendant to NOT IN.

    Tobi
     
  6. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    can u post your sql using NOT EXISTS. I think there is something missing in it. NOT EXISTS works faster than NOT IN in large tables.
     
  7. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Yes, of course. Here it is:

    Code (SQL):


    SELECT
      pfsp.rueckmelde_nr,
      pfsp.rueckmelde_positions_nr,
      pfsp.komponenten_artikel_nr,
      pfak.auftragskennwort,
      (
        CASE
          WHEN pfsp.bestell_nr = '0' THEN '0'
          ELSE CONCAT('BE-', to_char(pfsp.bestell_nr))
        END
      ) bestell_nr,
      pfsp.komponenten_menge,
      pfsp.entnahme_menge_ist,
      (pfsp.komponenten_menge - pfsp.entnahme_menge_ist) Differenz,
      pekp.bestellpositions_status,
      (
        CASE
          WHEN pekp.bestellpositions_status = 0 THEN 'erfasst'
          WHEN pekp.bestellpositions_status = 1 THEN 'bestÃĪtigt'
          WHEN pekp.bestellpositions_status = 2 THEN 'Teil/Vollrechnung o. Lief.'
          WHEN pekp.bestellpositions_status = 3 THEN 'Teillieferung o. Rechnung'
        END
      ) bestellpositions_status_2
    FROM pfsp
      INNER JOIN pfak
        ON pfak.rueckmelde_nr = pfsp.rueckmelde_nr
      INNER JOIN pekp
        ON pfsp.bestell_nr = pekp.vorgangs_nr AND
           pfsp.bestell_pos_nr = pekp.vorgangs_pos_nr
      WHERE
        NOT EXISTS
        (
          SELECT
            pfak.rueckmelde_nr
          FROM pfak
            CONNECT BY PRIOR pfak.rueckmelde_nr = pfak.basis_rueckmelde_nr
            START WITH pfak.rueckmelde_nr=10056940          
        ) AND
        (
          pfsp.rueckmelde_positions_nr >= 9800 AND
          pfsp.rueckmelde_positions_nr <= 9999
        ) AND
        pfsp.rueckmelde_status <= 3

     
     
  8. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    I believe you missed the where condition. Your NOT EXISTS should be as below:
    Code (SQL):

        NOT EXISTS
        (
          SELECT
            pfak.rueckmelde_nr
          FROM pfak p
          WHERE p.rueckmelde_nr = pfak.rueckmelde_nr
            CONNECT BY PRIOR pfak.rueckmelde_nr = pfak.basis_rueckmelde_nr
            START WITH pfak.rueckmelde_nr=10056940          
        )
     
  9. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank you.
    I added the missing line and finally I got the same resultset as already meantioned. Great!

    But it is only useful if the time of execution is rather low.
    The duration is still the same (up to 14 seconds!).
    How can I tune this up a little?

    Thanks, Tobi
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without seeing the execution plan Oracle has produced no one can say how to 'speed this up'. Before you run the query type this at the SQL> prompt:

    set autotrace on

    Run you query then post the plan displayed after the data is returned. Seeing that information will help diagnose why this query is , in your terms, slow.
     
  11. Tobi

    Tobi Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for this tip.
    Here is the execution plan:

    Code (SQL):

    -----------------------------------------------------------------------------------
    | Id  | Operation                     |  Name             | ROWS  | Bytes | Cost  |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                   |       |       |       |
    |   1 |  FILTER                       |                   |       |       |       |
    |   2 |   NESTED LOOPS                |                   |       |       |       |
    |   3 |    MERGE JOIN                 |                   |       |       |       |
    |   4 |     SORT JOIN                 |                   |       |       |       |
    |   5 |      TABLE ACCESS FULL        | PEKP              |       |       |       |
    |   6 |     SORT JOIN                 |                   |       |       |       |
    |   7 |      TABLE ACCESS FULL        | PFSP              |       |       |       |
    |   8 |    TABLE ACCESS BY INDEX ROWID| PFAK              |       |       |       |
    |   9 |     INDEX UNIQUE SCAN         | PFAK_PRIMARY_KEY  |       |       |       |
    |  10 |   FILTER                      |                   |       |       |       |
    |  11 |    CONNECT BY WITH FILTERING  |                   |       |       |       |
    |  12 |     FILTER                    |                   |       |       |       |
    |  13 |      TABLE ACCESS FULL        | PFAK              |       |       |       |
    |  14 |     NESTED LOOPS              |                   |       |       |       |
    |  15 |      BUFFER SORT              |                   |       |       |       |
    |  16 |       CONNECT BY PUMP         |                   |       |       |       |
    |  17 |      FILTER                   |                   |       |       |       |
    |  18 |       TABLE ACCESS FULL       | PFAK              |       |       |       |
    |  19 |     TABLE ACCESS FULL         | PFAK              |       |       |       |
    -----------------------------------------------------------------------------------
     
     
  12. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    In addition to the last post,
    please also post your oracle version, index information about the tabels, along with explain plan.
     
  13. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    An index for PFAK on rueckmelde_nr and basis_rueckmelde_nr may speed up that connect by query, and it may also help on the table join between PFAK and PFSP. And an index on PFSP on rueckmelde_nr could speed that join further. Remember that, with the exception of bitmap indexes, Oracle will select the best index (in its opinion) for a table. You may also want to investigate an index on PFSP on bestell_nr and bestell_pos_nr as well as a similar index on PEKP (vorgangs_nr, vorgangs_pos_nr) to help with that join.