Menu
I was trying to execute the following query to get the count of index from my schema.
select count(*) from USER_INDEXES; --which gave me a count of 397
But
select count(*) from ALL_INDEXES where table_owner ='MY_SCHEMA'; -- gave me 357.
What it sounds? Both should be same right?
When checked from Oracle SQL developer by counting the indexes myself, gave me 397
279
@jonearles : Yes.It is correct. I double-checked that. This is because, the view ALL_INDEXES contains all the indexes the current user has the ability to modify. You will not see the LOB index in this view because LOB indexes cannot be renamed, rebuilt, or modified. While USER_INDEXES view contains all the indexes that the user owns.
In order to gather information about all indexes in a specific database, you need to execute the sp_helpindex number of time equal to the number of tables in your database. For the previously created database that has three tables, we need to execute the sp_helpindex three times as shown below: sp_helpindex ' [dbo]. [STD_Evaluation]'
When checked from Oracle SQL developer by counting the indexes myself, gave me 397 Are you sure those numbers are correct? They look backwards to me. I would expect ALL_INDEXES to return more than USER_INDEXES. @jonearles : Yes.It is correct. I double-checked that.
This huge number of allowed, but not recommended, indexes help us in covering and enhancing the performance of a large number of queries that try to retrieve data from the database table.
This is because, the view ALL_INDEXES contains all the indexes the current user has the ability to modify. You will not see the LOB index in this view because LOB indexes cannot be renamed, rebuilt, or modified.
While USER_INDEXES view contains all the indexes that the user owns. The LOB index will be in this view if the user querying it is the same user that created it.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
