Re: (not) dropping indexes with PK constraints in 10g

From: Yong Huang <>
Date: Fri, 21 Aug 2009 09:17:01 -0700 (PDT)
Message-ID: <>


You may want to check just the 13th bit instead of the whole number 4097, as other bits have different meanings. According to sql.bsq in 10g (or dcore.bsq in 11g):

/* The index was created by a constraint : 0x1000 */

So the correct way to check is

DECODE(bitand(property,4096), 4096, 'implicit', 'user-generated')

The first bit is about uniqueness. You must be testing with a unique index?

Alternatively, user_objects.generated column probably can also give you the answer, although that column is not always reliable. It comes from a different perspective, i.e. whether the name is system-generated. Since a user-generated index is always given a name by the user, and an implicitly created index (when the constraint is created) is always given a name by Oracle, the two criteria are consistent with each other. I hope I got that right.

Yong Huang

  • Original Message ----- Q: How can I find out if the supporting index was user-generated or not? A: With access to the data dictionary table IND$: SELECT DECODE( property, 4097, 'implicit', 'user-generated' ) generation FROM SYS.ind$ WHERE obj# = ( SELECT object_id FROM user_objects WHERE object_name = 'PK_PKTEST' );

-- Received on Fri Aug 21 2009 - 11:17:01 CDT

Original text of this message