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

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

--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 21 2009 - 11:17:01 CDT