CODEWITHSUNDEEP
sqloracle-databaseindexing
HJW
HJW

Reputation: 23443

No indexes found in any table - is that possible?

By executing the following command that will list out all indexes found in my schema, the query returned nothing - suggesting that either no index is created, or probably i do not have sufficient permission. 

select * from user_indexes;

Are there any more ways to list the indexes i have in a schema?

Upvotes: 0

Views: 753

Answers (3)

Trevor North
Trevor North

Reputation: 2296

Florin is correct, USER_INDEXES is a view on ALL_INDEXES that only shows those which were created by you. You can query ALL_INDEXES directly to determine if anyone else has created indexes on the table in question but you will probably want to add a where clause for the table_name as it will list all indexes for all tables in the instance and also only some of the columns.

SELECT TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME='XYZ';

You can limit which tables using an IN CLAUSE if there is are several tables you are interested in 

SELECT TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME IN ('ABC','XYZ');

and you can use a like clause if there is a prefix or suffix

SELECT TABLE_NAME, INDEX_NAME FROM ALL_INDEXES WHERE TABLE_NAME like 'XYZ%';

Also, if you want to see which columns these indexes are on, you can select from ALL_IND_COLUMNS;

SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME='XYZ'

Note that whether a table has indexes or not depends on the data and the usage. A small lookup table that has maybe a hundred rows or less would not need an index whereas a table that contains millions of rows but is queried for just a handful when needed would need an index.

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

As paulsm4 say, you do not have any indexes in your schema.

you can use 

select * from all_indexes;

and you'll see all your indexes + the others where you have rights.

Upvotes: 1

paulsm4
paulsm4

Reputation: 121609

Sure it's possible.

Common, even :)

It just means nobody's created any indexes.

If the query returned nothing, it means that you DO have permission ... and there simply aren't any indexes.

Here's a good link on "Managing indexes in Oracle" (it sounds like you're probably running Oracle):

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm

Upvotes: 5

Related Questions