I'm in the process of writing a procedure for automating the index
creation....Parameters i pass will be tablename,new indexname,index
type and column list(list of column seperated by commas as one string)

How can i find whether already an index is associated with the same
column list and the same position...


Can u please help on it

On 4 Mar 2005 12:33:05 -0800, "Shasi" <v.sasikumar@gmail.com> wrote:

>I'm in the process of writing a procedure for automating the index
>creation....Parameters i pass will be tablename,new indexname,index
>type and column list(list of column seperated by commas as one string)
>
> How can i find whether already an index is associated with the same
>column list and the same position...
>
>
>Can u please help on it

user_indexes joined to user_ind_columns (or dba_indexes and
dba_ind_columns)


--
Sybrand Bakker, Senior Oracle DBA

I am aware of the two data dictionaries but still i cannot exacly
visualise because if the index already existing is along with a
constraint say unique constraint then i need to drop the constraint .
In that case i cannot i drop the index directly...can u please reply in
detail regading this as it will be very useful to me

On 5 Mar 2005 00:56:09 -0800, "Shasi" <v.sasikumar@gmail.com> wrote:

>I am aware of the two data dictionaries but still i cannot exacly
>visualise because if the index already existing is along with a
>constraint say unique constraint then i need to drop the constraint .
>In that case i cannot i drop the index directly...can u please reply in
>detail regading this as it will be very useful to me


Ok,
what is in the dictionary can be viewed by looking up
select * from dict where table_name /* in this case* / like
'%CONSTRAINT%'

This will result in
dba_constraints
user_constraints

as you can expect, the column definition is in dba_cons_columns and
user_cons_columns respectively.

The dba_constraints *view* has a column constraint_type.
In the docs you can read the allowed values are
'C' for check
'P' for primary
'R' for referential (in this case the r_constraint column is not null,
pointing recursively to a second record in the same view)
and 'U' for unique.

That's all there is to it.
Please note apart from the dict view, there is also a
dict_columns_view, and two comments views on the dictionary.
It is all there and it is all documented, and regrettably most people
prefer not to read the documentation and wait for someone here to
respond to their queries.
Mind you, I found this out ages ago, by doing some detective work
myself. And that time I didn't have access to Usenet at all, and it
was beyond me to start nagging Support for everything.




--
Sybrand Bakker, Senior Oracle DBA

"Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
news:g0ui21lenlrt1t4fq6s4h9187nma1gcu0e@4ax.com...
> On 5 Mar 2005 00:56:09 -0800, "Shasi" <v.sasikumar@gmail.com> wrote:
>
> >I am aware of the two data dictionaries but still i cannot exacly
> >visualise because if the index already existing is along with a
> >constraint say unique constraint then i need to drop the constraint .
> >In that case i cannot i drop the index directly...can u please reply in
> >detail regading this as it will be very useful to me
>
>
> Ok,
> what is in the dictionary can be viewed by looking up
> select * from dict where table_name /* in this case* / like
> '%CONSTRAINT%'
>
> This will result in
> dba_constraints
> user_constraints
>
> as you can expect, the column definition is in dba_cons_columns and
> user_cons_columns respectively.
>
> The dba_constraints *view* has a column constraint_type.
> In the docs you can read the allowed values are
> 'C' for check
> 'P' for primary
> 'R' for referential (in this case the r_constraint column is not null,
> pointing recursively to a second record in the same view)
> and 'U' for unique.
>
> That's all there is to it.
> Please note apart from the dict view, there is also a
> dict_columns_view, and two comments views on the dictionary.
> It is all there and it is all documented, and regrettably most people
> prefer not to read the documentation and wait for someone here to
> respond to their queries.
> Mind you, I found this out ages ago, by doing some detective work
> myself. And that time I didn't have access to Usenet at all, and it
> was beyond me to start nagging Support for everything.
>
>
>
>
> --
> Sybrand Bakker, Senior Oracle DBA
Of course, he could just try and create the thing and catch the error if
there is one and deal appropriately with it if the error indicates the
object exists. he might not have rights to see it anyway, but Oracle will
prevent him from making the same object 2 x even if owned by someone else.
Jim