How to set table level preference setting

📁

Tutorial Collection

This guide is part of our comprehensive Oracle Database Reference Hub.

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