Hello,
In this post, I would like to underline the use of the famous hint ROW_BASED in particular how the Content Server returns and filters the results of a DQL query.
A little theory
ROW_BASED – EMC® Documentum® Content Server Version 7.2 – DQL Reference
The ROW_BASED hint changes both the way query results are returned and the syntax rules for the query itself. The ROW_BASED hint forces Content Server to returns query results in a row format, as opposed to an object-based format. The difference is most readily apparent if the query selects values from a repeating property.
In an object-based format, the server returns all selected repeating values for a particular object in one query result object. In a row-based format, the server returns each selected repeating property value in a separate query result object.
Effects on returned results
For example, by default the following query returns results in an object-based format:
SELECT r_object_id, title, authors FROM dm_document WHERE subject='new_book_proposal'; .... ------------------------------------------------------------ r_object_id | title | authors ------------------------------------------------------------ 090000015973a2fc | Our Life and Times | Jennie Doe Carol Jones Hortense Smith 0900000123ac12f6 | Life of an Amoeba | James Does Jules Doe
This query returns the authors values as a list of authors in one query result object for each returned object. Each row in the table represents one query result object and each column is one property in the query result objects. There is one query result object (one row) for each object returned by the query.
Now, add the ROW_BASED hint to the query:
SELECT r_object_id,title,authors FROM dm_document WHERE subject='new_book_proposal' ENABLE(ROW_BASED); .... ------------------------------------------------------------ r_object_id | title | authors ------------------------------------------------------------ 090000015973a2fc | Our Life and Times | Jennie Doe 090000015973a2fc | Our Life and Times | Carol Jones 090000015973a2fc | Our Life and Times | Hortense Smith 0900000123ac12f6 | Life of an Amoeba | James Does 0900000123ac12f6 | Life of an Amoeba | Jules Doe
There is one query result object (one row) for each repeating property value returned.
The returned repeating property values for each object are not aggregated into one result object.
Filtering of returned results
For example, by default the following query returns the number of authors for the targeted document:
SELECT mydoc1.r_object_id, mydoc1.authors
FROM dm_document mydoc1 where
r_object_id = '0900000123ac12f6'
and any mydoc1.authors IN('James Does');
...
------------------------------------------------------------
r_object_id | authors
------------------------------------------------------------
0900000123ac12f6 | James Does, Jules Doe
SELECT mydoc1.r_object_id, count(mydoc1.authors)
FROM dm_document mydoc1 where
r_object_id = '0900000123ac12f6'
and any mydoc1.authors IN('James Does')
GROUP by mydoc1.r_object_id;
...
------------------------------------------------------------
r_object_id | count(authors)
------------------------------------------------------------
0900000123ac12f6 | 2
Now, add the ROW_BASED hint to the query:
SELECT mydoc1.r_object_id, mydoc1.authors
FROM dm_document mydoc1 where
r_object_id = '0900000123ac12f6'
and any mydoc1.authors IN('James Does')
ENABLE (ROW_BASED);
...
------------------------------------------------------------
r_object_id | authors
------------------------------------------------------------
0900000123ac12f6 | James Does
SELECT mydoc1.r_object_id, count(mydoc1.authors)
FROM dm_document mydoc1 where r_object_id = '0900000123ac12f6'
and any mydoc1.authors IN('James Does')
GROUP by mydoc1.r_object_id
ENABLE (ROW_BASED);
...
------------------------------------------------------------
r_object_id | count(authors)
------------------------------------------------------------
0900000123ac12f6 | 1
These queries return the authors values as a list of authors in one query result object (one row) for each repeating property value returned AND keep only the returned repeating property values corresponding to the filter. So it is necessary to be very careful when the use of ROW_BASED hint and processing of returned repeating property values.
So, in order to process “correctly” the returned repeating property values with use of ROW_BASED hint, a possible solution is to move the filter on repeating attribute from main query into a sub-query:
SELECT mydoc1.r_object_id, count(mydoc1.authors)
FROM dm_document mydoc1 where
mydoc1.r_object_id = '0900000123ac12f6'
and mydoc1.r_object_id IN(select r_object_id from dm_document mydoc2 where any mydoc2.authors IN('James Does'))
GROUP by mydoc1.r_object_id
ENABLE (ROW_BASED);
...
------------------------------------------------------------
r_object_id | count(authors)
------------------------------------------------------------
0900000123ac12f6 | 2
SELECT mydoc1.r_object_id, mydoc1.authors
FROM dm_document mydoc1 where
r_object_id = '0900000123ac12f6'
and mydoc1.r_object_id IN(select r_object_id from dm_document mydoc2 where any mydoc2.authors IN('James Does'))
ENABLE (ROW_BASED);
...
------------------------------------------------------------
r_object_id | authors
------------------------------------------------------------
0900000123ac12f6 | James Does
0900000123ac12f6 | Jules Doe
SELECT mydoc1.r_object_id, mydoc1.authors
FROM dm_document mydoc1 where
r_object_id = '0900000123ac12f6'
and mydoc1.r_object_id IN(select r_object_id from dm_document mydoc2 where any mydoc2.authors IN('James Does'))
;
...
------------------------------------------------------------
r_object_id | authors
------------------------------------------------------------
0900000123ac12f6 | James Does, Jules Doe
Kind regards,
Huseyin OZVEREN