Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    1

    Unanswered: removing full table scan from query

    hi all,
    i have this piece of sql which is doing a full table scan..
    Can anyone help to get rid of that ?

    this part...

    Code:
    select nvl (max (ch_validfrom), sysdate)
            into todate
            from contract_history
           where ch_pending is null or ch_pending != 'X';
    the table doesnt have index on the column ch_validfrom


    The block is attached where this code is used
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It may not be appropriate to get rid of the full table scan, if the majority (or even a large minority) of the rows in contract_history match the WHERE clause condition. Full scans are not inherently bad.

    However, if the WHERE clause really only matches a small proportion of the rows, then you could perhaps add an index such as:
    Code:
    create index contract_history_idx on contract_history
    ( nvl(ch_pending,'X'), ch_validfrom);
    Then change the query to:
    Code:
          select nvl (max (ch_validfrom), sysdate)
            into todate
            from contract_history
           where nvl(ch_pending,'X') != 'X';
    Oracle can then answer this query using the index alone.

Posting Permissions

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