I have an observation i collected the statistics on index & table but query responding slow , now when i tried to run query after deletion it gave result with in second. Why this happening?
Printable View
I have an observation i collected the statistics on index & table but query responding slow , now when i tried to run query after deletion it gave result with in second. Why this happening?
Hi,
Could u post more details abt total rows and no of deleted rows?
Tablesize : 531 M
Rows : 35,35,676
Indexes Sizes varies between 70M ~ 150M.
I rebuild all the indexes and result is same.
how i will find the deleted rows.
because the statistics you gathered werent good enough, and the RBO was just using indexes as it should
WillQuote:
Originally Posted by davey23uk
optimizer_mode=CHOOSE Help in this regard?
what do you have it set to at the moment
Mode of optimizer is set to CHOOSE
and version of oracle is 8.1.7.0
well changing it from CHOOSE to CHOOSE wont help much
you need to work out what stats are requried for your data
how do uyou generate them?
Post the explain plans with and without statistics on the table, as well as the query.