Skip to Main Content
  • Questions
  • New indexes and updating stats for long-running queries

Breadcrumb

Question and Answer

Chris Saxon

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

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.