Sometimes, we may need to set the optimizer statistics preferences at the table level. We will check in detail about it
How to set table level preference setting
The DBMS_STATS.SET_TABLE_PREFS procedure is used to set the statistics preferences of the specified table in the specified schema.
Syntax
DBMS_STATS.SET_TABLE_PREFS ( ownname IN VARCHAR2, tabname IN VARCHAR2, pname IN VARCHAR2, pvalue IN VARCHAR2)
Example
exec DBMS_STATS.SET_TABLE_PREFS ( 'SCOTT', 'DEPT', 'DEGREE', 4);
How to check the setting
Suppose we want to check if the settings are in place, then the below procedure can be used to find it out
exec DBMS_STATS.GET_PREFS ( 'DEGREE','SCOTT', 'DEPT');
How to delete the setting
Suppose you want to delete the setting, then the below procedure can be used
exec DBMS_STATS.DELETE_TABLE_PREFS ( 'SCOTT', 'DEPT',' DEGREE');
I hope you find this information helpful and useful
Related Articles
PREFERENCE_OVERRIDES_PARAMETER in Oracle
How to delete Optimizer preference
Optimizer statistics preferences in Oracle
How to check automatic statistics collection in Oracle