DQL Queries - Indexing

1. Total number of documents that should be indexed:

select distinct r_object_id as OBJID from dmr_content where any parent_id in (select r_object_id from dm_document where a_full_text = true)

2. Total number of documents that have failed to index:

select count(*) from dmr_content where any update_count < 0

3. Total number of documents that have been indexed:

select count(*) from dmr_content where any update_count > 0

4. Gives general overview of current indexing state:

select update_count, count(*) from dmr_content group by 1

5. Find names of all fulltext indices and what they refer to:

select index_name,store_name from dm_fulltext_index

6. Find all indexed documents:

select r_object_id object_name from dm_sysobject where a_full_text = true and r_object_id in (select parent_id from dmr_content where any (index_pending = false and update_count >0))

7. Find all documents waiting to be indexed:

select r_object_id object_name from dm_sysobject where a_full_text = true and r_object_id in (select parent_id from dmr_content where any index_pending = true)

8. Checking the status of a particular document:

select index_pending,update_count from dmr_content where any parent_id = 'document_id' and any parent_id in (select r_object_id from dm_sysobject where a_full_text = true)