Skip to Main Content
  • Questions

  • Query to find the Index which is not created by primary key

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aravind Kumar.

Asked: September 28, 2016 - 10:03 am UTC

Last updated: September 30, 2016 - 8:54 am UTC

Version: 1.5.5

Viewed 1000+ times

You Asked

Hi,

how to find the index list which is not created by primary key for a particular schema?

select * from all_indexes where owner='sys' currently i am running this query but this is giving the index which is created by primary key also.

Thanks in Advance...

and Connor said...

Dont forget that there are several possibilities here

- index created *automatically* for primary key
- index created manually, and then *used* by the primary key

and the same for unique constraints.

Here's a little example to get you started - a null constraint type would be an indicator of it being a "standalone" index.


SQL> create table t1 ( x int primary key , y int, z int);

Table created.

SQL>
SQL> create index ix1 on t1 ( z ) ;

Index created.

SQL>
SQL> create table t2 ( x int , y int, z int);

Table created.

SQL>
SQL> create index ix2 on t2 ( x ) ;

Index created.

SQL>
SQL> alter table t2 add primary key ( x ) ;

Table altered.

SQL>
SQL> alter table t2 add unique ( z ) ;

Table altered.

SQL>
SQL> select i.table_name,
  2         i.index_name,
  3         u.constraint_type
  4  from   user_constraints u,
  5         user_indexes i
  6  where  i.table_name in ('T1','T2')
  7  and    i.table_name = u.table_name(+)
  8  and    i.index_name = u.index_name(+)
  9  /

TABLE_NAME                     INDEX_NAME                     C
------------------------------ ------------------------------ -
T1                             SYS_C0023021                   P
T1                             IX1
T2                             IX2                            P
T2                             SYS_C0023023                   U

4 rows selected.

SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

user_constraints

Rajeshwaran, Jeyabal, September 29, 2016 - 11:06 am UTC

data dictionary that begins with DBA* , ALL*, CDB* have OWNER column to indicate to which schema these objects belongs to.

but why does user_constraints have OWNER column in it? any specific reason?

demo@ORA12C> set linesize 71
demo@ORA12C> desc user_constraints
 Name                                Null?    Type
 ----------------------------------- -------- -----------------------
 OWNER                                        VARCHAR2(128)
 CONSTRAINT_NAME                              VARCHAR2(128)
 CONSTRAINT_TYPE                              VARCHAR2(1)
 TABLE_NAME                                   VARCHAR2(128)
 SEARCH_CONDITION                             LONG
 SEARCH_CONDITION_VC                          VARCHAR2(4000)
 R_OWNER                                      VARCHAR2(128)
 R_CONSTRAINT_NAME                            VARCHAR2(128)
 DELETE_RULE                                  VARCHAR2(9)
 STATUS                                       VARCHAR2(8)
 DEFERRABLE                                   VARCHAR2(14)
 DEFERRED                                     VARCHAR2(9)
 VALIDATED                                    VARCHAR2(13)
 GENERATED                                    VARCHAR2(14)
 BAD                                          VARCHAR2(3)
 RELY                                         VARCHAR2(4)
 LAST_CHANGE                                  DATE
 INDEX_OWNER                                  VARCHAR2(128)
 INDEX_NAME                                   VARCHAR2(128)
 INVALID                                      VARCHAR2(7)
 VIEW_RELATED                                 VARCHAR2(14)
 ORIGIN_CON_ID                                NUMBER

demo@ORA12C>

Connor McDonald
September 30, 2016 - 8:54 am UTC

I dont know why.

There are plenty of inconsistencies in the dictionary, eg some views use "OWNER", others user "xxx_OWNER" where 'xxx' is the object type of reference. I always remember the USER column in v$sort_usage as being that always tripped people up, because it always looked like all sorting was being done by themselves :-)

Of course, it is then nearly impossible to fix these things, because we do our best to keep backward compatiblity.

inconsisties??

Ghassan, September 30, 2016 - 11:49 am UTC

the is no inconsisty no odd things and it is certainly needed for oracle engine performances and nomenclature purpose to meet objects logic existence.

the owner col. in the user_constraints is the owner not of the Constraint itself, but the very owner of the Table on which this constraint is. it is not ,for instance, similarly comparable to the -say- user_tables view where all tables in are for the user
to be more clear, take the dba_tables and the dba_constraints ; the user col there are not related to same identity object : so OWNER in the first is for owner of constraint'tables - not holding the same name - , and owner in the second is owner of the table itself holding the same name .

also , as you related there are other "xx_owner", and "index_owner" in this view, so it is -for me- very logic that the table owner be part of the cols.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.