Re: Gather table stats without partition stats

Hello Moustafa,

> So do you agree that we do not need partition stats?

No, as I don't know your corresponding execution plans. Partition statistics can be very helpful if your SQL accesses only one partition (e.g. like a table partitioned by date and your predicate looks like <PART_BY_COLUMN> = SYSDATE) and the optimizer is able to determine the partition at parse / optimization time.

> What is sensation for 2nd case partition known at hard arse time ?

Sorry, I don't understand this question. The partition needs to be known at optimization phase so that the optimizer can use the right/valid partition statistics - so basically the optimizer must be able to prune to one partition to make use of these particular partition statistics.

> I did that but it is still gather stats for partition what do you think could be the reason ?

Can you please share the DBMS_STATS call and the statistics (from table and its partitions) from before and after?

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Moustafa Ahmed <moustafa_dba_at_hotmail.com> hat am 18.06.2021 18:30 geschrieben:
>
>
> Stefan
>
> > Anyway partition statistics are used only in case of partition pruning to one single partition (and when the partition is known at hard parsing / optimization time).
> So do you agree that we do not need partition stats?
> What is sensation for 2nd case partition known at hard arse time ?
>
>
> > 2) I guess what you are looking for is DBMS_STATS.GATHER_TABLE_STATS(granularity => 'GLOBAL') - you can also set it as default on table level.
> I did that but it is still gather stats for partition what do you think could be the reason ?

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