Hi all ,
I ANALYZE some table for test and i query the table at the same time , but my query get a lot of time to retrivel data from database , the query run fast usually , i want to know that the ANALYZE either influrence the query or not ?
Yes, analyzing a table or index does affect the query. Probably was using an index before and is no longer because the CBO believes, wrongly, that the current access path is the quickest. You should try analyzing the table and table indexes (and use compute to be on the safe side for testing if they aren't too large). If the data in one of the columns accessed by the index is highly skewed, you'll need to compute histograms for those columns.
Let me know if you need further clarification.
(Delete the statistics first and run your query again and look at the explain plan, then reanalyze the table and indexes and run the query again. Compare the two differences and if you still need help, post the query and the plans from both runs)
Senior Database Administrator
If you mean at the same time of analyzing that, is because the table lock (while analyzing the table is locked).
Click Here to Expand Forum to Full Width