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

> You may want to check just the 13th bit instead of the whole number 4097,

thank you for pointing this out. As always, you share something, you learn something on this list.

>Alternatively, user_objects.generated column probably can also give you
>the answer, although that column is not always reliable. It comes from

I've tested this one, but it doesn't give you the same information as ind$.property. Just as you say, it is only about a (constraint) name being system generated or not. It is not filled in when you generate a constraint with a user-defined name and an implicit index, e.g.

... ADD CONSTRAINT pk_xyz ... USING INDEX;

Best regards,
Uwe

Yong Huang wrote:
> Uwe,
>
> 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