CertPal

I ran into a weird little error while trying to tune the performance of a query in the oracle database. A table had an index on a VARCHAR2 column. After a fair number of inserts were made the population of this table grew to 1.5 million records. A query that did an join on the VARCHAR2 column was talking ages to get the results. Well, it was taking 900ms actually but the SLA for the API call is way below that.

An “explain plan for QUERY” statement was run followed by a select from plan_details. It turns out the index is never used and a full table scan was used to get the data. Hence the problem.

So all I have to do is find out why the index is not being picked up. It probably has to do with statistics, and a rebuild on the index might help I thought. Or may be I need to analyze the index. So I went about trying to do some of these things. There were no DBAs around so I had to experiment a little.

analyze INDEX YOUR_INDEX_NAME validate STRUCTURE;
 
Error: ORA-01418: specified INDEX does NOT exist
 
SQLState:  72000
ErrorCode: 1418

This was totally weird. I knew that the index did exist and the name was correct. To double check I ran this query

SELECT * FROM all_indexes WHERE INDEX_NAME='YOUR_INDEX_NAME'

And it did return my index correctly. After trying several other commands related to indexes they all returned the same thing. I tried to prefix the SCHEMA name and that did not help.

So it turns out, I do not have permissions to create indexes or do any operations related to them and that is what throws this error. The error is pretty misleading since there already exists an error code for insufficient prvileges for performing a given operation – Error: ORA-00990: missing or invalid privilege.

I passed the index related queries on to a DBA and the performance of my application queries were back on track. I hope this saves a developer the time I lost trying to find out why the index did not exist.

PS: Oracle 11 also has an index visibility option which can be verified with

SELECT INDEX_NAME,VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME='YOUR_INDEX_NAME';