Re: Stats gather for big tables

Others can comment but as per my understanding its 'bite the bullet' scene and no way to get rid of it. Because although the synopsys already been created for the rest of the columns , and Oracle should only scan these newly added columns but as we basic principle says oracle stores rows in blocks but not columns, so in that way it has to scan through all the rows again for getting the data pattern pertaining to those newly added columns. The only thing you can do is rather taking it as a surprise , the dev team should inform beforehand regarding the new columns , so that you can plan the first/one time gathering in a controlled fashion with higher parallel degree. And this first time gather will create the base synopsys after which the subsequent gather should happen only on the changed data/live partitions without any issue.

On Tue, Mar 30, 2021 at 12:26 AM Pap <oracle.developer35_at_gmail.com> wrote:

> We are using incremental stats collection approach for gathering
> statistics on some big partition tables(having size ~20-30 TB). And thus we
> just have to pass table name to the stats gather block and rest is taken
> care by oracle from the synopsys information. Now we encountered a
> situation multiple times in which the team endup adding new columns and the
> stats collection job started from scratch running for days and then
> impacting other jobs. So want to understand if there is any better way of
> handling this situation? If we can by some way restrict Oracle to not scan
> all the data again and create synopsis from scratch rather utilize existing
> synopsis and just scan those delta column information which was added to
> the table? (Oracle version - 11.2.0.4)
>
> Regards
> Pap
>

--
http://www.freelists.org/webpage/oracle-l