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!

Issue in SQL-Full table scan

Discussion in 'Oracle Apps Technical' started by chinky, Jun 26, 2013.

  1. chinky

    chinky Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi


    Performance issue in SQL.
    I have uploaded the query as an attachment.
    It is doing a full table scan on the table. which has 40 lack records
     

    Attached Files:

  2. a_kamalraj

    a_kamalraj Forum Advisor

    Messages:
    121
    Likes Received:
    23
    Trophy Points:
    280
    Hi,

    Try to find hw you can avoid using exists and try to make the query. Full Table Scan is nothing to do with the number of records.
    you can try to use some hints or you can create an index on the column which you are selecting in the query.

    Cheers
    Kamal(kamal.love@gmail.com)
     
  3. chinky

    chinky Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi

    Thanks for the Input.
    There is already an index on party_id column in hz_person_profiles table.

    Removing the exists condition will also not help as the number of record is huge so the time taken is a lot more

    Isha
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The problem isn't the index, it isn't the EXISTS clause, it's the table definition as I suspect that the indexed column or columns are nullable, thus requiring a full table scan so Oracle won't miss any data; read here:

    http://dfitzjarrell.wordpress.com/2008/04/09/a-tale-of-two-indexes/

    It's the IS NOT NULL condition causing the full table scan.