Re: Index or Primary key ??

From: <tlondon_at_my-dejanews.com>
Date: Fri, 23 Apr 1999 09:20:51 GMT
Message-ID: <7fpe1h$d5g$1@nnrp1.dejanews.com>

In article <7fp3sv$a2s$1_at_zonnetje.NL.net>,   "nieuws net" <gkor_at_rdw.nl> wrote:
> Good morning
>
> My goal is to automate the index sizing for production instances.
> When i look at the view USER_INDEXES there is no difference between
> the primary and the secondary index.
> In my mind you don't have store the primary key apart from data in a
> different tablespace.
> So my question is to you
>
> how do i distinct indexes from primary indexes with the help from a view ??
>
> thanks
>
> gkor_at_rdw.nl
>
>

If the primary key index was created via database integrity constraint, then the name of the index is the same as that of the constraint. So a join from user_indexes to user_constraints should give you the information you want

SQLWKS> select ui.table_name,ui.index_name,uc.constraint_type
     2> from user_constraints uc, user_indexes ui
     3> where uc.constraint_name = ui.index_name
     4> and uc.constraint_type = 'P'
     5>
TABLE_NAME                     INDEX_NAME                     C
------------------------------ ------------------------------ -
DEPT                           PK_DEPT                        P
EMP                            PK_EMP                         P

2 rows selected.

This will show you the primary indexes

Tim L

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Apr 23 1999 - 04:20:51 CDT