Inside Documentum

What does this query do?

SELECT ALL 'y' AS notnavigatable,o.r_object_type AS r_object_type,
o.r_lock_owner AS r_lock_owner,o.r_object_id AS r_object_id,'y' AS
selectable,'0' AS isfolder,upper(o.object_name) AS objname,
o.owner_name AS owner_name,o.r_version_label AS r_version_label,
o.i_is_reference AS i_is_reference,o.a_content_type AS a_content_type,
o.object_name AS object_name,100 AS idunion,o.r_is_virtual_doc AS
r_is_virtual_doc,'' AS navigatable,o.r_link_cnt AS r_link_cnt,
o.r_full_content_size AS r_content_size FROM dm_process o
WHERE ( r_definition_state = 2) AND NOT
((FOLDER('/Resources', DESCEND) OR FOLDER('/System')) OR
FOLDER('/System/DistributionList Templates', DESCEND) AND
o.object_name LIKE 'dmSendTo%') ORDER BY 13 ASC,7 ASC,
4 ASC,9 ASC

I don’t really have an idea either until I format it into something readable:

SELECT ALL 	'y' AS notnavigatable,
		o.r_object_type AS r_object_type,
		o.r_lock_owner AS r_lock_owner,
		o.r_object_id AS r_object_id,
		'y' AS selectable,'0' AS isfolder,
		upper(o.object_name) AS objname,
		o.owner_name AS owner_name,
		o.r_version_label AS r_version_label,
		o.i_is_reference AS i_is_reference,
		o.a_content_type AS a_content_type,
		o.object_name AS object_name,
		100 AS idunion,
		o.r_is_virtual_doc AS r_is_virtual_doc,
		'' AS navigatable,
		o.r_link_cnt AS r_link_cnt,
		o.r_full_content_size AS r_content_size
FROM 		dm_process o
WHERE 		( r_definition_state = 2)
AND 		NOT (
			(   FOLDER('/Resources', DESCEND)
			 OR FOLDER('/System')
			)
		     OR FOLDER('/System/DistributionList Templates', DESCEND)
		     AND o.object_name LIKE 'dmSendTo%'
		)
ORDER BY 13 ASC,7 ASC,4 ASC,9 ASC

It is now immediately apparent which object types or registered tables are being queried (dm_process in this case), which attributes are in the select list and what the query predicates are. I generally use tabs to space out each block (e.g. the attributes in the select list are all aligned to a tab) and to show nesting of predicates and sub-queries.

Another benefit of formatting the query is that, generally, you are not that interested in the attribute select list when tuning queries. As a rule you look first at the object types/tables and predicates involved, along with any additional clauses that affect how queries will be processed like ORDER BY and GROUP BY. By formatting the query you can immediately spot the important FROM, WHERE, ORDER BY and GROUP BY clauses.

Here’s another example of a properly formatted query with multiple object types to illustrate another point:

select 	p.retainer_root_id as retainer_root_id,
	t.r_object_id as retained_object_id,
	p.event_date as event_date,
	p.r_object_id,
	p.object_name as retention_name,
	p.r_policy_id,
	p.retention_policy_id,
	p.r_retention_status,
	p.entry_date,
	p.qualification_date,
	p.r_object_type,
	p.phase_name,
	q.object_name as policy_name,
	s.object_name as retention_policy_name
from 	dm_sysobject (all) t,
	dmc_rps_retainer p,
	dm_policy q,
	dmc_rps_retention_policy s
where 	q.r_object_id=p.r_policy_id
and 	s.r_object_id=p.retention_policy_id 
and 	p.r_object_id in (
		select 	i_retainer_id
		from 	dm_sysobject (all)
		where 	(r_object_id = '0900379780155e1b')
	)
and 	t.r_object_id  ='0900379780155e1b'
and	p.retainer_root_id is not null

Where there are multiple types or tables in the FROM clause I put them, one line for each, one after the other. Then in the predicate (where) clause I ensure that the join conditions come first followed by the other, filtering, clauses.

You can immediately see in this example that dmc_rps_retainer, dm_policy and dmc_rps_retention_policy all have join conditions but dm_sysobject (all) does not. This should raise alarm bells as if there is no join condition for a table then all the rows of the table will be joined to the rowset formed by the other table joins – this could potentially result in massive amounts of work for the database engine.

In this case there is no need to worry as we can see further down that there is a filtering condition t.r_object_id =’0900379780155e1b’ which will ensure that only a single row will be output for joining to the other tables.

It is impossible to effectively tune queries without understanding what they do and it is very difficult to understand what a query does without formatting it. With only a little practice it is easy to format even very large queries in a few minutes.