Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Statistics at SUB-PARTITION level

764479Feb 20 2011 — edited Feb 21 2011

How do you SET statistics at sub-partition level?
We are running Oracle 11g R2 and want to set statistics to to simulate different data distributions among the sub-partitions within related partitions.
Essentially we want to control/change the "NUM_ROWS" to fool the the Optimizer to think there are more or less rows.

We have reviewed DBMS_STATS Subprograms
DBMS_STATS.GET_TABLE_STATS
DBMS_STATS.SET_TABLE_STATS
Both of these subprograms seem to be able to go to the subpartition for the deepest level.

We can determine the statistics (NUM_ROWS) for the sub-partitions by querying the column NUM_ROWS of table DBA_TAB_SUBPARTITIONS.
AFTER the table be analyzed.
But we have not found a way to SET the table statistics at sub-partition level.

We are working in a VLDB and using RANGE-LIST partitioning for our largest of tables.

Edited by: user10260925 on Feb 20, 2011 9:02 AM

Edited by: user10260925 on Feb 20, 2011 9:02 AM

This post has been answered by Jonathan Lewis on Feb 20 2011

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 21 2011

Added on Feb 20 2011

#general-database-discussions

6 comments

630 views