Home » Oracle » Oracle Database » How to set table level preference setting

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

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

See also  Logical view of the data model in R12.2

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top