Hi,
Strangely this reference to "stale ROWIDs" sounds like a dead ringer for the new deferred global index maintenance feature on partitioned tables. In 2004.
Just out of curiosity :
https://richardfoote.wordpress.com/2018/10/09/hidden-efficiencies-of-non-partitioned-indexes-on-partitioned-tables-part-ii-aladdin-sane/ mentions :
We notice that the ROWID for each index entry is now the extended 10 bytes in size as it includes the Data Object Id.
https://blog.toadworld.com/2017/04/07/oracle-database-12c-deferred-global-index mentions :
filter(TBL$OR$IDX$PART$NUM("DGOMEZ"."TABLE1",0,8,0,"TABLE1".ROWID)=1)
presumably that returns 1, as long as the given ROWIDs data_object_id (still) belongs to the given object.
sidenote : That (undocumented) function is probably a grab bag of functionality. In this case it doesn't seem to return the partition number or partition position but a Y/N answer. I also don't think we can write our own PL/SQL-functions that take actual objects (tables, (pseudo-)columns) rather than VARCHARS with those objects' names as parameters.
That explains how you can presumably truncate a partition and insert new rows into it and Oracle can still tell the old ROWIDs from the new ones even though they still point to the same object_id (just not data_object_id). Although :
> it is used so we can just repoint a segment to a new segment.
Why would truncate need a new segment?
There'se also this mentioned :
sys.index_orphaned_entry$;
select index_name, status,orphaned_entries from dba_indexes where owner='DGOMEZ' and index_name='INDEX1'
I assume that if during PMO_DEFERRED_GIDX_MAINT_JOB another drop/truncate occurs that should stop the cleaning of the orphaned_flag at the end.
Also unknown is if the cleaning continues (now removing the new dropped partitions entries too or leaving those alone?) or stops and restarts?
There seems to be some ironing out to do too :
http://mvelikikh.blogspot.com/2017/04/asynchronous-global-index-maintenance.html job SYS.PMO_DEFERRED_GIDX_MAINT_JOB did not ignore objects in the Recycle Bin
the automatic maintenance job PMO_DEFERRED_GIDX_MAINT_JOB may fail and does not process all of the indexes that require cleanup.
An alternative to the job is
ALTER INDEX COALESCE CLEANUP
The docu (11 and 12) say :
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-INDEX.html#GUID-D8F648E7-8C07-4C89-BB71-862512536558 Use this clause to compact the index segments. Specifying ALTER INDEX ... SHRINK SPACE COMPACT is equivalent to specifying ALTER INDEX ... COALESCE
but :
SHRINK : Restriction on Shrinking Index Segments : You cannot specify this clause for a bitmap join index or for a function-based index.
COALESCE : Restrictions on Coalescing Index Blocks : no BMI or FBI mentioned
This also finds differences :
https://blog.dbi-services.com/what-about-alter-index-shrink-space/ regards,