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!

I made the upgrade from Oracle Database 10g to 11g!

Discussion in 'SQL PL/SQL' started by jesthr@gmail.com, May 21, 2012.

  1. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    I made the upgrade from Oracle Database 10g to 11g!

    I have a table with 10 million records.
    The first implementation of the SQL statement SQL> SELECT COUNT (*) from myTable
    is carried out for 8 minutes. The subsequent implementation takes only 15 sec.

    What do I need to be amended to implement the first SQL statement took only 15 sec.

    Regards!

    Hine
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I will guess from your wording that you are asking why the second run only took 15 seconds -- 11g implements the results cache and since your query did not return a different result set the second time Oracle used the cached results. Had that table undergone ujpdates/inserts/deletes then you would not have seen such a dramatic reduction in query time.
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    It's impossible to say without looking at the two implementations you mention. We can only guess.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Actually given the version information the answer isn't a guess -- the 11g results cache is the reason the second run was so quick. Oracle verified that no updates had occurred between query runs and then chose to return the previously reported count. Had data been inserted or deleted in the table the query would have taken longer as a new count(*) would need to be made.
     
    Sadik likes this.