DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql Performance

  1. #1
    Join Date
    Nov 2000
    Posts
    20
    I have a query which is using 4 tables, all the tables are properly indexd for the query. When i ran the query for the first time it took around 5 min. When i saw the trace out put , it was using the idexes.

    After this I had analyzed all the the 4 tables. This time the ouput came within 20 sec. But i saw that it using a full table scan.

    Why a full tablescan is faster after the table are analyzed ?

    Thanks in advance
    Thomas

  2. #2
    Join Date
    Sep 2000
    Posts
    128
    The optimiser may choose a full table scan if it works out that the cost of doing this is less than visiting the indexes first to get the data.

    Are you sure that you have correctly indexed these tables?Are the indexes the correct types (i.e. B-Tree for a high number of distinct values) - Oracle may rule them out if not.

    I think Oracle works out that if a query will pull more than 5% of a tables data from a query, it will choose a full scan over an index scan.

    Terry

  3. #3
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    And the reason a full table scan may be used instead of an index reference is that at some point the overhead required to scan in the index and THEN reference the table outweighs the cost of a full table scan. This is especially true with small tables that only occupy a few data blocks. As TerryD indicates, there is a threshold for this set in the optimizer; however, Oracle keeps changing the rule of thumb. Several years ago, it was 10%, then 5%, then 7%, then 1%, and (I believe) is now at 2%. I don't know if they're changing thumbs, the way their indexes work, or if they're just zeroing in on the "correct" rule.

  4. #4
    Join Date
    May 2000
    Location
    Alex, Egypt
    Posts
    41
    Sometimes, when you test different execution paths, the time taken is not a good measure:
    your first query made an index scan, which caused so many blocks to be read and put in the cache, and since these blocks were accessed by using the index path, so they are kept in the cache for the longest possible time. (even you did an analyse which will again reread the table)
    and now come your second query, and finds most of the blocks ready in the cache, so it doesn't do any disk read, and finish fast.

    while if you flush the buffer cache in between, you may get a different result.

    Amir Magdy

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    If you are using the cost based optimizer .ensure that you analyze the tables and indexes as frequently as possible depending upon the changes that are taking place in the database.
    Radhakrishnan.M

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