Usage Tracking- SYS_LOB* Indexes on LOGICAL_QUERIES and PHYSICAL_QUERIES

Subhakara Netala-Oracle

We are facing one issue. We have UT in our OAC and DB is ADW. In LOGICAL_QUERIES and PHYSICAL_QUERIES tables Indexes are created on QUERY_BLOB columns. These are occupying lot of memory, please see below screenshot. Please suggest how can we delete there indexes without effecting data and performance. Auto Index Creation option is disables, and we did not create these, not sure how were these indexes created.

Subhakara Netala-Oracle

Let me know if this not the right forum to ask this question. Kindly point me to right forum. Thanks.

Gianni Ceresa

If you have access to the table, just drop the index you don't want...

UT isn't meant to need any of these things, it only requires the table structure to be the expected one because it will insert data expecting that list of columns. But that's about it for UT.

Please suggest how can we delete there indexes without effecting data and performance. 

Indexes are generally used to improve query performance when reading, using, that data. You can't, by default, expect to delete indexes without any possible impact on anything, because why would the index be there if it's pointless?

It all depends on what you do with your UT data, you will probably not really see a difference.

Subhakara Netala-Oracle

Thanks @Gianni Ceresa . We have already tried dropping those indexes, but getting below error.

Gianni Ceresa

A search for that error gave this result from the Oracle forum:

Behind every LOB column the database creates a LOB data and a LOB index segment.

You can't drop them separately, but you need to drop the LOB COLUMN: the two segments will be dropped automatically.

You can't drop that index, because nobody created it (and it makes sense: I was trying to guess why somebody did add an index on that column and I couldn't find any logical reason).

The database works like that, you can't change it.

If space is a real issue, you should consider removing data from the UT table. Either deleting old rows, or creating an archive version of the table where you only bring in the info you care about and drop the LOBs.

Subhakara Netala-Oracle

Thanks for the info. I will check on this.

Thanks