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_indexe开发者_高级运维s;

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

---

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

---

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.

---

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.