Monday, July 27, 2015

Gather statistics on spatial index

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.

2 comments :

Jeff Bourdier said...

Thanks for this. I'm working on some Oracle Spatial optimization right now and found this helpful.

BTW, DBA_INDEXES.LAST_ANALYZED is null for all spatial indexes in my case, so I rely solely on the MDXT tables.

SELECT COUNT(*)
FROM DBA_INDEXES
WHERE (ITYP_NAME = 'SPATIAL_INDEX') AND (LAST_ANALYZED IS NOT NULL);
/* returns 0 */

Oyvind Isene said...

Thank you for stopping by, and reminding about my post; I've recently looked into some Spatial optimization and forgotten about this post :-)