Printable View
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
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
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.
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.
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.