RegContent

DQL ENABLE hints for large result sets

A query that “works” on ten rows will pin the Content Server or your DFC heap when it returns tens of thousands. Cap the SQL, close the collection, and process ids in batches. The cheatsheet’s ENABLE(RETURN_TOP n) is the smallest version of this; the other two hints matter once you are past a page of results.

RETURN_TOP

Hard cap on rows coming back. Use it in DA / dqMan while you are still shaping the WHERE clause.

select r_object_id, object_name
from dm_document
enable(return_top 100)

OPTIMIZE_TOP

Tells the RDBMS optimizer you only care about the first n rows, so it can pick a plan that starts returning sooner. The number is the batch size you intend to consume.

select r_object_id
from dm_document
where a_content_type = 'pdf'
enable(optimize_top 1000)

Do not combine it with ORDER BY. Sorting the whole set before the top-n cut makes the hint pointless. If you need a sort, sort a key set you already limited, or sort in the client on one batch.

ROW_BASED

Row-based (not object-based) execution. Faster fetch in a lot of dump jobs. Required, or at least the thing that makes the query finish, when you join on a repeating attribute.

select r_object_id, object_name
from dm_sysobject
where any i_folder_id = '0bxxxxxxx'
enable(row_based)

You can stack hints: enable(return_top 500, row_based).

DFC collections

Read the collection, copy the r_object_id values (and only the columns you must have) into a list, close() the collection, then fetch objects one batch at a time. Do not hold an open IDfCollection while you do network or file work. Do not stash whole IDfSysObject graphs for a million hits.

If there is no natural batch key (folder, date, type), use the last two hex characters of r_object_id. Those two digits run 00–ff, so a 14-character prefix is at most 256 objects. Loop the suffix; do not publish a real 14-character prefix from a docbase (it includes the docbase id). Examples stay 09xxxxxxx / 0bxxxxxxx.

select r_object_id
from dm_document
where r_object_id like '09xxxx%'
enable(return_top 256, row_based)

Drive the real prefix from config, not from a hardcoded production id.

Related