Re: (not) dropping indexes with PK constraints in 10g
- From: Yong Huang <yong321@xxxxxxxxx>
- To: uwe@xxxxxxxxxxxx
- Date: Fri, 21 Aug 2009 09:17:01 -0700 (PDT)
Uwe,
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' );
--
//www.freelists.org/webpage/oracle-l
Other related posts:
