How to set table level preference setting

Sometimes, we may need to set the optimizer statistics preferences at the table level. We will check in detail about it

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');

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