CODEWITHSUNDEEP
oracle-databaseindexing
nag
nag

Reputation: 930

oracle table select with indexing becoming slower in first execution and after database cache clear

I've created couple of indexes along with primary key index on my table and when I ran my select query which is having joins and other stuff is taking about 30 sec to display the result for the very first time.

But after first execution, when I ran the query again second time it's giving me the result in below 2 sec and it always same for subsequent executions.

When I check online somewhere I found that execution is faster because of index database caching, so I cleared the database cache using alter system flush buffer_cache; then again my query taking 30 sec to display the result for the first time and second time execution onwards again taking only 2 sec time to display the result.

I'm new to this indexing and is this the way indexing will work or do I have any other ways to work my query which will display the results in 2 sec all the time.

Upvotes: 1

Views: 587

Answers (1)

EdStevens
EdStevens

Reputation: 3872

It has nothing to do with indexing or index caching. It has everything to do with buffer caching, which you are clearing. Whenever a query is executed, oracle will check the buffer cache to see if what is needed is already in the cache. If it is found in the cache, he can avoid an expensive physical read from disk. If not found in the cache, it is read from disk (slow, expensive) and placed in the cache. The next time the data is needed, it is simply pulled from the cache (fast). But when you clear the cache, you force it back to a physical disk read. Quit flushing the buffer cache, and the database will 'stabelize' with a normal 'working set' of cached data.

Upvotes: 4

Related Questions