Thanks for the question, Girish.
Asked: January 25, 2022 - 4:44 am UTC
Last updated: January 25, 2022 - 11:46 am UTC
Version: 12c
Viewed 1000+ times
You Asked
Hi,
1) There is batch program executing and one query running long is accessing a table T.
It is found that an index on column will help query and batch can't be killed.
Can we create an index on column of a table which is being used by long running query
2) Can we gather statistics on that table online and will that take effect ?
Thanks,
Girish
and Chris said...
1. You can create indexes while queries are running. But only queries started
after the index is created can use it. You can't change a query to use a new index part-way through execution.
2. Again yes you can gather stats while queries are running. But these only take effect for queries
after the stats are gathered. By default queries with existing plans pick up the new stats in a rolling fashion to stop the database from having to re-parse lots of statements at the same time.
Nigel Bayliss (optimizer PM) goes into the details at
https://blogs.oracle.com/optimizer/post/fine-grained-cursor-invalidation