Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Location
    Toronto
    Posts
    21

    Unanswered: Explain plan results

    Hi,

    I have a SQL query that I want to optimize. The query has execution time of ~ 1min 15 sec and an explain plan cost of 54,000. I created an index that I feel should improve performance. And indeed explain plan shows the cost reduction of 100 times! But the execution time has INCREASED to 5min. What is going on here?

    Thanks in advance,
    Edward

  2. #2
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    That depends on the query, the table and the index.
    The explain plan shows that the Oracle optimizer THINKS that using the index will be faster, but that might not always be the case. There might be some circumstances that the optimizer didn't see or the statistics could be outdated.
    In this case: Don't use the index. Maybe think of another index, re-writing the query or some other solution.
    For more help, please post more details on the query, table etc.

    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

Posting Permissions

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