Gather statistics on spatial index

July 27, 2015

When you run dbms_stats.gather_table_stats on tables with spatial indexes or dbms_stats.gather_index_stats directly on the spatial index the routine may return without an error even if no statistics gathering took place. You can verify this by looking at the LAST_ANALYZED column in DBA_INDEXES:

select owner,index_name,last_analyzed  
from dba_indexes   
where index_type='DOMAIN';  

Another way to verify if statistics gathering for these indexes took place is to look for tables with names that start with MDXT:

select owner,table_name   
from dba_tables  
where table_name like 'MDXT_%';  

The user that owns these indexes must be granted CREATE TABLE directly and not through a role. 

You can find some details about these tables in Doc ID 1610877.1 on My Oracle Support.  In case you have many tables that starts with MDXT you can see what are actually needed by running this:

select sdo_index_owner, index_name, sdo_index_table   
from mdsys.all_sdo_index_info;  

MDXT-tables whose number does not appear among the tables listed in sdo_index_table (name starts with MDRT) can be dropped according to the note at MOS.