Index utilization is not as per rule?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Index utilization is not as per rule?

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    Index utilization is not as per rule?

    Hi,

    THrough a reporting tool I have joined
    two large tables and there is no index
    in that tables.

    Half way through the query we found there is no
    index in that tables and we created with out cancelling
    the queries.

    After the index creation it seems the query
    running fast.. and I think it started using the index.

    is it correct?

    Please suggest.

    Thanks and regards
    Giri

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    I don't think a query can change its execution plan midway.. What makes you feel that the query started running faster through midway ?
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Apr 2003
    Posts
    353
    The query retrived 500 records per second before index creation..
    where as after that it retrieved 1500 per second.

    Thanks

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    so you re-ran after you created the index? then there is a good chance it will use the index

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I expect that any speed-up after creating the index was due to the presence of the table blocks in the buffer cache. The index creation kind-of pre-loaded the sga with the blocks required by the query.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Good one, slimdave!
    Engiri, how do you count the rows per second?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by slimdave
    I expect that any speed-up after creating the index was due to the presence of the table blocks in the buffer cache. The index creation kind-of pre-loaded the sga with the blocks required by the query.
    I wouldn't agree with this one, unless the table is rather small.

    When creating the index, oracle actually performs full scan on the table. And FTS only loads a limited portion of blocks at the time into the buffer cache, recycling previously useed blocks (presumably) from the same table scan. Hence, at the end of the index creation, only a relatively small portion of the table blocks will be held in the buffer cache.

    It is very likely that any speed-up after creating the index was due to the changed execution plan of the query as a direct result of the inew indexe's existance.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Really?
    Oracle will change execution plan during execution of one statement?

    Or the table could be defined as cached.
    Last edited by TomazZ; 06-19-2003 at 06:09 PM.
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by TomazZ
    Really?
    Oracle will change execution plan during execution of one statement?

    Or the table could be defined as cached.
    But of course not! That can't happen. I was refering to spead-ups in any subsequent reruns of the query after the index creation.

    I thought slimdave was refering to re-runs too, but now after your intervention I realised he perhaps was actually talking about the speed-up of the initial query, during which the index was created. So in this light his remark makes much more sence - however I still doubt table blocks buffered in cache because of index creation could realy have any significant impact on the initial query performance. Moreover, I sincirely doubt any query would suddenly became three times faster in the midle of the execution because of something like that. And very good remark from your side: "Engiri, how do you count the rows retrieved per second during execution of the query?" (although there are quite some techniques to get this during the very execution of the long running query).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by TomazZ
    Or the table could be defined as cached.
    Ah, precisely -- but even if the blocks get marked as least recently used, that doesn't always mean they will get aged out straight away.

    And SELECT execution plans getting changed part way through? I think not.

    Ask yourself how Oracle would identify the thousands of rows returned prior to the change in execution plan, so that it could eliminate those from the post-change query result. Not a feasible process.

    Unless, of course, this is not a single query, but a cursor loop with a select inside.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width