Performance Tip: Avoid ORed Folder predicates

April 17, 2007 at 8:48 pm | Posted in Documentum and Oracle, Performance | 1 Comment

Consider the following query that contains a ‘Folder’ predicate:

select 	r_object_id 
from 	dm_document 
where 	folder('/Temp/Folder1',descend) 

The query selects all documents that are in /Temp/Folder1 or any sub-folders underneath. This sort of query generally performs quite well. However if you want to select documents from 2 or more sets of folder it is best to avoid ORing the folder predicates. E.g. this query may scale poorly as the number of dm_document objects in the system increases:

select 	r_object_id 
from 	dm_document 
where 	folder('/Temp/Folder1',descend) 
OR 	folder('/Temp/Folder2',descend)

Why is this? The SQL generated by the first query looks like this:

select 	all 
	dm_document.r_object_id 
from 	dm_document_sp dm_document, 
	dm_folder_r dm_folder_r1, 
	dm_sysobject_r dm_sysobject_r2 
where 	(    dm_document.r_object_id = dm_sysobject_r2.r_object_id 
	 and dm_sysobject_r2.i_folder_id = dm_folder_r1.r_object_id 
	 and dm_folder_r1.i_ancestor_id = '0b0000038000e990'
	) 
and 	(    dm_document.i_has_folder = 1 
	 and dm_document.i_is_deleted = 0
	)

The folder predicate is implemented by adding the dm_folder_r table into the join and filtering on the i_ancestor_id field. The database optimiser can evaluate different join orders and work out the best table join order. If there are a relatively small number of objects under the folder /Temp/Folder1 then probably the best table to join first is dm_folder_r; subsequent joins to the dm_sysobject_r and dm_sysobject_r table will involve only a small number of rows.

On the other hand the SQL for the ORed predicate DQL looks like this:

select 	all 	
	dm_document.r_object_id 
from 	dm_document_sp dm_document 
where 	(    dm_document.r_object_id in (	
		select 	dm_sysobject_r2.r_object_id 
		from 	dm_sysobject_r dm_sysobject_r2, 
			dm_folder_r dm_folder_r1 
		where 	dm_sysobject_r2.i_folder_id 
				= dm_folder_r1.r_object_id 
		and 	dm_folder_r1.i_ancestor_id 
				= '0b0000038000e990')  
	 or  dm_document.r_object_id in (	
		select 	dm_sysobject_r2.r_object_id 
		from 	dm_sysobject_r dm_sysobject_r2, 
			dm_folder_r dm_folder_r1 
			where 	dm_sysobject_r2.i_folder_id 
					= dm_folder_r1.r_object_id 
			and 	dm_folder_r1.i_ancestor_id 
					= '0b0000038000e995') 
	) 
and 	(    dm_document.i_has_folder = 1 
	 and dm_document.i_is_deleted = 0
	)

The SQL is completely different. In this case the folder predicates are implemented using a correlated sub-query for each folder predicate. The database is unable to find a query plan that allows the optimal join order – the one where dm_folder_r is joined first. The only way the database can execute the query is to select every current dm_document object and attempt to join it to the dm_folder_r. In a large database the work to select every dm_document object could be huge.

So what are the alternatives? I can think of a few but a UNION ALL would be top of my list in this particular case:

select 	r_object_id 
from 	dm_document 
where 	folder('/Temp/Folder1',descend) 
UNION ALL
select 	r_object_id 
from 	dm_document 
where 	folder('/Temp/Folder2',descend)

By the way this performance tip applies whether the folder contains the ‘descend’ clause or not.

Advertisements

1 Comment »

RSS feed for comments on this post. TrackBack URI

  1. […] Query Performance In yesterday’s post I talked about the potential performance problems with a particular query. I noticed this problem […]


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: